Re: Immutable function WAY slower than Stable function?

Поиск

Список

Период

Сортировка

От	Ken Tanzer
Тема	Re: Immutable function WAY slower than Stable function?
Дата	7 августа 2018 г. 05:30:22
Msg-id	CAD3a31XELjttH7y8TVxva56jqBcnUA3koPy02xySzWaVDBif4A@mail.gmail.com обсуждение исходный текст
Ответ на	Re: Immutable function WAY slower than Stable function? (Tom Lane <tgl@sss.pgh.pa.us>)
Ответы	Re: Immutable function WAY slower than Stable function? (Laurenz Albe <laurenz.albe@cybertec.at>)
Список	pgsql-general

Дерево обсуждения

On Mon, Aug 6, 2018 at 4:11 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Ken Tanzer <ken.tanzer@gmail.com> writes:
> Hi. I was recently troubleshooting a function, and realized it had
> incorrectly been declared as Immutable, when it should have been declared
> Stable. When I changed it to Stable, the query I was running ran
> dramatically faster. Digging into this a little more, this is what I found:

EXPLAIN VERBOSE might shed a bit more light. I suspect you're falling
foul of the rather arcane rules about whether a SQL function can be
inlined. (Although it seems like only the wrapper function would be
inline-able anyway, so I'm not sure why the penalty is that high.)

regards, tom lane

Thanks Tom. This was as talkative as I could get it. I do see the difference on the Output line, but I don't know how to interperet it.

spc=> EXPLAIN (ANALYZE,BUFFERS,VERBOSE) SELECT client_id,si_imm(client_id) FROM tbl_residence_own;                                                         QUERY PLAN                                                           
-------------------------------------------------------------------------------------------------------------------------------Seq Scan on public.tbl_residence_own  (cost=0.00..1990.02 rows=6977 width=8) (actual time=3.771..22665.604 rows=6983 loops=1)  Output: client_id, si_imm(client_id)  Buffers: shared hit=199814Planning time: 0.156 msExecution time: 22677.333 ms
(5 rows)

spc=> EXPLAIN (ANALYZE,BUFFERS,VERBOSE) SELECT client_id,si_stable(client_id) FROM tbl_residence_own;                                                         QUERY PLAN                                                          
------------------------------------------------------------------------------------------------------------------------------Seq Scan on public.tbl_residence_own  (cost=0.00..3734.27 rows=6977 width=8) (actual time=3.100..1302.888 rows=6983 loops=1)  Output: client_id, staff_inspector_stable(client_id, target_date())  Buffers: shared hit=60174Planning time: 0.354 msExecution time: 1315.746 ms
(5 rows)

Cheers,

Ken