Just upgraded to 8.4 (I know, I know…) and ran across this. Unfortunately I
have no way to test this on 9.x, so I don't know if it's been fixed or not. I'm
hoping that someone *cough*Tom*cough* would quickly recognize whether this push
into subquery issue has been fixed or not, so I haven't included full details
or a test case. I have a work-around so I don't care about this in 8.4, but if
this regression still exists it would be nice if it were fixed.
CREATE VIEW loans.payday AS SELECT * FROM loans WHERE loan_type_cd IN (
'payday', 'other' );
CREATE VIEW loans.payday_defaulted AS SELECT * FROM loans.payday p WHERE
EXISTS( SELECT * FROM loan_statuses ls WHERE ls.loan_id = p.id AND ls.status_cd
= 'in_default' );
This query is fast:
SELECT defaulted_then_paid_loans
, ( SELECT count(*)
FROM loans.payday
WHERE ROW( customer_id, status_cd ) = ROW( d.customer_id, d.status_cd )
AND id > coalesce( max_defaulted_loan_id, 0 )
) AS number_of_loans_since_last_default
FROM ( SELECT p.customer_id, p.status_cd, count( d.id ) AS
defaulted_then_paid_loans
, max( d.id ) AS max_defaulted_loan_id
FROM loans.payday p
LEFT JOIN loans.payday_defaulted d USING( id )
WHERE d.customer_id = ?
GROUP BY p.customer_id, p.status_cd
) d
WHERE status_cd = 'paid_off';
This query is not (but was fine on 8.3):
SELECT defaulted_then_paid_loans
, ( SELECT count(*)
FROM loans.payday
WHERE ROW( customer_id, status_cd ) = ROW( d.customer_id, d.status_cd )
AND id > coalesce( max_defaulted_loan_id, 0 )
) AS number_of_loans_since_last_default
FROM ( SELECT p.customer_id, p.status_cd, count( d.id ) AS
defaulted_then_paid_loans
, max( d.id ) AS max_defaulted_loan_id
FROM loans.payday p
LEFT JOIN loans.payday_defaulted d USING( id )
GROUP BY p.customer_id, p.status_cd
) d
WHERE status_cd = 'paid_off'
AND customer_id = ?
;
Plan from the "bad" query on 8.3:
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------
Subquery Scan d (cost=0.00..438.00 rows=2 width=162) (actual
time=4883.286..4883.286 rows=1 loops=1)
-> GroupAggregate (cost=0.00..421.91 rows=2 width=17) (actual
time=4883.181..4883.181 rows=1 loops=1)
-> Nested Loop Left Join (cost=0.00..421.75 rows=13 width=17)
(actual time=314.426..4883.082 rows=31 loops=1)
-> Index Scan using loans_m13 on loans (cost=0.00..36.72
rows=13 width=17) (actual time=52.209..561.240 rows=31 loops=1)
Index Cond: ((customer_id = 10287151) AND
((status_cd)::text = 'paid_off'::text))
Filter: ((loan_type_cd)::text = ANY
('{payday,cso}'::text[]))
-> Index Scan using loans_pkey on loans (cost=0.00..29.61
rows=1 width=4) (actual time=139.410..139.410 rows=0 loops=31)
Index Cond: (cnu.loans.id = cnu.loans.id)
Filter: (((cnu.loans.loan_type_cd)::text = ANY
('{payday,cso}'::text[])) AND (subplan))
SubPlan
-> Index Scan using loan_status_u1 on loan_statuses ls
(cost=0.00..23.43 rows=1 width=88) (actual time=109.521..109.521 rows=0
loops=31)
Index Cond: (loan_id = $3)
Filter: ((status_cd)::text = 'in_default'::text)
SubPlan
-> Aggregate (cost=8.03..8.04 rows=1 width=0) (actual time=0.100..0.100
rows=1 loops=1)
-> Index Scan using loans_m13 on loans (cost=0.00..8.02 rows=1
width=0) (actual time=0.041..0.084 rows=31 loops=1)
Index Cond: ((customer_id = $0) AND ((status_cd)::text =
($1)::text))
Filter: (((loan_type_cd)::text = ANY ('{payday,cso}'::text[]))
AND (id > COALESCE($2, 0)))
Total runtime: 4883.439 ms
(19 rows)
And from 8.4…
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Subquery Scan d (cost=3003014.53..3027074.69 rows=2 width=162)
-> GroupAggregate (cost=3003014.53..3027059.89 rows=2 width=17)
-> Hash Left Join (cost=3003014.53..3027059.73 rows=13 width=17)
Hash Cond: (cnu.loans.id = cnu.loans.id)
-> Index Scan using loans_m13 on loans (cost=0.00..36.01
rows=13 width=17)
Index Cond: ((customer_id = 10287151) AND
((status_cd)::text = 'paid_off'::text))
Filter: ((loan_type_cd)::text = ANY
('{payday,cso}'::text[]))
-> Hash (cost=2902187.44..2902187.44 rows=6145607 width=4)
-> Hash Join (cost=2027941.10..2902187.44 rows=6145607
width=4)
Hash Cond: (cnu.loans.id = ls.loan_id)
-> Seq Scan on loans (cost=0.00..688340.03
rows=10783881 width=4)
Filter: ((loan_type_cd)::text = ANY
('{payday,cso}'::text[]))
-> Hash (cost=2015760.83..2015760.83 rows=974422
width=4)
-> HashAggregate
(cost=2006016.61..2015760.83 rows=974422 width=4)
-> Seq Scan on loan_statuses ls
(cost=0.00..1984621.11 rows=8558199 width=4)
Filter: ((status_cd)::text =
'in_default'::text)
SubPlan 1
-> Aggregate (cost=7.38..7.39 rows=1 width=0)
-> Index Scan using loans_m13 on loans (cost=0.00..7.37 rows=1
width=0)
Index Cond: ((customer_id = $0) AND ((status_cd)::text =
($1)::text))
Filter: (((loan_type_cd)::text = ANY ('{payday,cso}'::text[]))
AND (id > COALESCE($2, 0)))
(21 rows)
--
Jim C. Nasby, Database Architect j...@nasby.net
512.569.9461 (cell) http://jim.nasby.net
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
