Re: performance regression in 9.2 CTE with SRF function - Mailing list pgsql-hackers

>
> no, there is strange estimation
>
>          ->  Seq Scan on public.x2  (cost=0.00..345560.00 rows=500
> width=4) (actual time=17.914..9330.645 rows=133 loops=1)
>                Output: x2.a
>                Filter: (NOT (SubPlan 2))
>                Rows Removed by Filter: 867
>                SubPlan 2
>                  ->  CTE Scan on pl pl_1  (cost=0.00..468.59
> rows=89000 width=4) (actual time=0.023..8.379 rows=566 loops=1000)
>                        Output: foo(pl_1.a)
>
> CTE Scan expect rows=89000
>
> I don't know how is possible to take too high number
>

respective why estimation is unstrable

first (1MB work_mem)
                ->  CTE Scan on pl pl_1  (cost=0.00..468.59
rows=89000 width=4) (actual time=0.023..8.379 rows=566 loops=1000)                      Output: foo(pl_1.a)


second (3MB work_mem)
 ->  Hash  (cost=1.78..1.78 rows=89 width=4) (actual
time=9.650..9.650 rows=89 loops=1)        Output: pl.a        Buckets: 1024  Batches: 1  Memory Usage: 3kB        ->
CTEScan on pl  (cost=0.00..1.78 rows=89 width=4) (actual
 
time=8.468..9.346 rows=89 loops=1)              Output: pl.a

I expect so estimation not depends on work_mem

Best regards

Pavel

> Regards
>
> Pavel
>
>>
>> If you add "ROWS 10" or so to the declaration of the function, you
>> get a better row estimate and it goes back to the hashed subplan.
>>
>>                         regards, tom lane