Re: Creating Index - Mailing list pgsql-sql

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
FROMtable1   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) (actualtime=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=28482loops=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.23rows=28482 
 
loops=1)                    Merge Cond: ("outer".c1 = "inner".c1)                    ->  Index Scan using table2_pkey
ontable2                     (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_pkeyon 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.01rows=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) (actualtime=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)        ->
SubqueryScan "*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.84rows=0 
 
loops=1)                    Merge Cond: ("outer".c1 = "inner".c1)                    ->  Index Scan using table3_pkey
ontable3                     (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