Re: Question about LEFT JOIN and query plan - Mailing list pgsql-performance

<tt>Hi,<br /> The plan improves.  So can you explain why?<br /> Thanks in advance.<br /><br /> Kaloyan<br />
                                                                                       QUERY
PLAN                                                                                        <br />
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br
/> Nested Loop Left Join  (cost=0.00..82.88 rows=1 width=68) (actual time=92.455..92.455 rows=0 loops=1)<br />   
-> Nested Loop Left Join  (cost=0.00..77.73 rows=1 width=64) (actual time=92.453..92.453 rows=0 loops=1)<br />
        ->  Nested Loop Left Join  (cost=0.00..69.44 rows=1 width=64) (actual time=92.451..92.451 rows=0 loops=1)<br
/>               ->  Nested Loop  (cost=0.00..64.26 rows=1 width=60) (actual time=92.449..92.449 rows=0 loops=1)<br
/>                     Join Filter: (dd.measure_id = m.measure_id)<br />                      ->  Nested Loop 
(cost=0.00..62.95rows=1 width=60) (actual time=92.447..92.447 rows=0 loops=1)<br />                            Join
Filter:(dd.serviceid = s.serviceid)<br />                            ->  Nested Loop Left Join  (cost=0.00..59.96
rows=1width=37) (actual time=92.444..92.444 rows=0 loops=1)<br />                                  Join Filter:
(dom.domain_type_id= dt.id)<br />                                  ->  Nested Loop Left Join  (cost=0.00..58.13
rows=1width=36) (actual time=92.443..92.443 rows=0 loops=1)<br />                                        ->  Nested
Loop (cost=0.00..52.88 rows=1 width=28) (actual time=92.440..92.440 rows=0 loops=1)<br />
                                            ->  Nested Loop  (cost=0.00..27.50 rows=5 width=16) (actual
time=0.021..0.027rows=1 loops=1)<br />                                                    ->  Index Scan using
proforms_person1_id_idxon proforms p  (cost=0.00..10.67 rows=2 width=4) (actual time=0.008..0.009 rows=1 loops=1)<br />
                                                        Index Cond: (person1_id = 287294)<br />
                                                  ->  Index Scan using acc_debts_pno_idx on acc_debts add 
(cost=0.00..8.38rows=3 width=16) (actual time=0.007..0.009 rows=1 loops=1)<br />
                                                        Index Cond: (add.pno = p.pno)<br />
                                                        Filter: (NOT add.credit)<br />
                                            ->  Index Scan using debts_desc_pkey on debts_desc dd  (cost=0.00..5.06
rows=1width=16) (actual time=92.408..92.408 rows=0 loops=1)<br />                                                   
IndexCond: (dd.debtid = add.debtid)<br />                                                    Filter: (dd.active AND
(NOTdd.paid) AND dd.has_proform AND (NOT dd.storned))<br />                                        ->  Index Scan
usingdomeini_pkey on domeini dom  (cost=0.00..5.24 rows=1 width=16) (never executed)<br />
                                            Index Cond: (dd.domain_id = dom.id)<br />                                 
-> Seq Scan on domain_type dt  (cost=0.00..1.37 rows=37 width=9) (never executed)<br />                           
-> Seq Scan on services s  (cost=0.00..2.44 rows=44 width=31) (never executed)<br />                      ->  Seq
Scanon measures m  (cost=0.00..1.14 rows=14 width=8) (never executed)<br />                ->  Index Scan using
acc_debts_debtid_idxon acc_debts adc  (cost=0.00..5.16 rows=1 width=8) (never executed)<br />                     
IndexCond: (dd.debtid = adc.debtid)<br />                      Filter: adc.credit<br />          ->  Index Scan
usingacc_clients_transact_no_uidx on acc_clients ac  (cost=0.00..8.28 rows=1 width=8) (never executed)<br />
              Index Cond: (ac.transact_no = adc.transact_no)<br />                Filter: (NOT ac.credit)<br />   
-> Index Scan using invoices_ino_uidx on invoices i  (cost=0.00..5.13 rows=1 width=8) (never executed)<br />
        Index Cond: (ac.ino = i.ino)<br />  Total runtime: 92.612 ms<br /> (34 rows)<br /></tt><br /><br /> Kevin
Grittnerwrote: <blockquote cite="mid:4C80E73402000025000351AF@gw.wicourts.gov" type="cite"><pre wrap="">Kaloyan Iliev
Iliev<a class="moz-txt-link-rfc2396E" href="mailto:kaloyan@digsys.bg"><kaloyan@digsys.bg></a> wrote:
</pre><blockquotetype="cite"><pre wrap="">I thing they should be access only if there are rows from the
 
where. Why the left join executes first?   </pre></blockquote><pre wrap=""> 
Out of curiosity, what happens if you consistently us JOIN clauses,
rather than mixing that with commas?:
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 JOIN proforms P ON
(ADD.pno= P.pno) JOIN acc_debts ADD ON (DD.debtid = ADD.debtid) JOIN services S ON (DD.serviceid = S.serviceid) JOIN
measuresM ON (DD.measure_id = M.measure_id) 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_clientsAC   ON (AC.transact_no = ADC.transact_no AND NOT AC.credit) LEFT JOIN invoices I ON (AC.ino = I.ino AND
I.istatus= 0) WHERE DD.active   AND NOT DD.paid   AND DD.has_proform   AND NOT DD.storned   AND NOT ADD.credit   AND
P.person1_id= 287294
 
;
-Kevin
 </pre></blockquote>