Re: [PERFORM] Question about LEFT JOIN and query plan

Kaloyan Iliev Iliev Tue, 07 Sep 2010 00:19:39 -0700

Hi, The plan improves. So can you explain why? Thanks in advance. Kaloyan QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Nested Loop Left Join (cost=0.00..82.88 rows=1 width=68) (actual time=92.455..92.455 rows=0 loops=1) -> Nested Loop Left Join (cost=0.00..77.73 rows=1 width=64) (actual time=92.453..92.453 rows=0 loops=1) -> Nested Loop Left Join (cost=0.00..69.44 rows=1 width=64) (actual time=92.451..92.451 rows=0 loops=1) -> Nested Loop (cost=0.00..64.26 rows=1 width=60) (actual time=92.449..92.449 rows=0 loops=1) Join Filter: (dd.measure_id = m.measure_id) -> Nested Loop (cost=0.00..62.95 rows=1 width=60) (actual time=92.447..92.447 rows=0 loops=1) Join Filter: (dd.serviceid = s.serviceid) -> Nested Loop Left Join (cost=0.00..59.96 rows=1 width=37) (actual time=92.444..92.444 rows=0 loops=1) Join Filter: (dom.domain_type_id = dt.id) -> Nested Loop Left Join (cost=0.00..58.13 rows=1 width=36) (actual time=92.443..92.443 rows=0 loops=1) -> Nested Loop (cost=0.00..52.88 rows=1 width=28) (actual time=92.440..92.440 rows=0 loops=1) -> Nested Loop (cost=0.00..27.50 rows=5 width=16) (actual time=0.021..0.027 rows=1 loops=1) -> Index Scan using proforms_person1_id_idx on proforms p (cost=0.00..10.67 rows=2 width=4) (actual time=0.008..0.009 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.009 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..5.06 rows=1 width=16) (actual time=92.408..92.408 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 domeini_pkey on domeini dom (cost=0.00..5.24 rows=1 width=16) (never executed) Index Cond: (dd.domain_id = dom.id) -> Seq Scan on domain_type dt (cost=0.00..1.37 rows=37 width=9) (never executed) -> 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) -> Index Scan using acc_debts_debtid_idx on acc_debts adc (cost=0.00..5.16 rows=1 width=8) (never executed) Index Cond: (dd.debtid = adc.debtid) Filter: adc.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 = adc.transact_no) Filter: (NOT ac.credit) -> Index Scan using invoices_ino_uidx on invoices i (cost=0.00..5.13 rows=1 width=8) (never executed) Index Cond: (ac.ino = i.ino) Total runtime: 92.612 ms (34 rows)

Kevin Grittner wrote:

Kaloyan Iliev Iliev <[email protected]> wrote:

I thing they should be access only if there are rows from the
where. Why the left join executes first?

 
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 measures M 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_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)
  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

Re: [PERFORM] Question about LEFT JOIN and query plan

Reply via email to