X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 3B66AD1B513 for ; Thu, 2 Oct 2003 02:41:12 +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 80835-03 for ; Wed, 1 Oct 2003 23:40:27 -0300 (ADT) Received: from mail.messagingengine.com (out2.smtp.messagingengine.com [66.111.4.26]) by svr1.postgresql.org (Postfix) with ESMTP id AC9F2D1B50E for ; Wed, 1 Oct 2003 23:40:17 -0300 (ADT) Received: from smtp.us2.messagingengine.com (localhost [127.0.0.1]) by localhost.localdomain (Postfix) with ESMTP id CF01125352C for ; Wed, 1 Oct 2003 22:40:19 -0400 (EDT) Received: from 10.202.2.133 ([10.202.2.133] helo=smtp.us2.messagingengine.com) by messagingengine.com with SMTP; Wed, 01 Oct 2003 22:40:19 -0400 Received: by smtp.us2.messagingengine.com (Postfix, from userid 99) id 295C37A25A; Wed, 1 Oct 2003 22:40:19 -0400 (EDT) Content-Disposition: inline Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset="Big5" MIME-Version: 1.0 X-Mailer: MIME::Lite 1.2 (F2.71; T1.001; A1.51; B2.12; Q2.03) From: "CN" To: pgsql-sql@postgresql.org Date: Wed, 01 Oct 2003 18:40:19 -0800 X-Epoch: 1065062419 X-Sasl-enc: 4IR88E/vuaDJe3mePLkcSQ Subject: Re: Creating Index References: <20031001152542.8C9C76F692@smtp.us2.messagingengine.com> <20031001110830.O40704@megazone.bigpanda.com> In-Reply-To: <20031001110830.O40704@megazone.bigpanda.com> Message-Id: <20031002024019.295C37A25A@smtp.us2.messagingengine.com> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/20 X-Sequence-Number: 15420 > You do realize that extract returns a double precision value not an > integer, and it's probably not going to be willing to push clauses down > through the union where the types are different . > Argh! I didn't noticed that. Thanks for the reminder. Let's do not consider table2 and view1 for this moment and focus only on table1. Table1 in my original post was incorrect. Please forgive me! (I posted it midnight when my head was not clear and tried to make my case simple for understanding.) The correct one is: CREATE TABLE table1 ( id VARCHAR(20) PRIMARY KEY, d DATE, amount INTEGER ); CREATE INDEX itable1 ON table1 (d); EXPLAIN ANALYZE SELECT COUNT(*) FROM table1 WHERE EXTRACT(YEAR FROM d) >= 2001.0 AND EXTRACT(MONTH FROM d) >= 1.; takes 630 msec on my AMD 450MHz machine. While EXPLAIN ANALYZE SELECT COUNT(*) FROM table1 WHERE d >= '2001-1-1'; takes only 114 msec. ------------------ Aggregate (cost=535.20..535.20 rows=1 width=0) (actual time=625.10..625.11 rows=1 loops=1) -> Seq Scan on table1 (cost=0.00..532.58 rows=1048 width=0) (actual time=14.84..605.85 rows=3603 loops=1) Filter: ((date_part('year'::text, f2) > 2001::double precision) AND (date_part('month'::text, f2) >= 1::double precision)) Total runtime: 626.61 msec ----------------------- Aggregate (cost=464.12..464.12 rows=1 width=0) (actual time=114.28..114.28 rows=1 loops=1) -> Seq Scan on table1 (cost=0.00..461.86 rows=902 width=0) (actual time=10.71..102.99 rows=3603 loops=1) Filter: (f2 >= '2002-01-01'::date) Total runtime: 114.50 msec Does the first query perform sequential scan? If a composit index (year,month) derived from column "d" helps and is available, then someone please show me how to build that index like: CREATE INDEX i1 ON table1 Is creating a function that eats DATE as argument to build that index my only solution? Best Regards, CN -- http://www.fastmail.fm - The professional email service