help me - Mailing list pgsql-general

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!