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_p...@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 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_p...@postgres10.obr=#
--
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
