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
