Re: Optimizer regression

On 10/13/12 2:45 PM, Tom Lane wrote:
> Jim Nasby <jim(at)nasby(dot)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_prod(at)postgres10(dot)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_prod(at)postgres10(dot)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_prod(at)postgres10(dot)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 jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net