Thanks for the response kevin. DB size is about 30G. Bloat could have been due to recent load testing that was done. Autovaccum wasn't aggressive enough to catch up with load testing. I will rebuild those indexes if possible reload the table itself as they are bloated too. Sure I will collect necessary stats on the next occurrence of the slow query. Stalin -----Original Message----- From: Kevin Grittner [mailto:Kevin.Grittner@xxxxxxxxxxxx] Sent: Tuesday, August 04, 2009 8:57 AM To: Subbiah Stalin-XCGF84; pgsql-performance@xxxxxxxxxxxxxx Subject: RE: Query help "Subbiah Stalin-XCGF84" <SSubbiah@xxxxxxxxxxxx> wrote: > Server has 32G memory and it's a dedicated to run PG and no other > application is sharing this database. It's not likely to help with this particular problem, but it's generally best to start from a position of letting the optimizer know what it's really got for resources. An effective cache size of somewhere around 30GB would probably be best here. > Given the nature of the ix_objects_type_lastmodified index, wondering > if the index requires rebuilt. I tested rebuilding it in another db, > and it came to 2500 pages as opposed to 38640 pages. That's pretty serious bloat. Any idea how that happened? Have you had long running database transaction which might have prevented normal maintenance from working? If not, you may need more aggressive settings for autovacuum. Anyway, sure, try this with the index rebuilt. If you don't want downtime, use CREATE INDEX CONCURRENTLY and then drop the old index. (You could then rename the new index to match the old, if needed.) > The puzzle being why the same query with same filters, runs most of > times faster but at times runs 5+ mintues and it switches back to fast > mode. It is likely either that something has pushed the relevant data out of cache before the slow runs, or there is blocking. How big is this database? Can you get a list of pg_stat_activity and pg_locks during an episode of slow run time? > If it had used a different execution plan than the above, how do I > list all execution plans executed for a given SQL. It's unlikely that the slow runs are because of a different plan being chosen. I was wondering if a better plan might be available, but this one looks pretty good with your current indexes. I can think of an indexing change or two which *might* cause the optimizer to pick a different plan, but that is far from certain, and without knowing the cause of the occasional slow runs, it's hard to be sure that the new plan wouldn't get stalled for the same reasons. If it's possible to gather more data during an episode of a slow run, particularly the pg_stat_activity and pg_locks lists, run as the database superuser, it would help pin down the cause. A vmstat during such an episode, to compare to a "normal" one, might also be instructive. -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
