From: A. Kretschmer <andreas.kretschmer@schollglas.com>
To: pgsql-sql@postgresql.org
Subject: Re: Index lookup on > and < criteria
Date: Tue, 1 Nov 2005 20:12:00 +0100
Message-ID: <20051101191200.GA2934@webserv.wug-glas.de> (raw)
In-Reply-To: <4367B16B.2050003@vailsys.com>
References: <4367B16B.2050003@vailsys.com>
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.591 rows=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 ===
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: andreas.kretschmer@schollglas.com
Subject: Re: Index lookup on > and < criteria
In-Reply-To: <20051101191200.GA2934@webserv.wug-glas.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox