<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<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.95 rows=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=1 width=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=1 width=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.027 rows=1
loops=1)<br>
-> 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)<br>
Index Cond:
(person1_id = 287294)<br>
-> 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)<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=1 width=16)
(actual time=92.408..92.408 rows=0 loops=1)<br>
Index Cond:
(dd.debtid = add.debtid)<br>
Filter: (dd.active
AND (NOT dd.paid) AND dd.has_proform AND (NOT dd.storned))<br>
-> Index Scan using
domeini_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 Scan on measures m (cost=0.00..1.14
rows=14 width=8) (never executed)<br>
-> Index Scan using acc_debts_debtid_idx on
acc_debts adc (cost=0.00..5.16 rows=1 width=8) (never executed)<br>
Index Cond: (dd.debtid = adc.debtid)<br>
Filter: adc.credit<br>
-> Index Scan using acc_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 Grittner wrote:
<blockquote cite="mid:4C80E73402000025000351AF(at)gw(dot)wicourts(dot)gov"
type="cite">
<pre wrap="">Kaloyan Iliev Iliev <a class="moz-txt-link-rfc2396E" href="mailto:kaloyan(at)digsys(dot)bg"><kaloyan(at)digsys(dot)bg></a> wrote:
</pre>
<blockquote type="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 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
</pre>
</blockquote>
</body>
</html>