Re: Interpreting query plan

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