Re: Optimizing >= and <= for numbers and dates

here goes the EXPLAIN ANALYZE output:

template1=# VACUUM analyze mytable;
VACUUM
template1=# explain analyze select * from mytable where
mydate>='2003-09-01';
QUERY PLAN

---------------------------------------------------------------------------------------------------------------
Seq Scan on mytable (cost=0.00..2209.11 rows=22274 width=562) (actual
time=0.06..267.30 rows=22677 loops=1)
Filter: (mydate >= '2003-09-01'::date)
Total runtime: 307.71 msec
(3 rows)

template1=# explain analyze select * from mytable where mydate='2003-09-01';
QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------
Index Scan using mytable_query on mytable (cost=0.00..148.56 rows=43
width=562) (actual time=41.22..41.27 rows=4 loops=1)
Index Cond: (mydate = '2003-09-01'::date)
Total runtime: 41.34 msec
(3 rows)

> On Wed, 2003-10-01 at 13:30, Dimitri Nagiev wrote:
> > Hi all,
> >
> > I haven't found any official documentation about the postgres sql
> optimiz
> er
> > on the web, so please forgive me if there is such a document and point
> me
> to
> > the right direction.
> >
> > I've got the following problem: I cannot make the postgres SQL Optimizer
> use
> > an index on a date field to filter out a date range, e.g.
> >
> > select * from mytable where mydate >= '2003-10-01';
> >
> > Seq Scan on mytable (cost=0.00..2138.11 rows=12203 width=543)
> > Filter: (mydate >= '2003-09-01'::date)
>
> EXPLAIN ANALYZE output please.
>

--
NEU FÜR ALLE - GMX MediaCenter - für Fotos, Musik, Dateien...
Fotoalbum, File Sharing, MMS, Multimedia-Gruß, GMX FotoService

Jetzt kostenlos anmelden unter http://www.gmx.net

+++ GMX - die erste Adresse für Mail, Message, More! +++