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 8064CD1B55C for ; Wed, 1 Oct 2003 17:46:16 +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 85951-01 for ; Wed, 1 Oct 2003 14:45:30 -0300 (ADT) Received: from mail.gmx.net (pop.gmx.de [213.165.64.20]) by svr1.postgresql.org (Postfix) with SMTP id 02414D1B503 for ; Wed, 1 Oct 2003 14:45:29 -0300 (ADT) Received: (qmail 10891 invoked by uid 0); 1 Oct 2003 17:45:29 -0000 Received: from 212.202.242.130 by www60.gmx.net with HTTP; Wed, 1 Oct 2003 19:45:29 +0200 (MEST) Date: Wed, 1 Oct 2003 19:45:29 +0200 (MEST) From: "Dimitri Nagiev" To: pgsql-performance@postgresql.org MIME-Version: 1.0 References: <1065029881.85344.138.camel@jester> Subject: Re: Optimizing >= and <= for numbers and dates X-Priority: 3 (Normal) X-Authenticated: #19654632 Message-ID: <3603.1065030329@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.5 tagged_above=0.0 required=5.0 tests=QUOTED_EMAIL_TEXT, RCVD_IN_OSIRUSOFT_COM, REFERENCES X-Spam-Level: X-Archive-Number: 200310/31 X-Sequence-Number: 3779 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! +++