On Tue, Aug 7, 2018 at 12:05 AM Ken Tanzer <ken.tan...@gmail.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.tan...@agency-software.org (253) 245-3801 Subscribe to the mailing list <agency-general-requ...@lists.sourceforge.net?body=subscribe> to learn more about AGENCY or follow the discussion.
explain.staff_inspector_query_corrected.sql
Description: Binary data