Hi!
After upgrade (dump/restore/analyze) query (below) after some time is killed by kernel.
Postgres creates a lot of tmp files:
temporary file: path "base/pgsql_tmp/pgsql_tmp8949.2046", size 24576
temporary file: path "base/pgsql_tmp/pgsql_tmp8949.2045", size 24576
temporary file: path "base/pgsql_tmp/pgsql_tmp8949.2044", size 32768
temporary file: path "base/pgsql_tmp/pgsql_tmp8949.2043", size 32768
temporary file: path "base/pgsql_tmp/pgsql_tmp8949.2042", size 24576
I have no idea whats wrong. Looks like planer bad decision.
SELECT vwc.* , c.jednzawid as bureau_id ,
c.nazwa as borrower_name ,
c.nrumowy as agreement_number ,
c.kwotakred as credit_amount_gross ,
c.nrumchar as id_ge ,
c.zazndoanuldata as marked_to_cancel_date ,
c.bureau_category_id ,
l.typid as product_type_id ,
cs.doc_receive_tmstp ,
cs.doc_send_path_id ,
cs.first_scan_complete_tmstp ,
cs.verification_doc_not_complete_id ,
cs.verification_phone_not_complete_id ,
caged.requested_doc_send_path_id ,
end_of_validity(c.*,l.*) AS end_of_validity ,
bc.creationdate as last_contact_tmstp ,
last_va.action_status_id as verification_status_id ,
c.verification_assistant_id ,
caged.client_verification_path_id
FROM verification_waiting_credit vwc,
kredyty c LEFT JOIN kredaged caged ON (caged.kredytid = c.id) LEFT
JOIN bureau_contact bc ON (bc.credit_id = c.id AND NOT EXISTS
( SELECT 1 FROM bureau_contact bc1
WHERE bc1.credit_id = bc.credit_id and bc1.id > bc.id)
) LEFT JOIN verification_action last_va ON (last_va.credit_id = c.id
AND NOT EXISTS
(SELECT 1 FROM verification_action va_t
WHERE va_t.id > last_va.id
AND va_t.credit_id = last_va.credit_id)
) , kredytstatus cs , linie l
WHERE true
AND vwc.user_id = 12949
AND vwc.credit_id = c.id
AND vwc.credit_id = cs.kredytid
AND c.linia = l.id
ORDER BY vwc.id
Query plan below (9.2).
"Nested Loop (cost=73132.54..5320352.90 rows=1 width=4681)"
" -> Nested Loop (cost=73132.54..5320345.72 rows=1 width=4416)"
" -> Nested Loop (cost=73132.54..5320334.36 rows=1 width=4392)"
" Join Filter: (vwc.credit_id = c.id)"
" -> Index Scan using verification_waiting_credit_pkey on verification_waiting_credit vwc
(cost=0.00..12.97rows=1 width=156)"
" Filter: (user_id = 12949)"
" -> Hash Left Join (cost=73132.54..5279312.99 rows=3280672 width=4236)"
" Hash Cond: (c.id = bc.credit_id)"
" -> Hash Left Join (cost=73019.54..5119260.82 rows=3280672 width=4228)"
" Hash Cond: (c.id = last_va.credit_id)"
" -> Hash Left Join (cost=24351.16..4650135.04 rows=3280672 width=4224)"
" Hash Cond: (c.id = caged.kredytid)"
" -> Seq Scan on kredyty c (cost=0.00..1202754.72 rows=3280672 width=4216)"
" -> Hash (cost=16741.96..16741.96 rows=437696 width=12)"
" -> Seq Scan on kredaged caged (cost=0.00..16741.96 rows=437696 width=12)"
" -> Hash (cost=45953.74..45953.74 rows=217172 width=8)"
" -> Hash Anti Join (cost=19583.56..45953.74 rows=217172 width=8)"
" Hash Cond: (last_va.credit_id = va_t.credit_id)"
" Join Filter: (va_t.id > last_va.id)"
" -> Seq Scan on verification_action last_va (cost=0.00..15511.58 rows=325758
width=12)"
" -> Hash (cost=15511.58..15511.58 rows=325758 width=8)"
" -> Seq Scan on verification_action va_t (cost=0.00..15511.58 rows=325758
width=8)"
" -> Hash (cost=104.99..104.99 rows=641 width=12)"
" -> Hash Anti Join (cost=49.65..104.99 rows=641 width=12)"
" Hash Cond: (bc.credit_id = bc1.credit_id)"
" Join Filter: (bc1.id > bc.id)"
" -> Seq Scan on bureau_contact bc (cost=0.00..37.62 rows=962 width=16)"
" -> Hash (cost=37.62..37.62 rows=962 width=8)"
" -> Seq Scan on bureau_contact bc1 (cost=0.00..37.62 rows=962 width=8)"
" -> Index Scan using kredytstatus_pkey on kredytstatus cs (cost=0.00..11.35 rows=1 width=32)"
" Index Cond: (kredytid = c.id)"
" -> Index Scan using linie_pkey on linie l (cost=0.00..6.92 rows=1 width=273)"
" Index Cond: (id = c.linia)"
To compare query plan on postgresql 9.0
"Nested Loop (cost=0.00..28892.99 rows=1 width=357)"
" -> Nested Loop Left Join (cost=0.00..28884.11 rows=1 width=333)"
" Join Filter: (bc.credit_id = c.id)"
" -> Nested Loop (cost=0.00..135.81 rows=1 width=325)"
" -> Nested Loop Left Join (cost=0.00..133.03 rows=1 width=293)"
" -> Nested Loop Left Join (cost=0.00..96.01 rows=1 width=289)"
" -> Nested Loop (cost=0.00..92.56 rows=1 width=281)"
" -> Index Scan using verification_waiting_credit_pkey on verification_waiting_credit
vwc (cost=0.00..83.92 rows=1 width=156)"
" Filter: (user_id = 12949)"
" -> Index Scan using kredyty_desc_pkey on kredyty c (cost=0.00..8.63 rows=1
width=125)"
" Index Cond: (c.id = vwc.credit_id)"
" -> Index Scan using kredaged_pkey on kredaged caged (cost=0.00..3.44 rows=1 width=12)"
" Index Cond: (caged.kredytid = c.id)"
" -> Index Scan using verification_action_credit_id_idx on verification_action last_va
(cost=0.00..37.00rows=2 width=8)"
" Index Cond: (last_va.credit_id = c.id)"
" Filter: (NOT (SubPlan 2))"
" SubPlan 2"
" -> Index Scan using verification_action_credit_id_idx on verification_action va_t
(cost=0.00..8.38rows=1 width=0)"
" Index Cond: (credit_id = $3)"
" Filter: (id > $2)"
" -> Index Scan using linie_pkey on linie l (cost=0.00..2.77 rows=1 width=40)"
" Index Cond: (l.id = c.linia)"
" -> Seq Scan on bureau_contact bc (cost=0.00..28742.08 rows=498 width=12)"
" Filter: (NOT (SubPlan 1))"
" SubPlan 1"
" -> Index Scan using bureau_contact_pkey on bureau_contact bc1 (cost=0.00..28.79 rows=1 width=0)"
" Index Cond: (id > $1)"
" Filter: (credit_id = $0)"
" -> Index Scan using kredytstatus_pkey on kredytstatus cs (cost=0.00..8.62 rows=1 width=32)"
" Index Cond: (cs.kredytid = vwc.credit_id)"
Query run time 52ms
Best regards
--
Andrzej Zawadzki