Re: Query performance discontinuity

Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> writes:
>> pganalysis=> explain analyze select * from ps2 where tstart<> '2000-1-1
>> 00:00:00' and time_stamp > '2000-1-1 00:00:00' order by
>> tstart,time_stamp limit 59625;
>> NOTICE: QUERY PLAN:
>>
>> Limit (cost=0.00..160331.06 rows=59625 width=179) (actual
>> time=0.45..2212.19 rows=59625 loops=1)
>> -> Index Scan using ps2_idx on ps2 (cost=0.00..881812.85 rows=327935
>> width=179) (actual time=0.45..2140.87 rows=59626 loops=1)
>> Total runtime: 2254.50 msec

> I believe that the query is using the index to avoid a sort, but
> possibly/probably not to do the condition.

Certainly not to do the condition, because <> is not an indexable
operator. Would it be possible to express the tstart condition as
tstart > '2000-1-1 00:00:00' ?

The other thing that's pretty obvious is that the cost of the indexscan
plan is drastically overestimated relative to the seqscan/sort plan.
It might be worth experimenting with lowering random_page_cost to see
if that helps. I'm also curious to know whether the table is likely to
be nearly in order by tstart/time_stamp --- we know that the effects
of index-order correlation aren't modeled very well in 7.2.

Finally, it might be worth increasing sort_mem, if it's at the default
presently.

regards, tom lane