Re: Immutable function WAY slower than Stable function?

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

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

Hmm, whatever it is looks to be tied to si_* being IMMUTABLE. Just a
thought but have you tried(NOTE: DEFAULT value):

CREATE OR REPLACE FUNCTION staff_inspector_stable( client INTEGER, asof
DATE DEFAULT target_date()) RETURNS INTEGER AS $$
SELECT
CASE WHEN
(SELECT program_type_code FROM reg_spc WHERE asof BETWEEN reg_spc_date
AND COALESCE(reg_spc_date_end,asof) AND client_id=client LIMIT 1)
ILIKE 'SSP%' THEN
--- SSP answer
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
---SPC answer
(SELECT staff_inspector FROM tbl_residence_own ro LEFT JOIN
l_housing_project USING (housing_project_code) WHERE client_id=client
AND asof BETWEEN residence_date AND COALESCE(residence_date_end,asof)
AND NOT ro.is_deleted LIMIT 1)

END
--LIMIT 1
$$ LANGUAGE SQL STABLE;

>
> Cheers,
> Ken
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com