Re: Query performance discontinuity

On 12 Nov 2002, Mike Nielsen wrote:

> Just out of curiosity, anybody with any ideas on what happens to this
> query when the limit is 59626?  It's as though 59626 = infinity?

> EXPLAIN
> 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
>
> EXPLAIN
> 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 59626;
> NOTICE:  QUERY PLAN:
>
> Limit  (cost=160332.32..160332.32 rows=59626 width=179) (actual
> time=37359.41..37535.85 rows=59626 loops=1)
>   ->  Sort  (cost=160332.32..160332.32 rows=327935 width=179) (actual
> time=37359.40..37471.07 rows=59627 loops=1)
>         ->  Seq Scan on ps2  (cost=0.00..13783.52 rows=327935 width=179)
> (actual time=0.26..12433.00 rows=327960 loops=1)
> Total runtime: 38477.39 msec

This is apparently the breakpoint at which the sequence scan/sort/limit
max cost seems to become lower than indexscan/limit given the small
difference in estimated costs.  What do you get with limit 59626 and
enable_seqscan=off?  My guess is that it's just above the 160332.32
estimated here.
I believe that the query is using the index to avoid a sort, but
possibly/probably not to do the condition. I'd wonder if analyzing with
more buckets might get it a better idea, but I really don't know.
Another option is to see what making an index on (time_stamp, tstart)
gives you, but if most of the table meets the time_stamp condition,
that wouldn't help any.