From: CN <cnliou9@fastmail.fm>
To: pgsql-sql@postgresql.org
Subject: Creating Index
Date: Wed, 01 Oct 2003 03:47:46 -0800
Message-ID: <20031001114746.CAC7E74708@smtp.us2.messagingengine.com> (raw)
Hi!
CREATE TABLE table1
( d DATE PRIMARY KEY,
amount INTEGER
);
CREATE TABLE table2
( PRIMARY KEY (y,m),
y INTEGER,
m INTEGER
amount INTEGER
);
CREATE VIEW view1 AS
SELECT EXTRACT(YEAR FROM d) AS year, EXTRACT(MONTH FROM d) AS month,
amount
UNION ALL
SELECT * from table2;
Table1 contains 9000 rows and table2 contains 0 row. This query, which
takes 13489 msec, is extremely slow as pgsql sequentially scans all rows
in table1:
EXPLAIN ANALYZE SELECT COUNT(*) FROM view1;
I am in the impression that building an index on column d surely will
help improve the performance but I am not smart enough to apply its usage
explained in the manual.
I would much appreciate if anyone could show me how to build that index
something similar to (I guess) the following query (which is illegal of
course):
CREATE INDEX index1 ON table1 EXTRACT(YEAR FROM d) || EXTRACT(MONTH FROM
d);
TIA
CN
--
http://www.fastmail.fm - Faster than the air-speed velocity of an
unladen european swallow
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: <20031001114746.CAC7E74708@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