On 10/13/12 2:45 PM, Tom Lane wrote:
Jim Nasby <j...@nasby.net> writes:
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 know if it's been fixed or not. I'm
hoping that someone *cough*Tom*cough* would quickly recognize whether this push
into subquery issue 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 care about this in 8.4, but if
this regression still exists it would be nice if it were fixed.
It's hard to be sure with such an incomplete example, but I think 8.4 is
flattening the EXISTS to a semijoin and then getting trapped by join
order constraints into doing something less than optimal for this
particular use-case. It was this type of example that motivated the
"parameterized path" stuff I've been working on for the past couple
of years.
In short, 9.2 should produce at least as good a plan as 8.3 for this
example, but 8.4 through 9.1 might not.
FWIW, it's definitely an issue of not being able to push down past the GROUP BY:
cnuapp_p...@postgres10.obr=# explain WITH default_stats AS (select
customer_id, status_cd, count(*), max(id) from loans.payday_defaulted group by
customer_id, status_cd) SELECT * FROM default_stats where customer_id=10287151;
QUERY PLAN
------------------------------------------------------------------------------------------------------------
CTE Scan on default_stats (cost=2980046.56..3004313.73 rows=5393 width=162)
Filter: (customer_id = 10287151)
CTE default_stats
-> HashAggregate (cost=2963868.44..2980046.56 rows=1078541 width=17)
-> Hash Join (cost=2028045.22..2902409.22 rows=6145922 width=17)
Hash Cond: (loans.id = ls.loan_id)
-> Seq Scan on loans (cost=0.00..688437.25 rows=10785404
width=17)
Filter: ((loan_type_cd)::text = ANY
('{payday,cso}'::text[]))
-> Hash (cost=2015864.33..2015864.33 rows=974471 width=4)
-> HashAggregate (cost=2006119.62..2015864.33
rows=974471 width=4)
-> Seq Scan on loan_statuses ls
(cost=0.00..1984723.02 rows=8558638 width=4)
Filter: ((status_cd)::text =
'in_default'::text)
(12 rows)
cnuapp_p...@postgres10.obr=# explain analyze select customer_id, status_cd,
count(*), max(id) from loans.payday_defaulted where customer_id=10287151 group
by customer_id, status_cd;
QUERY
PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=202.16..202.19 rows=2 width=17) (actual time=0.422..0.422
rows=0 loops=1)
-> Nested Loop Semi Join (cost=0.00..202.07 rows=9 width=17) (actual
time=0.422..0.422 rows=0 loops=1)
-> Index Scan using loans_m12 on loans (cost=0.00..41.48 rows=16
width=17) (actual time=0.028..0.121 rows=31 loops=1)
Index Cond: (customer_id = 10287151)
Filter: ((loan_type_cd)::text = ANY ('{payday,cso}'::text[]))
-> Index Scan using loan_statuses__loan_id__status on loan_statuses
ls (cost=0.00..10.17 rows=1 width=4) (actual time=0.009..0.009 rows=0 loops=31)
Index Cond: ((ls.loan_id = loans.id) AND ((ls.status_cd)::text =
'in_default'::text))
Total runtime: 0.510 ms
(8 rows)
cnuapp_p...@postgres10.obr=#
I hope that we'll have 9.2 stood up before the year is out, so we'll check this
then and see if it's fixed.
--
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
