On Thu, 2 Oct 2003, Oleg Lebedev wrote:

> I was trying to get the pg_stats information to Josh and decided to
> recreate the indexes on all my tables. After that I ran vacuum full
> analyze, re-enabled nestloop and ran explain analyze on the query. It
> ran in about 2 minutes.
> I attached the new query plan. I am not sure what did the trick, but 2
> minutes is much better than 2 hours. But then again, I can't take long
> lunches anymore :)
> Is there any way to make this query run even faster without increasing
> the memory dedicated to postgres?
> Thanks.

As long as the estimated row counts and real ones match up, and postgresql 
seems to be picking the right plan, there's probably not a lot to be done.  
You might want to look at increasing sort_mem a bit, but don't go crazy, 
as being too high can result in swap storms under load, which are a very 
bad thing.

I'd check for index growth.  You may have been reloading your data over 
and over and had an index growth problem.  Next time instead of recreating 
the indexed completely, you might wanna try reindex indexname.

Also, 7.4 mostly fixes the index growth issue, especially as it applies to 
truncating/reloading a table over and over, so moving to 7.4 beta3/4 and 
testing might be a good idea (if you aren't there already).

What you want to avoid is having postgresql switch back to that nestloop 
join on you in the middle of the day, and to prevent that you might need 
to have higher statistics targets so the planner gets the right number 
all the time.

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
      joining column's datatypes do not match

Reply via email to