Re: Creating Index

On Thu, 2 Oct 2003, CN wrote:

> 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.

So, in practice you'll actually be doing queries with equality rather than
ranges?

> 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.

Well, you should probably again analyze the tables. I think it's running
on default statistics again. It might do better with stats.

> ---------------------
> --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
>