Re: Optimizer regression - Mailing list pgsql-hackers

On 10/13/12 3:15 PM, Jim Nasby wrote:
> FWIW, it's definitely an issue of not being able to push down past the GROUP BY:

I take that back... GROUP BY doesn't matter. It's an issue of having the EXISTS in the inner query. I realize the
exampleshave gotten a bit silly, but this seems to break it down to the simplest case of what's happening.
 

FAST:

explain analyze

                                                        SELECT p.customer_id, p.status_cd, EXISTS( SELECT * FROM
loan_statusesls WHERE ls.loan_id = p.id AND ls.status_cd = 'in_default' )
                                                                                                      FROM loans p

                                                                    LEFT JOIN

                                                                                                 ( SELECT * FROM loans
pWHERE loan_type_cd IN ( 'payday', 'cso' )

       ) d USING( id )

                                                                                                     WHERE
p.customer_id= 10287151

                           AND p.status_cd = 'paid_off'

                                                        AND p.loan_type_cd IN ( 'payday', 'cso' )



                                                ;
QUERY PLAN
 

-----------------------------------------------------------------------------------------------------------------------------------------------------------
NestedLoop Left Join  (cost=0.00..234.87 rows=13 width=17) (actual time=0.085..0.861 rows=31 loops=1)   ->  Index Scan
usingloans_m13 on loans p  (cost=0.00..36.01 rows=13 width=17) (actual time=0.045..0.137 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 p  (cost=0.00..5.12 rows=1 width=4) (actual
time=0.011..0.011rows=1 loops=31)         Index Cond: (p.id = p.id)         Filter: ((p.loan_type_cd)::text = ANY
('{payday,cso}'::text[]))  SubPlan 1     ->  Index Scan using loan_statuses__loan_id__status on loan_statuses ls
(cost=0.00..10.17rows=1 width=0) (actual time=0.011..0.011 rows=0 loops=31)           Index Cond: ((loan_id = $0) AND
((status_cd)::text= 'in_default'::text)) Total runtime: 0.950 ms
 
(11 rows)


SLOW:


cnuapp_prod@postgres10.obr=# explain

                                                                                     SELECT p.customer_id, p.status_cd


   FROM loans p

                                   LEFT JOIN

                                                                     ( SELECT * FROM loans p WHERE loan_type_cd IN (
'payday','cso' ) AND EXISTS( SELECT * FROM loan_statuses ls WHERE ls.loan_id = p.id AND ls.status_cd = 'in_default' )
                                                                                                     ) d USING( id )


    WHERE p.customer_id = 10287151

                                      AND p.status_cd = 'paid_of
 
f'

    AND p.loan_type_cd IN ( 'payday', 'cso' )


                                                                                                                     ;
                                             QUERY PLAN
 
---------------------------------------------------------------------------------------------------------- Hash Left
Join (cost=3003251.16..3027297.36 rows=13 width=13)   Hash Cond: (p.id = p.id)   ->  Index Scan using loans_m13 on
loansp  (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=2902419.07..2902419.07rows=6145927 width=4)         ->  Hash Join  (cost=2028047.07..2902419.07 rows=6145927
width=4)              Hash Cond: (p.id = ls.loan_id)               ->  Seq Scan on loans p  (cost=0.00..688444.00
rows=10785509width=4)                     Filter: ((loan_type_cd)::text = ANY ('{payday,cso}'::text[]))
-> Hash  (cost=2015866.17..2015866.17 rows=974472 width=4)                     ->  HashAggregate
(cost=2006121.45..2015866.17rows=974472 width=4)                           ->  Seq Scan on loan_statuses ls
(cost=0.00..1984724.84rows=8558646 width=4)                                 Filter: ((status_cd)::text =
'in_default'::text)
(14 rows)

cnuapp_prod@postgres10.obr=#

-- 
Jim C. Nasby, Database Architect                   jim@nasby.net
512.569.9461 (cell)                         http://jim.nasby.net