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: Optimizing >= and <= for numbers and dates
Date: Wed, 1 Oct 2003 19:30:47 +0200 (MEST)
Message-ID: <24741.1065029447@www60.gmx.net> (raw)

Hi all,

I haven't found any official documentation about the postgres sql optimizer
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)


the index is created as follows:

create index query on mytable(mydate);

Testing for equality gives me the index optimization:

select * from mytable where mydate = '2003-10-01';

Index Scan using query on mytable  (cost=0.00..54.93 rows=44 width=543)
   Index Cond: (mydate = '2003-09-01'::date)


I have run vacuum analyze on the table. Also the table contains 25.000
records, so the index should be used in my opinion. Am I missing something ?
The
same seems to apply to integers. 

Thank you very much in advance
Dimi

PS The postgres version is as follows:

 PostgreSQL 7.3.2 on i386-redhat-linux-gnu, compiled by GCC
i386-redhat-linux-gcc (GCC) 3.2.2 20030213 (Red Hat Linux 8.0 3.2.2-1)



-- 
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: <24741.1065029447@www60.gmx.net>
Permalink:  ../24741.1065029447@www60.gmx.net/
Also on:    postgresql.org/message-id/24741.1065029447@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: <24741.1065029447@www60.gmx.net>
  References: <24741.1065029447@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