Re: Query performance discontinuity

Mike,

> Yes, I'd run a VACUUM FULL ANALYZE -- I did it again just to make sure,
> and re-ran the query (similar result):
>
> pganalysis=> explain analyze select * from ps2 where tstart> '2000-1-1
> pganalysis'> 00:00:00' and time_stamp > '2000-1-1 00:00:00' order by
> pganalysis-> tstart,time_stamp limit 59628;
> NOTICE:  QUERY PLAN:
>
> Limit  (cost=160313.27..160313.27 rows=59628 width=179) (actual
> time=45405.47..46320.12 rows=59628 loops=1)
>   ->  Sort  (cost=160313.27..160313.27 rows=327895 width=179) (actual
> time=45405.46..46248.31 rows=59629 loops=1)
>         ->  Seq Scan on ps2  (cost=0.00..13783.40 rows=327895 width=179)
> (actual time=13.52..17111.66 rows=327960 loops=1)
> Total runtime: 46894.21 msec
>
> EXPLAIN
>
> Unfortunately, I have not yet had time to experiment with twiddling the
> query optimizer parameters or memory -- my apologies for this, but,
> well, a guy's gotta eat...

Well, I''ll just concur with what others have said: for some reason, the
parser is slightly underestimateing the cost of a seq scan, and dramatically
overestimating the cost of an index scan, for this query.

Other than tweaking to parser calculation values, I'd suggest dropping and
re-building the index just for thouroughness.

--
-Josh Berkus
 Aglio Database Solutions
 San Francisco