Re: Creating Index - Mailing list pgsql-sql

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
>