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