From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: performance regression in 9.2 CTE with SRF function |
Date: | 2013-02-11 05:25:14 |
Message-ID: | CAFj8pRDXU9vand0XGyuFgXJ-2FiVUimp_r9ggPYdeK8V7bXxig@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
-> CTE Scan 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
From | Date | Subject | |
---|---|---|---|
Next Message | Jeevan Chalke | 2013-02-11 06:44:26 | Re: unlogged tables vs. GIST |
Previous Message | Pavel Stehule | 2013-02-11 04:56:04 | Re: performance regression in 9.2 CTE with SRF function |