Mercurial > gemma
view schema/isrs.sql @ 4161:64cd18281c76
Improve performance of row level security policies
Using constraint_column_usage instead of key_column_usage makes
the query twice as fast. I did not explore why. Let's just take it.
Using 'EXISTS(... WHERE ... = value) is often more efficient than
value IN(...) since it allows the inner query to be executed only
up to the point where it turns out to return more than nothing with
filtering directly in place.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Fri, 02 Aug 2019 17:14:13 +0200 |
parents | d9ac1c56bfe0 |
children | d3fb2f37380b |
line wrap: on
line source
-- This is Free Software under GNU Affero General Public License v >= 3.0 -- without warranty, see README.md and license for details. -- SPDX-License-Identifier: AGPL-3.0-or-later -- License-Filename: LICENSES/AGPL-3.0.txt -- Copyright (C) 2018, 2019 by via donau -- – Österreichische Wasserstraßen-Gesellschaft mbH -- Software engineering by Intevation GmbH -- Author(s): -- * Tom Gottfried <tom@intevation.de> -- -- Types and functions for ISRS location codes -- BEGIN; CREATE TABLE countries ( country_code char(2) PRIMARY KEY -- ISO 3166 ALPHA-2 code ); CREATE OR REPLACE FUNCTION is_country(code char) RETURNS boolean AS $$ SELECT EXISTS(SELECT 1 FROM countries WHERE country_code = code) $$ LANGUAGE sql STABLE PARALLEL SAFE; -- -- Types -- -- Composite type: UN/LOCODE, fairway section, object reference, hectometre. -- See RIS-Index Encoding Guide CREATE TYPE isrs_base AS ( country_code char(2), -- ISO 3166 country code locode char(3), -- without the country code: -- http://www.unece.org/cefact/locode/welcome.html fairway_section char(5), orc char(5), -- Object Reference Code hectometre int ); CREATE DOMAIN isrs AS isrs_base CHECK (is_country((VALUE).country_code)) CHECK ((VALUE).hectometre BETWEEN 0 AND 99999); CREATE FUNCTION isrs_cmp(a isrs, b isrs) RETURNS int AS $$ /* TODO: handle non-matching combinations of country_codes and fairway_sections. Otherwise, this will give unexpected results if both hectometre values do not refer to the same river. */ SELECT (a).hectometre - (b).hectometre $$ LANGUAGE sql IMMUTABLE PARALLEL SAFE; CREATE FUNCTION isrslt(a isrs, b isrs) RETURNS boolean AS $$ SELECT isrs_cmp(a, b) < 0 $$ LANGUAGE sql IMMUTABLE PARALLEL SAFE; CREATE FUNCTION isrsle(a isrs, b isrs) RETURNS boolean AS $$ SELECT isrs_cmp(a, b) <= 0 $$ LANGUAGE sql IMMUTABLE PARALLEL SAFE; CREATE FUNCTION isrseq(a isrs, b isrs) RETURNS boolean AS $$ SELECT isrs_cmp(a, b) = 0 $$ LANGUAGE sql IMMUTABLE PARALLEL SAFE; CREATE FUNCTION isrsge(a isrs, b isrs) RETURNS boolean AS $$ SELECT isrs_cmp(a, b) >= 0 $$ LANGUAGE sql IMMUTABLE PARALLEL SAFE; CREATE FUNCTION isrsgt(a isrs, b isrs) RETURNS boolean AS $$ SELECT isrs_cmp(a, b) > 0 $$ LANGUAGE sql IMMUTABLE PARALLEL SAFE; CREATE OPERATOR <~ ( leftarg = isrs, rightarg = isrs, function = isrslt ); CREATE OPERATOR <~= ( leftarg = isrs, rightarg = isrs, function = isrsle ); CREATE OPERATOR ~= ( leftarg = isrs, rightarg = isrs, function = isrseq, commutator = ~= ); CREATE OPERATOR >~= ( leftarg = isrs, rightarg = isrs, function = isrsge, commutator = <~=, negator = <~ ); CREATE OPERATOR >~ ( leftarg = isrs, rightarg = isrs, function = isrsgt, commutator = <~, negator = <~= ); CREATE OPERATOR CLASS isrs_ops FOR TYPE isrs USING btree AS OPERATOR 1 <~, OPERATOR 2 <~=, OPERATOR 3 ~=, OPERATOR 4 >~=, OPERATOR 5 >~, FUNCTION 1 isrs_cmp; CREATE FUNCTION isrs_diff(a isrs, b isrs) RETURNS double precision AS $$ SELECT CAST(isrs_cmp(a, b) AS double precision) $$ LANGUAGE sql IMMUTABLE PARALLEL SAFE; CREATE TYPE isrsrange AS RANGE ( subtype = isrs, subtype_opclass = isrs_ops, subtype_diff = isrs_diff ); -- -- Functions -- CREATE OR REPLACE FUNCTION isrs_fromText( isrs_text varchar ) RETURNS isrs AS $$ DECLARE isrs_len CONSTANT int = 20; BEGIN isrs_text = upper(isrs_text); IF char_length(isrs_text) <> isrs_len THEN RAISE 'ISRS location code must be % characters long', isrs_len USING ERRCODE = 'invalid_parameter_value', DETAIL = 'Failing value: ' || isrs_text; ELSE RETURN CAST(( substring(isrs_text from 1 for 2), substring(isrs_text from 3 for 3), substring(isrs_text from 6 for 5), substring(isrs_text from 11 for 5), CAST(substring(isrs_text from 16 for 5) AS int)) AS isrs); END IF; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION isrs_asText( code isrs ) RETURNS text AS $$ SELECT code.country_code || code.locode || code.fairway_section || code.orc || lpad(CAST(code.hectometre AS text), 5, '0'); $$ LANGUAGE sql IMMUTABLE PARALLEL SAFE; COMMIT;