From: Dimitri Nagiev <dnagiev@gmx.de>
To: pgsql-performance@postgresql.org
Subject: Re: Optimizing >= and <= for numbers and dates
Date: Wed, 1 Oct 2003 19:45:29 +0200 (MEST)
Message-ID: <3603.1065030329@www60.gmx.net> (raw)
References: <1065029881.85344.138.camel@jester>
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! +++
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: dnagiev@gmx.de
Subject: Re: Optimizing >= and <= for numbers and dates
In-Reply-To: <3603.1065030329@www60.gmx.net>
References: <1065029881.85344.138.camel@jester> <3603.1065030329@www60.gmx.net>
* 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