Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id D8CDA632A47 for ; Fri, 3 Sep 2010 13:45:07 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 95453-06 for ; Fri, 3 Sep 2010 16:44:59 +0000 (UTC) X-Greylist: delayed 00:28:17.655001 by SQLgrey-1.7.6 Received: from relay2.digsys.bg (varna.digsys.bg [192.92.129.9]) by mail.postgresql.org (Postfix) with ESMTP id 097306329DF for ; Fri, 3 Sep 2010 13:44:58 -0300 (ADT) Received: from luke.digsys.bg (kaloyan@luke.digsys.bg [193.68.6.22]) by relay2.digsys.bg (8.14.4/8.14.4) with ESMTP id o83GGbDu002094 for ; Fri, 3 Sep 2010 19:16:37 +0300 (EEST) (envelope-from kaloyan@digsys.bg) Message-ID: <4C811F68.9060601@digsys.bg> Date: Fri, 03 Sep 2010 19:16:40 +0300 From: Kaloyan Iliev Iliev Reply-To: kaloyan@digsys.bg Organization: Register.BG Ltd. User-Agent: Thunderbird 2.0.0.24 (X11/20100614) MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Question about LEFT JOIN and query plan Content-Type: text/plain; charset=windows-1251; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201009/10 X-Sequence-Number: 40213 Hello, I have I query which behave strangely (according to me). According to the first plan PG makes absolutely unnecessary seq scan on tables "invoices" and "domeini" and etc. I thing they should be access only if there are rows from the where. Why the left join executes first? Then I rewrite the query and move left joins to sub queries and the result was great speed up. But I thing it is more correctly to write the query with left joins. At least the sub queries have similar parts which are now accessed twice. So I will appreciate any suggestions how it is correct to write this query and why the left join plan is so wrong. SELECT version(); version ---------------------------------------------------------------------------------------------------------- PostgreSQL 8.4.4 on amd64-portbld-freebsd8.1, compiled by GCC cc (GCC) 4.2.1 20070719 [FreeBSD], 64-bit (1 row) Best regards, Kaloyan Iliev ===============================ORIGINAL QUERY============================== explain analyze SELECT DD.debtid, ADD.amount as saldo, DOM.fqdn ||DT.descr as domain_fqdn, S.descr_bg as service_descr_bg, ADD.pno, ADD.amount, M.name_bg as measure_name_bg, AC.ino, I.idate FROM debts_desc DD LEFT JOIN domeini DOM ON (DD.domain_id = DOM.id) LEFT JOIN domain_type DT ON (DOM.domain_type_id = DT.id) LEFT JOIN acc_debts ADC ON (DD.debtid = ADC.debtid AND ADC.credit) LEFT JOIN acc_clients AC ON (AC.transact_no = ADC.transact_no AND NOT AC.credit) LEFT JOIN invoices I ON (AC.ino = I.ino AND I.istatus = 0), acc_debts ADD, services S, measures M, proforms P WHERE DD.debtid = ADD.debtid AND DD.measure_id = M.measure_id AND DD.active AND NOT DD.paid AND DD.has_proform AND NOT DD.storned AND ADD.pno = P.pno AND NOT ADD.credit AND P.person1_id = 287294 AND DD.serviceid = S.serviceid; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Nested Loop (cost=37503.47..47243.77 rows=1 width=110) (actual time=1522.796..1522.796 rows=0 loops=1) Join Filter: (dd.measure_id = m.measure_id) -> Nested Loop (cost=37503.47..47242.45 rows=1 width=106) (actual time=1522.794..1522.794 rows=0 loops=1) Join Filter: (dd.serviceid = s.serviceid) -> Hash Join (cost=37503.47..47239.46 rows=1 width=79) (actual time=1522.791..1522.791 rows=0 loops=1) Hash Cond: (dd.debtid = add.debtid) -> Hash Left Join (cost=37475.95..47122.76 rows=23782 width=67) (actual time=1370.668..1521.629 rows=1037 loops=1) Hash Cond: (dom.domain_type_id = dt.id) -> Hash Left Join (cost=37474.12..46793.92 rows=23782 width=66) (actual time=1370.563..1519.302 rows=1037 loops=1) Hash Cond: (dd.domain_id = dom.id) -> Hash Left Join (cost=23487.71..30402.02 rows=23782 width=54) (actual time=556.587..636.320 rows=1037 loops=1) Hash Cond: (ac.ino = i.ino) -> Hash Left Join (cost=8410.66..14259.11 rows=23782 width=50) (actual time=318.180..387.026 rows=1037 loops=1) Hash Cond: (adc.transact_no = ac.transact_no) -> Hash Left Join (cost=4973.98..9903.69 rows=23782 width=50) (actual time=175.979..234.068 rows=1037 loops=1) Hash Cond: (dd.debtid = adc.debtid) -> Seq Scan on debts_desc dd (cost=0.00..2866.52 rows=23782 width=46) (actual time=0.481..45.085 rows=1037 loops=1) Filter: (active AND (NOT paid) AND has_proform AND (NOT storned)) -> Hash (cost=3942.08..3942.08 rows=62872 width=8) (actual time=175.410..175.410 rows=63157 loops=1) -> Seq Scan on acc_debts adc (cost=0.00..3942.08 rows=62872 width=8) (actual time=0.097..102.172 rows=63157 loops=1) Filter: credit -> Hash (cost=2536.53..2536.53 rows=54812 width=8) (actual time=142.169..142.169 rows=54559 loops=1) -> Seq Scan on acc_clients ac (cost=0.00..2536.53 rows=54812 width=8) (actual time=0.019..78.736 rows=54559 loops=1) Filter: (NOT credit) -> Hash (cost=14181.02..14181.02 rows=54562 width=8) (actual time=238.380..238.380 rows=54559 loops=1) -> Seq Scan on invoices i (cost=0.00..14181.02 rows=54562 width=8) (actual time=0.029..170.761 rows=54559 loops=1) Filter: (istatus = 0) -> Hash (cost=8669.96..8669.96 rows=305796 width=16) (actual time=813.940..813.940 rows=305796 loops=1) -> Seq Scan on domeini dom (cost=0.00..8669.96 rows=305796 width=16) (actual time=0.015..419.684 rows=305796 loops=1) -> Hash (cost=1.37..1.37 rows=37 width=9) (actual time=0.087..0.087 rows=37 loops=1) -> Seq Scan on domain_type dt (cost=0.00..1.37 rows=37 width=9) (actual time=0.003..0.040 rows=37 loops=1) -> Hash (cost=27.45..27.45 rows=5 width=16) (actual time=0.078..0.078 rows=1 loops=1) -> Nested Loop (cost=0.00..27.45 rows=5 width=16) (actual time=0.067..0.073 rows=1 loops=1) -> Index Scan using proforms_person1_id_idx on proforms p (cost=0.00..10.62 rows=2 width=4) (actual time=0.045..0.046 rows=1 loops=1) Index Cond: (person1_id = 287294) -> Index Scan using acc_debts_pno_idx on acc_debts add (cost=0.00..8.38 rows=3 width=16) (actual time=0.017..0.019 rows=1 loops=1) Index Cond: (add.pno = p.pno) Filter: (NOT add.credit) -> Seq Scan on services s (cost=0.00..2.44 rows=44 width=31) (never executed) -> Seq Scan on measures m (cost=0.00..1.14 rows=14 width=8) (never executed) Total runtime: 1523.525 ms (41 rows) ==================================================AFTER REWRITE============================================ explain analyze SELECT DD.debtid, ADD.amount as saldo, (SELECT DOM.fqdn ||DT.descr FROM domeini DOM, domain_type DT WHERE DOM.domain_type_id = DT.id AND DD.domain_id = DOM.id) as domain_fqdn, S.descr_bg as service_descr_bg, ADD.pno, ADD.amount, M.name_bg as measure_name_bg, (SELECT AC.ino FROM acc_debts ACD, acc_clients AC WHERE ACD.debtid = ADD.debtid AND ACD.credit AND AC.transact_no = ACD.transact_no AND NOT AC.credit) as ino, (SELECT I.idate FROM acc_debts ACD, acc_clients AC, invoices I WHERE ACD.debtid = ADD.debtid AND ACD.credit AND AC.transact_no = ACD.transact_no AND NOT AC.credit AND AC.ino = I.ino AND I.istatus = 0) as idate FROM debts_desc DD, acc_debts ADD, services S, measures M, proforms P WHERE DD.debtid = ADD.debtid AND DD.measure_id = M.measure_id AND DD.active AND NOT DD.paid AND DD.has_proform AND NOT DD.storned AND ADD.pno = P.pno AND NOT ADD.credit AND P.person1_id = 287294 AND DD.serviceid = S.serviceid; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------- Nested Loop (cost=0.00..77.90 rows=1 width=93) (actual time=0.047..0.047 rows=0 loops=1) -> Nested Loop (cost=0.00..32.96 rows=1 width=66) (actual time=0.045..0.045 rows=0 loops=1) -> Nested Loop (cost=0.00..32.68 rows=1 width=62) (actual time=0.043..0.043 rows=0 loops=1) -> Nested Loop (cost=0.00..27.45 rows=5 width=16) (actual time=0.026..0.031 rows=1 loops=1) -> Index Scan using proforms_person1_id_idx on proforms p (cost=0.00..10.62 rows=2 width=4) (actual time=0.013..0.014 rows=1 loops=1) Index Cond: (person1_id = 287294) -> Index Scan using acc_debts_pno_idx on acc_debts add (cost=0.00..8.38 rows=3 width=16) (actual time=0.007..0.008 rows=1 loops=1) Index Cond: (add.pno = p.pno) Filter: (NOT add.credit) -> Index Scan using debts_desc_pkey on debts_desc dd (cost=0.00..1.03 rows=1 width=46) (actual time=0.007..0.007 rows=0 loops=1) Index Cond: (dd.debtid = add.debtid) Filter: (dd.active AND (NOT dd.paid) AND dd.has_proform AND (NOT dd.storned)) -> Index Scan using measures_pkey on measures m (cost=0.00..0.27 rows=1 width=8) (never executed) Index Cond: (m.measure_id = dd.measure_id) -> Index Scan using services_pkey on services s (cost=0.00..0.27 rows=1 width=31) (never executed) Index Cond: (s.serviceid = dd.serviceid) SubPlan 1 -> Hash Join (cost=8.31..9.84 rows=1 width=13) (never executed) Hash Cond: (dt.id = dom.domain_type_id) -> Seq Scan on domain_type dt (cost=0.00..1.37 rows=37 width=9) (never executed) -> Hash (cost=8.30..8.30 rows=1 width=12) (never executed) -> Index Scan using domeini_pkey on domeini dom (cost=0.00..8.30 rows=1 width=12) (never executed) Index Cond: ($0 = id) SubPlan 2 -> Nested Loop (cost=0.00..16.63 rows=1 width=4) (never executed) -> Index Scan using acc_debts_debtid_idx on acc_debts acd (cost=0.00..8.33 rows=1 width=4) (never executed) Index Cond: (debtid = $1) Filter: credit -> Index Scan using acc_clients_transact_no_uidx on acc_clients ac (cost=0.00..8.28 rows=1 width=8) (never executed) Index Cond: (ac.transact_no = acd.transact_no) Filter: (NOT ac.credit) SubPlan 3 -> Nested Loop (cost=0.00..18.19 rows=1 width=4) (never executed) -> Nested Loop (cost=0.00..16.63 rows=1 width=4) (never executed) -> Index Scan using acc_debts_debtid_idx on acc_debts acd (cost=0.00..8.33 rows=1 width=4) (never executed) Index Cond: (debtid = $1) Filter: credit -> Index Scan using acc_clients_transact_no_uidx on acc_clients ac (cost=0.00..8.28 rows=1 width=8) (never executed) Index Cond: (ac.transact_no = acd.transact_no) Filter: (NOT ac.credit) -> Index Scan using invoices_ino_uidx on invoices i (cost=0.00..1.55 rows=1 width=8) (never executed) Index Cond: (i.ino = ac.ino) Total runtime: 0.202 ms (43 rows)