From: CN <cnliou9@fastmail.fm>
To: pgsql-sql@postgresql.org
Subject: Re: Creating Index
Date: Wed, 01 Oct 2003 18:40:19 -0800
Message-ID: <20031002024019.295C37A25A@smtp.us2.messagingengine.com> (raw)
In-Reply-To: <20031001110830.O40704@megazone.bigpanda.com>
References: <Pine.LNX.4.44.0310011419060.2932-100000@peter.localdomain>
<20031001152542.8C9C76F692@smtp.us2.messagingengine.com>
<20031001110830.O40704@megazone.bigpanda.com>
> 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 <EXTRACT(YEAR FROM d)::TEXT || EXTRACT(MONTH
FROM d)::TEXT>
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
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: cnliou9@fastmail.fm
Subject: Re: Creating Index
In-Reply-To: <20031002024019.295C37A25A@smtp.us2.messagingengine.com>
* 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