Re: Optimizer regression - Mailing list pgsql-hackers

On 10/13/12 2:45 PM, Tom Lane wrote:
> Jim Nasby <jim@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'tknow if it's been fixed or not. I'm hoping that someone *cough*Tom*cough* would quickly recognize whether this
pushinto subquery issue has been fixed or not, so I haven't included full details or a test case. I have a work-around
soI 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@postgres10.obr=# explain  WITH default_stats AS (select customer_id, status_cd, count(*), max(id)  from
loans.payday_defaultedgroup by customer_id, status_cd) SELECT * FROM default_stats  where customer_id=10287151;
                                       QUERY PLAN 
------------------------------------------------------------------------------------------------------------ CTE Scan
ondefault_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.22rows=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
onloan_statuses ls  (cost=0.00..1984723.02 rows=8558638 width=4)                                   Filter:
((status_cd)::text= 'in_default'::text) 
(12 rows)

cnuapp_prod@postgres10.obr=# explain analyze  select customer_id, status_cd, count(*), max(id)  from
loans.payday_defaultedwhere 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_m12on 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[]))         ->
IndexScan using loan_statuses__loan_id__status on loan_statuses ls  (cost=0.00..10.17 rows=1 width=4) (actual
time=0.009..0.009rows=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@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                   jim@nasby.net
512.569.9461 (cell)                         http://jim.nasby.net