help me

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!