X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id A9405D1B500 for ; Wed, 1 Oct 2003 17:31:34 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 82389-09 for ; Wed, 1 Oct 2003 14:30:48 -0300 (ADT) Received: from mail.gmx.net (pop.gmx.de [213.165.64.20]) by svr1.postgresql.org (Postfix) with SMTP id 205E7D1B4EF for ; Wed, 1 Oct 2003 14:30:47 -0300 (ADT) Received: (qmail 13938 invoked by uid 0); 1 Oct 2003 17:30:47 -0000 Received: from 212.202.242.130 by www60.gmx.net with HTTP; Wed, 1 Oct 2003 19:30:47 +0200 (MEST) Date: Wed, 1 Oct 2003 19:30:47 +0200 (MEST) From: "Dimitri Nagiev" To: pgsql-performance@postgresql.org MIME-Version: 1.0 Subject: Optimizing >= and <= for numbers and dates X-Priority: 3 (Normal) X-Authenticated: #19654632 Message-ID: <24741.1065029447@www60.gmx.net> X-Mailer: WWW-Mail 1.6 (Global Message Exchange) X-Flags: 0001 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 8bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.9 tagged_above=0.0 required=5.0 tests=RCVD_IN_OSIRUSOFT_COM X-Spam-Level: X-Archive-Number: 200310/26 X-Sequence-Number: 3774 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! +++