Re: Interpreting query plan
От | Chris Smith |
---|---|
Тема | Re: Interpreting query plan |
Дата | |
Msg-id | 005601c4638e$55394920$6f00000a@KYA обсуждение исходный текст |
Ответ на | Re: Interpreting query plan ("Chris Smith" <cdsmith@twu.net>) |
Ответы |
Re: Interpreting query plan
|
Список | pgsql-general |
Stephan Szabo wrote: > > [running at higher load] > It might help. Okay, I'll give it a shot. Unfortunately, load isn't so predictable with our current users, so I'll have to give it a few shots and see if I come up with something substantially different. > I wonder if in practice this query uses distinct to get around a > problem with subqueries. Yes, it does exactly that. The database was recently moved over to PostgreSQL 7.4. The thought of switching over to IN-subqueries is a bit scary, since the query generator is a really over-generalized mess of about 7000 LOC in 17 different source files; but I'll certainly look at that. > These steps are for the distinct. It's not alot of actual time, but > if the row set returned was large enough to exceed sort_mem the sort > might start going off to disk and be slower. Indeed, that looks like it could be a problem for some of our larger customers who have up to tens of thousands of users. The IN form would avoid this sort? > The row estimate is pretty reasonable, estimated 629 versus actual > 753. How many rows are in useraccount? I'm wondering if 629 is a > reasonable percentage of the rows to see if seq scan is reasonable > here. Total? On this server, it's currently 2566. > Here the estimate isn't so good, estimated 115 vs actual 1328. You > might want to consider raising the groupid column's statistics target > and re-analyzing to see if you can get a better estimate. Alright. So how exactly do I raise the groupid column's statistics target? > Also, how many rows are in usermapping? 4120 > I didn't see any indexes on usermapping. Perhaps an index on (userid, > groupid) would help as well. Done. I'll watch for more profiling results to see if we run into this same problem again. Thanks for your help, -- www.designacourse.com The Easiest Way to Train Anyone... Anywhere. Chris Smith - Lead Software Developer/Technical Trainer MindIQ Corporation
В списке pgsql-general по дате отправления: