Re: Optimizer regression

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 examples have 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_statuses ls WHERE ls.loan_id = p.id AND ls.status_cd = 'in_default' ) FROM loans p LEFT JOIN

( SELECT * FROM loans p WHERE 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
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=0.00..234.87 rows=13 width=17) (actual time=0.085..0.861 rows=31 loops=1)
-> Index Scan using loans_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.011 rows=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.17 rows=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(at)postgres10(dot)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 loans p (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.07 rows=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=10785509 width=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.17 rows=974472 width=4)
-> Seq Scan on loan_statuses ls (cost=0.00..1984724.84 rows=8558646 width=4)
Filter: ((status_cd)::text = 'in_default'::text)
(14 rows)

cnuapp_prod(at)postgres10(dot)obr=#

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