Re: Immutable function WAY slower than Stable function?

On Tue, Aug 7, 2018 at 12:05 AM Ken Tanzer <ken(dot)tanzer(at)gmail(dot)com> wrote:

> 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;
>
>
>
Whoops, scratch that previous explain and query. I accidentally left in a
hard-coded client_id from earlier testing. The correct query is:

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=tro.client_id 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;

The corrected explain output is attached, and the actual timing was:

Planning time: 2.741 ms
Execution time: 2538.277 ms

Sorry for the confusion! It's been a long day, and filled with many 20+
second waits for queries to finish. :)

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.