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
