Re: Immutable function WAY slower than Stable function?

Ken Tanzer wrote:
> On Mon, Aug 6, 2018 at 4:11 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Ken Tanzer <ken(dot)tanzer(at)gmail(dot)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:
>
> 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=199814
> Planning time: 0.156 ms
> Execution 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=60174
> Planning time: 0.354 ms
> Execution time: 1315.746 ms
> (5 rows)

That seems to say that the _stable function is running much faster.
Buth functions don't get inlined.

I'd dig into the functions and find out how long the queries in
them take. auto_explain is a good helper for that.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com