Content-Disposition: inline
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset="Big5"
MIME-Version: 1.0
From: "CN" <cnliou9@fastmail.fm>
To: pgsql-sql@postgresql.org
Date: Wed, 01 Oct 2003 03:47:46 -0800
Subject: Creating Index
Message-Id: <20031001114746.CAC7E74708@smtp.us2.messagingengine.com>

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