Mercurial > gemma
view schema/tap_tests_data.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 | 52f7264265bb |
children | 5e38667f740c |
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 by via donau -- – Österreichische Wasserstraßen-Gesellschaft mbH -- Software engineering by Intevation GmbH -- Author(s): -- * Tom Gottfried <tom@intevation.de> -- * Sascha Teichmann <sascha.teichmann@intevation.de> -- -- Test data used in *_tests.sql scripts -- INSERT INTO countries VALUES ('AT'), ('RO'), ('DE'); INSERT INTO language_codes VALUES ('DE'); INSERT INTO depth_references VALUES ('ZPG'); INSERT INTO users.responsibility_areas VALUES ('AT', ST_geomfromtext('MULTIPOLYGON(((0 0, 0 1, 1 1, 1 0, 0 0)))', 4326)), ('RO', ST_geomfromtext('MULTIPOLYGON(((1 0, 1 1, 2 1, 2 0, 1 0)))', 4326)); INSERT INTO users.list_users VALUES ( 'waterway_user', 'test_user_at', 'user_at1$', 'AT', NULL, 'xxx'); INSERT INTO users.list_users VALUES ( 'waterway_user', 'test_user_ro', 'user_ro1$', 'RO', NULL, 'xxy'); INSERT INTO users.list_users VALUES ( 'waterway_admin', 'test_admin_at', 'admin_at1$', 'AT', NULL, 'yyy'); INSERT INTO users.list_users VALUES ( 'waterway_admin', 'test_admin_at2', 'admin_at2$', 'AT', NULL, 'yyy'); INSERT INTO users.list_users VALUES ( 'waterway_admin', 'test_admin_ro', 'admin_ro1$', 'RO', NULL, 'yyx'); INSERT INTO users.list_users VALUES ( 'sys_admin', 'test_sys_admin1', 'sys_admin1$', 'AT', NULL, 'zzz'); INSERT INTO limiting_factors VALUES ('depth'), ('width'); WITH gs AS ( INSERT INTO waterway.gauges ( location, validity, objname, geom, zero_point, date_info, source_organization, lastupdate) VALUES ( ('AT', 'XXX', '00001', 'G0001', 1)::isrs, tstzrange(current_timestamp - '1 day'::interval, current_timestamp), 'testgauge', ST_geomfromtext('POINT(0 0)', 4326), 0, current_timestamp, 'testorganization', current_timestamp) RETURNING location, validity), bns AS ( VALUES ( 'testbottleneck1', isrsrange(('AT', 'XXX', '00001', '00000', 0)::isrs, ('AT', 'XXX', '00001', '00000', 2)::isrs), ST_geomfromtext('MULTIPOLYGON(((0 0, 0 1, 1 1, 1 0, 0 0)))', 4326), 'AT', 'AT', 'AT', 1, 'depth', current_timestamp, 'testorganization', false ), ( 'testbottleneck2', isrsrange(('AT', 'XXX', '00001', '00000', 0)::isrs, ('AT', 'XXX', '00001', '00000', 2)::isrs), ST_geomfromtext('MULTIPOLYGON(((0 0, 0 1, 1 1, 1 0, 0 0)))', 4326), 'AT', 'AT', 'AT', 1, 'depth', current_timestamp, 'testorganization', true )) INSERT INTO waterway.bottlenecks ( gauge_location, validity, bottleneck_id, stretch, area, rb, lb, responsible_country, revisiting_time, limiting, date_info, source_organization, staging_done) SELECT * FROM gs, bns; INSERT INTO waterway.distance_marks_virtual VALUES ( ('AT', 'XXX', '00001', '00000', 0)::isrs, ST_SetSRID('POINT(0 0)'::geometry, 4326), 'someENC' ), ( ('AT', 'XXX', '00001', '00000', 1)::isrs, ST_SetSRID('POINT(1 0)'::geometry, 4326), 'someENC' ), ( ('AT', 'XXX', '00001', '00000', 2)::isrs, ST_SetSRID('POINT(1.6 0)'::geometry, 4326), 'someENC' ); INSERT INTO waterway.waterway_axis (wtwaxs, objnam) VALUES ( ST_SetSRID(ST_CurveToLine( 'CIRCULARSTRING(0 0, 0.5 0.5, 0.6 0.4)'), 4326), 'testriver' ), ( ST_SetSRID(ST_CurveToLine('CIRCULARSTRING(0.6 0.4, 1 0, 1.5 -0.00001)'), 4326), 'testriver' ), ( ST_SetSRID('LINESTRING(0.5 0.5, 1 1)'::geometry, 4326), 'testriver' ), ( ST_SetSRID('LINESTRING(1.5 0, 1.55001 0)'::geometry, 4326), 'testriver' ), ( ST_SetSRID('LINESTRING(1.55 0, 2 0)'::geometry, 4326), 'testriver' ); -- Simulate waterway area as non-intersecting buffers around axis WITH RECURSIVE buffer AS ( SELECT id, ST_Buffer(wtwaxs, 10000, 'endcap=flat')::geometry AS buf FROM waterway.waterway_axis), cleaned AS ( (SELECT ARRAY[id] AS ids, buf AS cbuf, buf AS others FROM buffer ORDER BY id FETCH FIRST ROW ONLY) UNION (SELECT ids || id, ST_Difference(buf, others), ST_Union(buf, others) FROM cleaned, buffer WHERE id <> ALL(ids) ORDER BY id ASC, ids DESC FETCH FIRST ROW ONLY)), cleaned1 AS ( SELECT DISTINCT geom FROM (SELECT (ST_Dump(cbuf)).geom FROM cleaned) AS dmp, waterway.waterway_axis WHERE ST_Intersects(geom, wtwaxs)) INSERT INTO waterway.waterway_area (area) SELECT geom FROM cleaned1; INSERT INTO users.templates (template_name, country, template_data) VALUES ('AT', 'AT', '\x'), ('RO', 'RO', '\x'); WITH job AS ( INSERT INTO import.imports (kind, username, data) VALUES ( 'test', 'test_admin_ro', 'test') RETURNING id), log AS ( INSERT INTO import.import_logs (import_id, msg) SELECT id, 'test' FROM job) INSERT INTO import.track_imports (import_id, relation, key) SELECT id, 'waterway.bottlenecks', 1 FROM job; WITH config AS ( INSERT INTO import.import_configuration (kind, username) VALUES ( 'test', 'test_admin_ro') RETURNING id) INSERT INTO import.import_configuration_attributes SELECT id, 'test key', 'test value' FROM config;