"Subbiah Stalin-XCGF84" <SSubbiah@Motorola.com> 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