Re: Immutable function WAY slower than Stable function?

On Mon, Aug 6, 2018 at 11:05 PM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
wrote:

> 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:
> >
> > 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.
>

Agreed!

> Buth functions don't get inlined.
>
> OK, I clearly don't understand the output lines. What does it mean then
that the stable output line shows the underlying (two-argument) function,
while the immutable one shows the convenience function?

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

I'd dig into the functions and find out how long the queries in
> them take. auto_explain is a good helper for that.
>
>
I'm definitely not understanding why or how auto-explain would help here.
(Also, don't overlook the fact that both si_stable and si_imm have the
exact same definition (except for stable v. immutable), and are calling the
same function (staff_inspector_stable)). I can load up the auto_explain
stuff if it really will help, but in the meantime what I did was pull the
query out of staff_inspector_stable, and inserted into the "SELECT
client_id,staff_inspector( ... ) FROM tbl_residence_own" query, replacing
the "client" (parameter 1) with the client_id from the tbl_residence_own,
and replacing "asof" (parameter 2) with target_date(), which seems like it
should closely mimic the original query.

I ended up with the query below, and have attached the explain output to
this email. The actual timing is:

Planning time: 2.737 ms
Execution time: 821.034 ms

Which is somewhat better than the function versions, though it seems
unsurprisingly and not dramatically so.

EXPLAIN (VERBOSE,ANALYZE,BUFFERS) SELECT client_id,
CASE WHEN
(SELECT program_type_code FROM reg_spc WHERE target_date() BETWEEN
reg_spc_date AND COALESCE(reg_spc_date_end,target_date()) AND
client_id=tro.client_id LIMIT 1)
ILIKE 'SSP%' THEN
COALESCE((SELECT staff_id FROM staff_employment_current WHERE
staff_position_code='COORD_PROP' AND agency_project_code='SSP' LIMIT
1),(SELECT staff_id FROM staff_employment_current WHERE
staff_position_code='MGRPROJ' AND agency_project_code='SSP' LIMIT 1))
ELSE
(SELECT staff_inspector FROM tbl_residence_own ro LEFT JOIN
l_housing_project USING (housing_project_code) WHERE client_id=5538
AND target_date() BETWEEN residence_date AND
COALESCE(residence_date_end,target_date()) AND NOT ro.is_deleted LIMIT
1)
END
FROM tbl_residence_own tro;

Let me know if that is not helpful. Or if you need something from
auto-explain, please help me with some more specifics. Thanks!

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.