Mercurial > gemma
comparison schema/updates/1105/01.improve_rls_performance.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 | |
| children |
comparison
equal
deleted
inserted
replaced
| 4160:7cccf7fef3e8 | 4161:64cd18281c76 |
|---|---|
| 1 CREATE OR REPLACE FUNCTION import.is_new_key( | |
| 2 tablename varchar, | |
| 3 kv anyelement) | |
| 4 RETURNS boolean | |
| 5 AS $$ | |
| 6 DECLARE columnname varchar; | |
| 7 DECLARE ret boolean; | |
| 8 BEGIN | |
| 9 columnname = (SELECT column_name | |
| 10 FROM information_schema.constraint_column_usage k | |
| 11 JOIN information_schema.table_constraints USING (constraint_name) | |
| 12 WHERE k.table_name = tablename and constraint_type = 'PRIMARY KEY'); | |
| 13 EXECUTE format('SELECT NOT EXISTS(SELECT 1 FROM import.%I WHERE %I = $1)', | |
| 14 tablename, columnname) | |
| 15 INTO ret | |
| 16 USING kv; | |
| 17 RETURN ret; | |
| 18 END; | |
| 19 $$ | |
| 20 LANGUAGE plpgsql | |
| 21 SECURITY DEFINER | |
| 22 STABLE PARALLEL SAFE; | |
| 23 | |
| 24 DROP POLICY parent_allowed ON import.import_logs; | |
| 25 CREATE POLICY parent_allowed ON import.import_logs | |
| 26 FOR ALL TO waterway_admin | |
| 27 USING (EXISTS(SELECT 1 FROM import.imports WHERE id = import_id)) | |
| 28 WITH CHECK (import.is_new_key('imports', import_id) | |
| 29 OR EXISTS(SELECT 1 FROM import.imports WHERE id = import_id)); | |
| 30 | |
| 31 DROP POLICY parent_allowed ON import.track_imports; | |
| 32 CREATE POLICY parent_allowed ON import.track_imports | |
| 33 FOR ALL TO waterway_admin | |
| 34 USING (EXISTS(SELECT 1 FROM import.imports WHERE id = import_id)) | |
| 35 WITH CHECK (import.is_new_key('imports', import_id) | |
| 36 OR EXISTS(SELECT 1 FROM import.imports WHERE id = import_id)); | |
| 37 | |
| 38 DROP POLICY import_configuration_policy ON import.import_configuration; | |
| 39 CREATE POLICY import_configuration_policy ON import.import_configuration | |
| 40 FOR ALL TO waterway_admin | |
| 41 USING (EXISTS(SELECT 1 FROM users.list_users lu | |
| 42 WHERE lu.username = import_configuration.username)); | |
| 43 | |
| 44 DROP POLICY parent_allowed ON import.import_configuration_attributes; | |
| 45 CREATE POLICY parent_allowed ON import.import_configuration_attributes | |
| 46 FOR ALL TO waterway_admin | |
| 47 USING (EXISTS(SELECT 1 FROM import.import_configuration | |
| 48 WHERE id = import_configuration_id)) | |
| 49 WITH CHECK ( | |
| 50 import.is_new_key('import_configuration', import_configuration_id) | |
| 51 OR EXISTS(SELECT 1 FROM import.import_configuration | |
| 52 WHERE id = import_configuration_id)); |