Re: Index lookup on > and < criteria

am  01.11.2005, um 12:18:19 -0600 mailte David Durham folgendes:
> Apologies if this questions is asked often.  I'm doing some select 
> statements based on a timestamp field.  I have an index on the field, and 
> when I use the '=' operator the index is used.  However, if I use the '>' 
> or '<' operators, then it does a full table scan.  I've got around 6 
> million rows, so I would think that an index scan would be more 
> appropriate.
> 
> 
> Here are the statements I'm looking at:
> 
>     select * from myTable where myTimeStamp = '10/1/2005';
> 
> uses an index.
> 
>     select max(myTimeStamp) from myTable;
>     select * from myTable where myTimeStamp < '10/2/2005';
>     select * from myTable where myTimeStamp < '10/2/2005' and myTimeStamp
>         >= '10/1/2005';
> 
> do not use indexes.  Can anyone point me to some info about what's going 
> on?  I've started reading through the manual (chapter 13) which I think 

Nice question. My guess:

The planner fels its better to use seq-scan. My test:

,----[  Test  ]
| Produktionsreport=# explain select * from bde_meldungen where zeitpunkt > '2005-08-30'::timestamp;
|                                  QUERY PLAN
| ----------------------------------------------------------------------------
|  Seq Scan on bde_meldungen  (cost=0.00..33862.46 rows=55106 width=139)
|    Filter: (zeitpunkt > '2005-08-30 00:00:00'::timestamp without time zone)
| (2 Zeilen)
|
| Produktionsreport=# explain select * from bde_meldungen where zeitpunkt > '2005-09-30'::timestamp;
|                                         QUERY PLAN
| -------------------------------------------------------------------------------------------
|  Index Scan using bde_zeitpunkt on bde_meldungen  (cost=0.00..8255.23 rows=9521 width=139)
|    Index Cond: (zeitpunkt > '2005-09-30 00:00:00'::timestamp without time zone)
| (2 Zeilen)
`----

Sorry about the german column names, 'zeitpunkt' is a timestamp. On the
first query the result set is estimeted 55.000 rows long -> seq-scan.
The second test: estimated to rows=9521 -> index scan.


Btw.: min/max cant use index, this is coming with 8.1. I'm using for
examples above 7.4.6.


PS.: you can use set ..., example:

,----[  Test with set enable_seqscan=... ]
| Produktionsreport=# set enable_seqscan=on;
| SET
| Produktionsreport=# explain analyse select * from bde_meldungen where zeitpunkt > '2005-08-30'::timestamp;
|                                                         QUERY PLAN
|
---------------------------------------------------------------------------------------------------------------------------
|  Seq Scan on bde_meldungen  (cost=0.00..33862.46 rows=55106 width=139) (actual time=2574.004..4892.563 rows=99915
loops=1)
|    Filter: (zeitpunkt > '2005-08-30 00:00:00'::timestamp without time zone)
|  Total runtime: 4971.179 ms
| (3 Zeilen)
|
| Produktionsreport=# set enable_seqscan=off;
| SET
| Produktionsreport=# explain analyse select * from bde_meldungen where zeitpunkt > '2005-08-30'::timestamp;
|                                                                   QUERY PLAN
|
-----------------------------------------------------------------------------------------------------------------------------------------------
|  Index Scan using bde_zeitpunkt on bde_meldungen  (cost=0.00..47679.39 rows=55106 width=139) (actual
time=57.387..1649.591rows=99915 loops=1)
 
|    Index Cond: (zeitpunkt > '2005-08-30 00:00:00'::timestamp without time zone)
|  Total runtime: 1729.420 ms
| (3 Zeilen)
`----


Now it using the index _and_ it is faster!


HTH, Andreas
-- 
Andreas Kretschmer    (Kontakt: siehe Header)
Heynitz:  035242/47212,      D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net===    Schollglas Unternehmensgruppe    ===