Query performance discontinuity - Mailing list pgsql-performance

pgsql-performers,

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?

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 59624;
NOTICE:  QUERY PLAN:

Limit  (cost=0.00..160328.37 rows=59624 width=179) (actual
time=14.52..2225.16 rows=59624 loops=1)
  ->  Index Scan using ps2_idx on ps2  (cost=0.00..881812.85 rows=327935
width=179) (actual time=14.51..2154.59 rows=59625 loops=1)
Total runtime: 2265.93 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 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

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 59627;
NOTICE:  QUERY PLAN:

Limit  (cost=160332.32..160332.32 rows=59627 width=179) (actual
time=38084.85..38260.88 rows=59627 loops=1)
  ->  Sort  (cost=160332.32..160332.32 rows=327935 width=179) (actual
time=38084.83..38194.63 rows=59628 loops=1)
        ->  Seq Scan on ps2  (cost=0.00..13783.52 rows=327935 width=179)
(actual time=0.15..12174.74 rows=327960 loops=1)
Total runtime: 38611.83 msec

EXPLAIN

pganalysis=> \d ps2
                    Table "ps2"
   Column    |           Type           | Modifiers
-------------+--------------------------+-----------
 host        | character varying(255)   |
 pid         | integer                  |
 line        | integer                  |
 time_stamp  | timestamp with time zone |
 seq         | integer                  |
 cpu_sys     | real                     |
 cpu_elapsed | real                     |
 cpu_user    | real                     |
 cpu_syst    | real                     |
 cpu_usert   | real                     |
 mssp        | integer                  |
 sigp        | integer                  |
 msrt        | integer                  |
 msst        | integer                  |
 sigt        | integer                  |
 msrp        | integer                  |
 swap        | integer                  |
 swat        | integer                  |
 recp        | integer                  |
 rect        | integer                  |
 pgfp        | integer                  |
 pgft        | integer                  |
 icsp        | integer                  |
 vcst        | integer                  |
 icst        | integer                  |
 vcsp        | integer                  |
 fsbop       | integer                  |
 fsbos       | integer                  |
 fsbip       | integer                  |
 fsbis       | integer                  |
 dread       | integer                  |
 dwrit       | integer                  |
 sbhr        | real                     |
 sread       | integer                  |
 swrit       | integer                  |
 lbhr        | real                     |
 lread       | integer                  |
 lwrit       | integer                  |
 dbuser      | character(8)             |
 tstart      | timestamp with time zone |
Indexes: ps2_idx

pganalysis=> \d ps2_idx
            Index "ps2_idx"
   Column   |           Type
------------+--------------------------
 tstart     | timestamp with time zone
 time_stamp | timestamp with time zone
btree

pganalysis=>

psql (PostgreSQL) 7.2
contains support for: readline, history, multibyte


Platform: Celeron 1.3GHz, 512MB 40GB IDE hard disk, Linux 2.4.8-26mdk
kernel

Regards,

Mike