Re: Creating Index

Stephan and Tom,
Many thanks!

I am trying to explain the meaning of tables and views:
The tables in the first SELECT in the UNION of view1 are jorunal, whose
rows are entered daily.
The table in the second SELECT in the UNION of view1 is budget, whose
rows use year+month as key.
View1 unions journal and budget to present users a pseudo table that can
be selected to produce comparison reports - budget vs. actual.
User will be prompted to enter the interested year+month before selecting
the view.

I think I have found out a way to make the index from year and month.
Although this approach produces reduntant data but it should be feasible:
- Create 2 more columns, year and month for table2.
- Feed NEW.c3 and NEW.f4 with values EXTRACT(YEAR FROM c2) and
EXTRACT(MONTH FROM c2), respectively in
plpgsql trigger function.
- Create composit index for year+month.

There is still one thing I don't know why - query on view1 being
extermely slow. I also removed the subquery
from view1 to form view2. The query on view2 is swift. (postgresql
v7.3.2)
Both queries return 0 rows. The results are correct because table2
contains data of years on or before 2003
and table4 contains no rows.

Again, I would much appreciate any idea helping me speed up view1.

One off-topic issue is that I wish postgresql could be smarter to make
use of the index even there are INT2 columns in composit index. As my
case shows, INT2 is big enough for columns year and month, isn't it?

Best Regards,
CN
---------------------
--This table contains 1036 rows.
CREATE TABLE table1 (
c1 VARCHAR(20) PRIMARY KEY,
c2 "char"
)WITHOUT OIDS;
---------------------
--This table contains 9255 rows.
CREATE TABLE table2 (
c1 VARCHAR(20) PRIMARY KEY,
c2 DATE,
c3 INTEGER, --year part of c2
c4 INTEGER --month part of c2
)WITHOUT OIDS;
CREATE INDEX i2c3c4 ON table2 (c3,c4);
---------------------
--This table contains 28482 rows.
CREATE TABLE table3 (
CONSTRAINT fk2c1 FOREIGN KEY (c1) REFERENCES table2 (c1) ON UPDATE
CASCADE ON DELETE CASCADE,
CONSTRAINT fk2c3 FOREIGN KEY (c3) REFERENCES table1 (c1),
PRIMARY KEY (c1,c2),
c1 VARCHAR(20),
c2 INTEGER,
c3 VARCHAR(20),
c4 "char",
c5 NUMERIC --amount
)WITHOUT OIDS;
---------------------
--This table contains 0 row.
CREATE TABLE table4 (
PRIMARY KEY (c1,c2),
c1 INTEGER, --year
c2 INTEGER, --month
c3 INTEGER
)WITHOUT OIDS;
---------------------
CREATE OR REPLACE VIEW view1 AS
SELECT
table2.c3 AS year
,table2.c4 AS month
,(SELECT CASE
WHEN (table1.c2 = 'A' OR table1.c2 = 'E') AND table3.c4 = 'D'
OR table1.c2 IN ('L','O','I') AND table3.c4 = 'C'
THEN table3.c5 ELSE 0-table3.c5 END
AS amount
FROM table1
WHERE table1.c1=table3.c3
)
FROM table2,table3
WHERE table3.c1=table2.c1

UNION ALL

SELECT c1,c2,c3 FROM table4;
---------------------
CREATE OR REPLACE VIEW view2 AS
SELECT table2.c3 AS year,table2.c4 AS month
FROM table2,table3
WHERE table3.c1=table2.c1

UNION ALL

SELECT c1,c2 FROM table4;
---------------------
---------------------
EXPLAIN ANALYZE SELECT * FROM view1 WHERE year > 2003;
---------------------
Subquery Scan view1 (cost=0.00..141.50 rows=2000 width=185) (actual
time=4348.92..4348.92 rows=0 loops=1)
Filter: ("year" > 2003)
-> Append (cost=0.00..141.50 rows=2000 width=185) (actual
time=2.65..4230.44 rows=28482 loops=1)
-> Subquery Scan "*SELECT* 1" (cost=0.00..121.50 rows=1000
width=185) (actual time=2.64..4127.71
rows=28482 loops=1)
-> Merge Join (cost=0.00..121.50 rows=1000 width=185)
(actual time=2.62..3875.23 rows=28482
loops=1)
Merge Cond: ("outer".c1 = "inner".c1)
-> Index Scan using table2_pkey on table2
(cost=0.00..52.00 rows=1000 width=56)
(actual time=0.81..183.37 rows=9255 loops=1)
-> Index Scan using table3_pkey on table3
(cost=0.00..52.00 rows=1000 width=129)
(actual time=0.74..649.32 rows=28482 loops=1)
SubPlan
-> Index Scan using table1_pkey on table1
(cost=0.00..4.82 rows=1 width=1) (actual
time=0.07..0.07 rows=1 loops=28482)
Index Cond: (c1 = $2)
-> Subquery Scan "*SELECT* 2" (cost=0.00..20.00 rows=1000
width=12) (actual time=0.02..0.02 rows=0
loops=1)
-> Seq Scan on table4 (cost=0.00..20.00 rows=1000
width=12) (actual time=0.01..0.01 rows=0
loops=1)
Total runtime: 4350.24 msec
---------------------
---------------------
EXPLAIN ANALYZE SELECT * FROM view2 WHERE year > 2003;
---------------------
Subquery Scan view2 (cost=36.47..119.30 rows=667 width=104) (actual
time=40.90..40.90 rows=0 loops=1)
-> Append (cost=36.47..119.30 rows=667 width=104) (actual
time=40.88..40.88 rows=0 loops=1)
-> Subquery Scan "*SELECT* 1" (cost=36.47..96.80 rows=333
width=104) (actual time=40.85..40.85
rows=0 loops=1)
-> Merge Join (cost=36.47..96.80 rows=333 width=104)
(actual time=40.84..40.84 rows=0
loops=1)
Merge Cond: ("outer".c1 = "inner".c1)
-> Index Scan using table3_pkey on table3
(cost=0.00..52.00 rows=1000 width=48)
(actual time=0.52..0.52 rows=1 loops=1)
-> Sort (cost=36.47..37.30 rows=333 width=56)
(actual time=40.30..40.30 rows=0 loops=1)
Sort Key: table2.c1
-> Seq Scan on table2 (cost=0.00..22.50
rows=333 width=56) (actual
time=38.65..38.65 rows=0 loops=1)
Filter: (c3 > 2003)
-> Subquery Scan "*SELECT* 2" (cost=0.00..22.50 rows=333
width=8) (actual time=0.02..0.02 rows=0
loops=1)
-> Seq Scan on table4 (cost=0.00..22.50 rows=333
width=8) (actual time=0.01..0.01 rows=0
loops=1)
Filter: (c1 > 2003)
Total runtime: 41.86 msec

--
http://www.fastmail.fm - Send your email first class