help me
От | Paolo Tavalazzi |
---|---|
Тема | help me |
Дата | |
Msg-id | 200407211347.02677.ptavalazzi@charta.it обсуждение исходный текст |
Список | 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!
В списке pgsql-general по дате отправления:
Предыдущее
От: Peter EisentrautДата:
Сообщение: Re: tsearch2, ispell, utf-8 and german special characters
Следующее
От: "Markus Wollny"Дата:
Сообщение: Re: tsearch2, ispell, utf-8 and german special characters