Re: Optimizer regression
От
Jim Nasby
Тема
Re: Optimizer regression
Дата
Msg-id
5079D020.8020208@nasby.net
Ответ на
Re: Optimizer regression (Jim Nasby)
Список
Дерево обсуждения
Optimizer regression Jim Nasby <jim@nasby.net>
Re: Optimizer regression Tom Lane <tgl@sss.pgh.pa.us>
Re: Optimizer regression Jim Nasby <jim@nasby.net>
Re: Optimizer regression Jim Nasby <jim@nasby.net>
Re: Optimizer regression Tom Lane <tgl@sss.pgh.pa.us>
Re: Optimizer regression Jim Nasby <jim@nasby.net>
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@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_prod@postgres10.obr=#
--
Jim C. Nasby, Database Architect jim@nasby.net
512.569.9461 (cell) http://jim.nasby.net
В списке pgsql-hackers по дате отправления
