pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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! +++




view thread (7+ messages)  latest in thread

Message-ID: <3603.1065030329@www60.gmx.net>
Permalink:  ../3603.1065030329@www60.gmx.net/
Also on:    postgresql.org/message-id/3603.1065030329@www60.gmx.net

 · 

reply

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