Optimizer regression - Mailing list pgsql-hackers

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
knowif it's been fixed or not. I'm hoping that someone *cough*Tom*cough* would quickly recognize whether this push into
subqueryissue 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
careabout 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
BYp.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
Scand  (cost=0.00..438.00 rows=2 width=162) (actual time=4883.286..4883.286 rows=1 loops=1)  ->  GroupAggregate
(cost=0.00..421.91rows=2 width=17) (actual time=4883.181..4883.181 rows=1 loops=1)        ->  Nested Loop Left Join
(cost=0.00..421.75rows=13 width=17) (actual time=314.426..4883.082 rows=31 loops=1)              ->  Index Scan using
loans_m13on loans  (cost=0.00..36.72 rows=13 width=17) (actual time=52.209..561.240 rows=31 loops=1)
IndexCond: ((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.61rows=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_u1on 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=31loops=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
Scand  (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=6145607width=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=10783881width=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_statusesls  (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)          ->
IndexScan 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@nasby.net
512.569.9461 (cell)                         http://jim.nasby.net