Optimizer regression

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 jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net