On 08/07/2018 12:38 AM, Ken Tanzer wrote:
On Tue, Aug 7, 2018 at 12:05 AM Ken Tanzer <ken.tan...@gmail.com
<mailto:ken.tan...@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' 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.kla...@aklaver.com
