Optimizing views in pgSQL - Revision history

Alnaucler at 19:28, 20 February 2020

2020-02-20T19:28:02Z

← Older revision		Revision as of 21:28, 20 February 2020
Line 256:		Line 256:
	{{NavBox Hub}}		{{NavBox Hub}}
	[[Category:NoPublic]]		[[Category:NoPublic]]
	~~[[Category:VIE]]~~

Evinek at 13:23, 6 August 2014

2014-08-06T13:23:32Z

← Older revision		Revision as of 15:23, 6 August 2014
Line 31:		Line 31:
	== Examples ==__NOEDITSECTION__		== Examples ==__NOEDITSECTION__
	=== View my_view_hazardreductions in Afghanistan ===__NOEDITSECTION__		=== View my_view_hazardreductions in Afghanistan ===__NOEDITSECTION__
	Version with a very poor performance (approx. 860 seconds / 86'~~242~~ rows):		Version with a very poor performance (approx. 860 seconds / approx. 86'000 rows):
	<pre>		<pre>
	SELECT type.enumvalue AS Type,		SELECT type.enumvalue AS Type,
Line 62:		Line 62:
	AND customdefinedfield.name = 'CDF-Clearance Type';		AND customdefinedfield.name = 'CDF-Clearance Type';
	</pre>		</pre>
	Optimised version (approx. 160 seconds / 86'~~242~~ rows):		Optimised version (approx. 160 seconds / approx. 86'000 rows):
	<pre>		<pre>
	WITH hazreduc_imsmaenum AS (		WITH hazreduc_imsmaenum AS (
Line 115:		Line 115:

	=== View annualreport_view_bac in Afghanistan ===__NOEDITSECTION__		=== View annualreport_view_bac in Afghanistan ===__NOEDITSECTION__
	Version with a very poor performance (more than 15 minutes):		Version with a very poor performance (more than 15 minutes / approx. 86'000 rows):
	<pre>		<pre>
	SELECT progress.hazreduc_guid AS "ProgressGUID",		SELECT progress.hazreduc_guid AS "ProgressGUID",
Line 151:		Line 151:
	my_view_gazetteer.Village;		my_view_gazetteer.Village;
	</pre>		</pre>
	Optimised version (approx. 15 seconds):		Optimised version (approx. 15 seconds / approx. 86'000 rows):
	<pre>		<pre>
	WITH progress as (		WITH progress as (

Evinek at 13:21, 6 August 2014

2014-08-06T13:21:42Z

← Older revision		Revision as of 15:21, 6 August 2014
Line 31:		Line 31:
	== Examples ==__NOEDITSECTION__		== Examples ==__NOEDITSECTION__
	=== View my_view_hazardreductions in Afghanistan ===__NOEDITSECTION__		=== View my_view_hazardreductions in Afghanistan ===__NOEDITSECTION__
	Version with a very poor performance (approx. 860 seconds):		Version with a very poor performance (approx. 860 seconds / 86'242 rows):
	<pre>		<pre>
	SELECT type.enumvalue AS Type,		SELECT type.enumvalue AS Type,
Line 62:		Line 62:
	AND customdefinedfield.name = 'CDF-Clearance Type';		AND customdefinedfield.name = 'CDF-Clearance Type';
	</pre>		</pre>
	Optimised version (approx. 160 seconds):		Optimised version (approx. 160 seconds / 86'242 rows):
	<pre>		<pre>
	WITH hazreduc_imsmaenum AS (		WITH hazreduc_imsmaenum AS (

Evinek at 13:20, 6 August 2014

2014-08-06T13:20:01Z

← Older revision		Revision as of 15:20, 6 August 2014
Line 238:		Line 238:
	</pre>		</pre>
	There will be no visible output of that command, but the database statistics will be refreshed. Freshly updated database statistics can improve the performance of specific queries (especially after big changes - such as after heavy imports - when the automatic statistics gathering process has not yet run).		There will be no visible output of that command, but the database statistics will be refreshed. Freshly updated database statistics can improve the performance of specific queries (especially after big changes - such as after heavy imports - when the automatic statistics gathering process has not yet run).
	* Rebuilding the indexes on the ~~affected~~ table(s) by running the following command (specifying the index or table name):		* Rebuilding the indexes on the table(s) used in the problematic query by running the following command (specifying the index or table name):
	<pre>		<pre>
	REINDEX { INDEX \| TABLE \| DATABASE \| SYSTEM } name		REINDEX { INDEX \| TABLE \| DATABASE \| SYSTEM } name

Evinek at 13:18, 6 August 2014

2014-08-06T13:18:38Z

← Older revision		Revision as of 15:18, 6 August 2014
Line 233:		Line 233:
	== Summary of recommendations ==__NOEDITSECTION__		== Summary of recommendations ==__NOEDITSECTION__
	'''Things to check before looking into rewriting the query itself:'''		'''Things to check before looking into rewriting the query itself:'''
	* ~~Gather~~ database statistics by running the following command on the database:		* Refresh the database statistics used by the query optimizer (to determine the best or a good query execution plan) by running the following command on the database:
	<pre>		<pre>
	ANALYZE;		ANALYZE;
	</pre>		</pre>
	Freshly updated database statistics can improve the performance of specific queries (especially after big changes - such as after heavy imports - when the automatic statistics gathering process has not yet run).		There will be no visible output of that command, but the database statistics will be refreshed. Freshly updated database statistics can improve the performance of specific queries (especially after big changes - such as after heavy imports - when the automatic statistics gathering process has not yet run).
	* Rebuilding the indexes on the affected table(s) by running the following command (specifying the index or table name):		* Rebuilding the indexes on the affected table(s) by running the following command (specifying the index or table name):
	<pre>		<pre>

Evinek at 13:12, 6 August 2014

2014-08-06T13:12:48Z

← Older revision		Revision as of 15:12, 6 August 2014
Line 204:		Line 204:
	=== Problematic WHERE clause example ===__NOEDITSECTION__		=== Problematic WHERE clause example ===__NOEDITSECTION__
	In Afghanistan, there is an Access-based reporting system that is building on views created in the IMSMA database. When upgrading to V6, some of the Access queries had a very poor performance. In particular, the following query was affected (it initially took about 30 minutes!):		In Afghanistan, there is an Access-based reporting system that is building on views created in the IMSMA database. When upgrading to V6, some of the Access queries had a very poor performance. In particular, the following query was affected (it initially took about 30 minutes!):
			{{Note\|These two queries are in MS Access syntax, not pgSQL syntax. In order to get a working pgSQL syntax, the [] (square brackets) need to be replaced by "" (double quotes) and the "" (double quotes) in the WHERE clause by single quotes}}
	<pre>		<pre>
	SELECT districtfree_view_hazardclosed.Region,		SELECT districtfree_view_hazardclosed.Region,
Line 225:		Line 226:
	GROUP BY districtfree_view_hazardclosed.Region, districtfree_view_hazardclosed.Province, districtfree_view_hazardclosed.District, districtfree_view_hazardclosed.district_guid;		GROUP BY districtfree_view_hazardclosed.Region, districtfree_view_hazardclosed.Province, districtfree_view_hazardclosed.District, districtfree_view_hazardclosed.district_guid;
	</pre>		</pre>

	{{Note\|These are Access queries, not pgSQL queries. There is a slightly different syntax. In order to get a working pgSQL query, the [] (square brackets) need to be replaced by "" (double quotes) and the "" (double quotes) in the WHERE clause by '' (single quotes)}}

	In this case, it seemed safe to make the according changes, because it is not very likely that another status will be added to the system (and thus the two where clauses are and will stay equivalent). However, this cannot be generalised to other cases.		In this case, it seemed safe to make the according changes, because it is not very likely that another status will be added to the system (and thus the two where clauses are and will stay equivalent). However, this cannot be generalised to other cases.

Evinek at 13:09, 6 August 2014

2014-08-06T13:09:34Z

← Older revision		Revision as of 15:09, 6 August 2014
Line 178:		Line 178:
	cast(progress.enddate as date),		cast(progress.enddate as date),
	progress.areasize,		progress.areasize,
	annualreport_view_clearancetype."Clearance Type")		annualreport_view_clearancetype."Clearance Type"
			)
	SELECT		SELECT
	"ProgressGUID",		"ProgressGUID",

Evinek at 13:04, 6 August 2014

2014-08-06T13:04:56Z

← Older revision		Revision as of 15:04, 6 August 2014
Line 33:		Line 33:
	Version with a very poor performance (approx. 860 seconds):		Version with a very poor performance (approx. 860 seconds):
	<pre>		<pre>
	~~CREATE OR REPLACE VIEW my_view_hazardreductions AS~~
	SELECT type.enumvalue AS Type,		SELECT type.enumvalue AS Type,
	imsmaenum.enumvalue AS Status,		imsmaenum.enumvalue AS Status,

Evinek at 13:04, 6 August 2014

2014-08-06T13:04:28Z

← Older revision		Revision as of 15:04, 6 August 2014
Line 56:		Line 56:
	LEFT JOIN my_view_gazetteer ON (location.gazetteer_guid = my_view_gazetteer.gazetteer_guid)		LEFT JOIN my_view_gazetteer ON (location.gazetteer_guid = my_view_gazetteer.gazetteer_guid)
	LEFT JOIN hazreduc_has_imsmaenum ON (hazreduc.hazreduc_guid = hazreduc_has_imsmaenum.hazreduc_guid)		LEFT JOIN hazreduc_has_imsmaenum ON (hazreduc.hazreduc_guid = hazreduc_has_imsmaenum.hazreduc_guid)
	LEFT JOIN imsmaenum method_used ON n (hazreduc_has_imsmaenum.imsmaenum_guid = method_used.imsmaenum_guid)		LEFT JOIN imsmaenum method_used ON (hazreduc_has_imsmaenum.imsmaenum_guid = method_used.imsmaenum_guid)
	JOIN customdefinedfield ON (customdefinedfield.cdf_id = areatype.cdf_id)		JOIN customdefinedfield ON (customdefinedfield.cdf_id = areatype.cdf_id)
	LEFT JOIN my_view_organization ON (hazreduc.org_guid = my_view_organization.org_guid)		LEFT JOIN my_view_organization ON (hazreduc.org_guid = my_view_organization.org_guid)

Evinek at 13:00, 6 August 2014

2014-08-06T13:00:12Z

← Older revision		Revision as of 15:00, 6 August 2014
Line 74:		Line 74:
	hazreduc.org_guid,		hazreduc.org_guid,
	hazreduc.location_guid		hazreduc.location_guid
			FROM hazreduc
	LEFT JOIN imsmaenum Type on (type.imsmaenum_guid = hazreduc.hazreductypeenum_guid)		LEFT JOIN imsmaenum Type on (type.imsmaenum_guid = hazreduc.hazreductypeenum_guid)
	LEFT JOIN imsmaenum on (imsmaenum.imsmaenum_guid = hazreduc.status_guid)		LEFT JOIN imsmaenum on (imsmaenum.imsmaenum_guid = hazreduc.status_guid)