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 ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
