Re: Immutable function WAY slower than Stable function?

On Mon, Aug 6, 2018 at 7:42 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Ken Tanzer <ken(dot)tanzer(at)gmail(dot)com> writes:
> > Hi Adrian. Happy to provide this info. Though on a side note, I don't
> > understand why it should matter, if functions are black box optimization
> > fences.
>
> They aren't, at least not when they are SQL-language functions that
> meet the conditions for inlining.

Yeah, I kinda realized after I sent this that I wasn't really making much
sense. :) I appreciate your explanation though.

The reason that EXPLAIN VERBOSE
> is helpful here is that you can see whether the function got inlined
> or not: do you see a call to the function, or a representation of
> its body?
>
> I wasn't able to get anything more VERBOSE than what I sent before. I
re-copied it below. (If there's another command or option I should be
using, please advise.)

There is the difference in the two output lines, which I guess suggests
that the stable one got inlined and the immutable one didn't?

* Output: client_id, si_imm(client_id)
*

* Output: client_id, staff_inspector_stable(client_id, target_date())
*

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)

Cheers,
Ken

--
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ <http://agency-software.org/>*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client>*
ken(dot)tanzer(at)agency-software(dot)org
(253) 245-3801

Subscribe to the mailing list
<agency-general-request(at)lists(dot)sourceforge(dot)net?body=subscribe> to
learn more about AGENCY or
follow the discussion.