[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    [GENERAL] help me
From:       Paolo Tavalazzi <ptavalazzi () charta ! it>
Date:       2004-07-21 11:47:02
Message-ID: 200407211347.02677.ptavalazzi () charta ! it
[Download RAW message or body]

I have a problem on FROM subselect that i don't understand.
 
 
I do two query different only for a WHERE clause in a FROM subquery .
 
1) explain analyze
SELECT DISTINCT ON (spettacoli.teatro,spettacoli.code) 
                           spettacoli.teatro,spettacoli.code,     
                           scnf_spettacoli.scnf_gruppo 
,scnf_spettacoli.scnf_client,
                           scnf_spettacoli.scnf_client 
,scnf_spettacoli.scnf_code 
   
  FROM spettacoli LEFT JOIN scnf_spettacoli ON  (scnf_spettacoli.scnf_code in 
(spettacoli.code,'*') AND
                                                                        
(scnf_spettacoli.scnf_teatro = spettacoli.teatro OR scnf_spettacoli = '*') 
AND
                                                                         
spettacoli.system = scnf_spettacoli.scnf_system  AND 
                                                                         
scnf_spettacoli.scnf_gruppo in ('leoni','*') AND
                                                                        
scnf_spettacoli.scnf_client in ('paolo','*')) 
  WHERE
    spettacoli.system    = 0 AND
    spettacoli.flag      != 0 AND
    spettacoli.orarioinizio < '200407141219'
  ORDER BY spettacoli.teatro,spettacoli.code, scnf_spettacoli.scnf_gruppo DESC 
,
                   scnf_spettacoli.scnf_client  
DESC,scnf_spettacoli.scnf_client DESC,scnf_spettacoli.scnf_code DESC;
 
  WITH RESULT :
      Unique  (cost=128133.80..128135.94 rows=43 width=54) (actual 
time=181431.85..181441.64 rows=401 loops=1)
  ->  Sort  (cost=128133.80..128133.80 rows=430 width=54) (actual 
time=181431.83..181434.25 rows=2233 loops=1)
        ->  Merge Join  (cost=0.00..128115.01 rows=430 width=54) (actual 
time=1.78..181390.04 rows=2233 loops=1)
              ->  Index Scan using spet_system_idx on spettacoli  
(cost=0.00..135.12 rows=430 width=26) (actual time=0.87..44.16 rows=401 
loops=1)
              ->  Index Scan using scnf_sys_tea_perf_idx on scnf_spettacoli  
(cost=0.00..1497.34 rows=23910 width=28) (actual time=0.65..118910.47 
rows=9587510 loops=1)

 The index scan using scnf_sys_tea_perf give back 9587510 rows bat the table 
scnf_spettacoli is only 23910 rows.
 
 
2) If I change  (scnf_spettacoli.scnf_teatro = spettacoli.teatro OR 
scnf_spettacoli = '*') 
     in      scnf_spettacoli.scnf_teatro::text = spettacoli.teatro::text
 
 
  explain analyze
SELECT DISTINCT ON (spettacoli.teatro,spettacoli.code) 
                                 spettacoli.teatro,spettacoli.code,   
                                 scnf_spettacoli.scnf_gruppo 
,scnf_spettacoli.scnf_client,
                                scnf_spettacoli.scnf_client 
,scnf_spettacoli.scnf_code 
   
  FROM spettacoli LEFT JOIN scnf_spettacoli ON 
     (scnf_spettacoli.scnf_code in (spettacoli.code,'*') AND
      scnf_spettacoli.scnf_teatro::text = spettacoli.teatro::text AND
      spettacoli.system = scnf_spettacoli.scnf_system  AND 
      scnf_spettacoli.scnf_gruppo in ('leoni','*') AND
      scnf_spettacoli.scnf_client in ('paolo','*') ) 
  WHERE
    spettacoli.system    = 0 AND
    spettacoli.flag      != 0 AND
    spettacoli.orarioinizio < '200407141219'
  ORDER BY spettacoli.teatro,spettacoli.code, scnf_spettacoli.scnf_gruppo DESC 
,scnf_spettacoli.scnf_client DESC,scnf_spettacoli.scnf_client 
DESC,scnf_spettacoli.scnf_code DESC;
 
 
WITH RESULT :
 
Unique  (cost=5402.31..5404.45 rows=43 width=67) (actual time=62.45..64.43 
rows=401 loops=1)
  ->  Sort  (cost=5402.31..5402.31 rows=430 width=67) (actual 
time=62.43..62.85 rows=411 loops=1)
        ->  Nested Loop  (cost=0.00..5383.52 rows=430 width=67) (actual 
time=1.75..56.30 rows=411 loops=1)
              ->  Seq Scan on spettacoli  (cost=0.00..59.86 rows=430 width=26) 
(actual time=0.26..28.77 rows=401 loops=1)
              ->  Index Scan using scnf_sys_tea_perf_idx on scnf_spettacoli  
(cost=0.00..12.31 rows=3 width=41) (actual time=0.03..0.05 rows=1 loops=401)
Total runtime: 67.22 msec

 
The result not be able to be the same one, but the difference between the two 
query is exaggerated.
 
 
 
The table of the database are :
 
CREATE TABLE spettacoli (
 system              INT2,
 titolo              VARCHAR(50),
 tipo                VARCHAR(4),
 date                VARCHAR(9),
 time                CHAR(6),
 teatro              CHAR(09),
 orarioinizio        VARCHAR(13),
 flag                INT2,
 code                VARCHAR(12),
 serial              INT4,
 bitFlag             INT4,
 avaiability         INT2 DEFAULT 0,
 last_modified       TIMESTAMP DEFAULT null,
 insert_time         TIMESTAMP,
 perf_num            INT2,
 CONSTRAINT spe_sys_tea_perf 
  PRIMARY KEY(system,teatro, code)
); 
 
 
CREATE TABLE scnf_spettacoli (
        scnf_system              INT2  NOT NULL,
        scnf_teatro              CHAR(09)  NOT NULL,
        scnf_code                VARCHAR(12)  NOT NULL,
        scnf_gruppo              VARCHAR(21),
        scnf_client              VARCHAR(21),
        scnf_operator            VARCHAR(21) DEFAULT '*',
        scnf_vendita             INT2 DEFAULT 1,
        scnf_rinnovo             INT2 DEFAULT 1, 
        scnf_sell_untill         INT4 DEFAULT 0,
 CONSTRAINT scnf_spe_tk 
  PRIMARY KEY(scnf_system,scnf_teatro, scnf_code,
                            scnf_gruppo,scnf_client,scnf_operator)
);

CREATE INDEX code_idx                        ON spettacoli(code);
CREATE INDEX spet_system_idx                 ON spettacoli(system);    
CREATE INDEX spet_teatro_idx                 ON spettacoli(teatro); 
 
 CREATE INDEX scnf_sys_tea_perf_idx ON 
scnf_spettacoli(scnf_system,scnf_teatro,scnf_code);
 CREATE INDEX scnf_code_idx ON scnf_spettacoli(scnf_code);
 
the database is VACUUM ANALYZE;
 
 
Can anyone help me please thank!


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[prev in list] [next in list] [prev in thread] [next in thread]