Re: Changing optimizations

Hmm.  Nothing out of the ordinary about the table schemas.  The relevant
bits of the query seem to be

>         FROM percept p
>              INNER JOIN perceptType pt ON pt.ptid=p.ptid
>                AND pt.runinterval IS NOT NULL

>        WHERE p.deleted=0 AND UNIX_TIMESTAMP( p.nextrun )<=NOW() AND
>              pt.runinterval IS NOT NULL AND p.running=0 AND h.active=1

What seems to be happening is that as you repeatedly VACUUM ANALYZE,
the statistics shift causing a shift in the estimated number of percept
rows that match the WHERE clauses.  As that estimate rises, you get a
change in the selected plan types for the later joins, in a direction
that isn't favorable if the correct number of rows is small.

But it seems odd that you'd get a factor-of-100 change in that estimate
if the true underlying data distribution isn't changing much.  Could
you keep track of the results of these two queries:

select * from pg_class where relname = 'percept';

select attname,attdispersion,s.*
from pg_statistic s, pg_attribute a, pg_class c
where starelid = c.oid and attrelid = c.oid and staattnum = attnum
and relname = 'percept';

(this is for 7.1, s/attdispersion/attdisbursion/ if you're using 7.0)
and see how they change between the state where you're getting a good
plan and the state where you're getting a not-so-good plan?

Another possibility is that what looks to be the same bottom-level join
plan isn't really the same, but is using different restriction/join
clauses for some weird reason.  It would be good to look at EXPLAIN
VERBOSE output not just EXPLAIN output for the two plans, just to rule
that out.

            regards, tom lane