Re: Creating Index - Mailing list pgsql-sql

Hi!

I thought I have got no more question in this thread, and I was wrong :-(
Is the extra 300ms in UNION essential?

Best Regards,
CN

--This table contains 1036 rows.
CREATE TABLE table1 (
c1  VARCHAR(20) PRIMARY KEY,
c2  "char"
)WITHOUT OIDS;
---------------------
--This table contains 9429 rows.
CREATE TABLE table2 (
c1  VARCHAR(20) PRIMARY KEY,
c2  DATE
)WITHOUT OIDS;
---------------------
--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  INTEGER
)WITHOUT OIDS;
---------------------
--This table contains 0 row.
CREATE TABLE table4 (
PRIMARY KEY (c1,c2),
c1  INTEGER,   --year
c2  INTEGER,   --month
c3  INTEGER
)WITHOUT OIDS;
---------------------
EXPLAIN ANALYZE
SELECT table2.c2 AS date ,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 table2,table3,table1
WHERE table3.c1=table2.c1 AND table1.c1=table3.c3

UNION ALL

SELECT TO_DATE(c1 || ' ' || c2 || ' 1','YYYY MM DD'),c3 FROM table4;
---------------Append  (cost=199.64..2446.56 rows=28483 width=54) (actualtime=154.06..2200.53 rows=28482 loops=1)  ->
SubqueryScan "*SELECT* 1"  (cost=199.64..2446.56 rows=28482  width=54) (actual time=154.05..2116.96 
 
rows=28482 loops=1)        ->  Hash Join  (cost=199.64..2446.56 rows=28482 width=54)        (actual
time=154.03..1941.40rows=28482 
 
loops=1)              Hash Cond: ("outer".c1 = "inner".c1)              ->  Hash Join  (cost=18.95..1411.41 rows=28482
width=37)             (actual time=22.85..934.94 rows=28482 
 
loops=1)                    Hash Cond: ("outer".c3 = "inner".c1)                    ->  Seq Scan on table3
(cost=0.00..822.82                   rows=28482 width=27) (actual 
 
time=8.26..403.61 rows=28482 loops=1)                    ->  Hash  (cost=16.36..16.36 rows=1036 width=10)
    (actual time=14.18..14.18 rows=0 
 
loops=1)                          ->  Seq Scan on table1  (cost=0.00..16.36                          rows=1036
width=10)(actual 
 
time=0.23..8.26 rows=1036 loops=1)              ->  Hash  (cost=157.55..157.55 rows=9255 width=17) (actual
time=130.63..130.63rows=0 loops=1)                    ->  Seq Scan on table2  (cost=0.00..157.55 rows=9255
     width=17) (actual time=0.24..78.22 
 
rows=9255 loops=1)  ->  Subquery Scan "*SELECT* 2"  (cost=0.00..0.00 rows=1 width=12)  (actual time=0.02..0.02 rows=0
loops=1)       ->  Seq Scan on table4  (cost=0.00..0.00 rows=1 width=12)        (actual time=0.01..0.01 rows=0
loops=1)Totalruntime: 2259.10 msec
 
---------------------
EXPLAIN ANALYZE
SELECT table2.c2 AS date ,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 table2,table3,table1
WHERE table3.c1=table2.c1 AND table1.c1=table3.c3;
---------------Hash Join  (cost=199.64..2446.56 rows=28482 width=54) (actualtime=155.37..1857.25 rows=28482 loops=1)
HashCond: ("outer".c1 = "inner".c1)  ->  Hash Join  (cost=18.95..1411.41 rows=28482 width=37) (actual
time=23.08..891.03rows=28482 loops=1)        Hash Cond: ("outer".c3 = "inner".c1)        ->  Seq Scan on table3
(cost=0.00..822.82rows=28482 width=27)        (actual time=8.12..386.64 rows=28482 
 
loops=1)        ->  Hash  (cost=16.36..16.36 rows=1036 width=10) (actual        time=14.58..14.58 rows=0 loops=1)
      ->  Seq Scan on table1  (cost=0.00..16.36 rows=1036              width=10) (actual time=0.26..9.19 
 
rows=1036 loops=1)  ->  Hash  (cost=157.55..157.55 rows=9255 width=17) (actual  time=131.84..131.84 rows=0 loops=1)
  ->  Seq Scan on table2  (cost=0.00..157.55 rows=9255 width=17)        (actual time=0.24..77.11 rows=9255 
 
loops=1)Total runtime: 1915.33 msec

-- 
http://www.fastmail.fm - A no graphics, no pop-ups email service