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.tanzer@gmail.com 
> <mailto:ken.tanzer@gmail.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'
ANDagency_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_idAND target_date() BETWEEN residence_date AND COALESCE(residence_date_end,target_date()) AND NOT
ro.is_deletedLIMIT 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.klaver@aklaver.com