From: | "Chris Smith" <cdsmith(at)twu(dot)net> |
---|---|
To: | "Stephan Szabo" <sszabo(at)megazone(dot)bigpanda(dot)com> |
Cc: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Interpreting query plan |
Date: | 2004-07-06 19:20:42 |
Message-ID: | 005601c4638e$55394920$6f00000a@KYA |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Chris Browne | 2004-07-06 19:27:54 | Re: postgresql +AMD64 +big address spaces - does it work? |
Previous Message | Richard Huxton | 2004-07-06 18:49:00 | Re: backup using cron |