My project is using PostgreSQL 12.9 and has a table that gets millions of
positions of ships every day. It is partitioned by tier (the importance of
position) and sub-partitioned by time.
We also use RLS to restrict access to rows in the table.
Our problem is the query planner will not let user queries use the spatial
condition like it does for the table owner and as a result the queries
don't ever finish.
I created a minimal test case of the problem and have attached all of the
statements needed to recreate the problem
The key statements are as follows:
CREATE TABLE IF NOT EXISTS test.qtest_position (
position_id bigint NOT NULL,
tier smallint,
toi timestamptz(0) NOT NULL,
track_id bigint,
security_tag varchar(33),
posit public.geometry(Point)
) PARTITION BY LIST (tier);
CREATE TABLE IF NOT EXISTS test.qtest_posit_t1 PARTITION OF
test.qtest_position
FOR VALUES IN (1) PARTITION BY RANGE (toi);
CREATE TABLE test.qtest_posit_t1_template ( LIKE test.qtest_posit_t1
INCLUDING ALL );
ALTER TABLE test.qtest_posit_t1_template ADD PRIMARY KEY (position_id);
CREATE INDEX ON test.qtest_posit_t1_template (track_id, toi);
CREATE INDEX ON test.qtest_posit_t1_template USING GIST (posit, toi)
include (security_tag);
SELECT partman.create_parent('test.qtest_posit_t1', 'toi', 'native', '1
days',
p_premake := 20, p_start_partition := '2022-09-01', p_template_table :=
'test.qtest_posit_t1_template');
Here's a simple example of a user query and the resulting plan:
set role test_user;
SET
explain analyze SELECT
position_961.SECURITY_TAG,
position_961.TRACK_ID,
position_961.POSIT,
position_961.POSITION_ID,
position_961.TOI
FROM
test.qtest_position position_961
WHERE
(ST_Intersects( position_961.POSIT,
ST_SETSRID(ST_GeomFromGeoJSON('{"type":"Polygon","coordinates":[[[
67.729806,
15.734972],[49.077444,-9.989],[39.995417,-3.982083],[51.616528,17.977861],[67.729806,15.734972]]]}')
, 4326) )
) AND
position_961.TOI BETWEEN '2022-09-02T20:28:42.753Z'::TIMESTAMPTZ AND
'2022-09-03T20:28:42.753Z'::TIMESTAMPTZ
and test.user_has_access(security_tag) = '1';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
Append (cost=0.42..495598323.78 rows=2 width=60) (actual
time=7246.524..31614.944 rows=699 loops=1)
-> Index Scan using
qtest_posit_t1_p2022_09_02_posit_toi_security_tag_idx on
qtest_posit_t1_p2022_09_02 position_961 (cost=0.42..72721949.89 rows=1
width=60) (actual time=7246.523..9560.695 rows=
121 loops=1)
Index Cond: ((toi >= '2022-09-02 20:28:42.753+00'::timestamp with
time zone) AND (toi <= '2022-09-03 20:28:42.753+00'::timestamp with time
zone))
Filter: ((test.user_has_access(security_tag) = '1'::text) AND
(test.user_has_access(security_tag) = '1'::text) AND st_intersects(posit,
'0103000020E610000001000000050000002C9B3924B5EE504091F3
FE3F4E782F40BEA25BAFE9894840EE7C3F355EFA23C0F47002D369FF434019A9F7544EDB0FC0BE88B663EACE4940AB08371955FA31402C9B3924B5EE504091F3FE3F4E782F40'::geometry))
Rows Removed by Filter: 2851335
-> Seq Scan on qtest_posit_t1_p2022_09_03 position_961_1
(cost=0.00..422876373.88 rows=1 width=60) (actual time=84.624..22054.050
rows=578 loops=1)
Filter: ((toi >= '2022-09-02 20:28:42.753+00'::timestamp with time
zone) AND (toi <= '2022-09-03 20:28:42.753+00'::timestamp with time zone)
AND (test.user_has_access(security_tag) = '1'::tex
t) AND (test.user_has_access(security_tag) = '1'::text) AND
st_intersects(posit,
'0103000020E610000001000000050000002C9B3924B5EE504091F3FE3F4E782F40BEA25BAFE9894840EE7C3F355EFA23C0F47002D369FF434019A9
F7544EDB0FC0BE88B663EACE4940AB08371955FA31402C9B3924B5EE504091F3FE3F4E782F40'::geometry))
Rows Removed by Filter: 16561241
Planning Time: 0.369 ms
Execution Time: 31615.459 ms
(10 rows)
-- run the same query as table owner
set role test_owner;
SET
explain analyze SELECT
position_961.SECURITY_TAG,
position_961.TRACK_ID,
position_961.POSIT,
position_961.POSITION_ID,
position_961.TOI
FROM
test.qtest_position position_961
WHERE
(ST_Intersects( position_961.POSIT,
ST_SETSRID(ST_GeomFromGeoJSON('{"type":"Polygon","coordinates":[[[
67.729806,
15.734972],[49.077444,-9.989],[39.995417,-3.982083],[51.616528,17.977861],[67.729806,15.734972]]]}')
, 4326) )
) AND
position_961.TOI BETWEEN '2022-09-02T20:28:42.753Z'::TIMESTAMPTZ AND
'2022-09-03T20:28:42.753Z'::TIMESTAMPTZ
and test.user_has_access(security_tag) = '1';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Append (cost=0.42..88190.82 rows=16 width=60) (actual time=0.167..6.259
rows=699 loops=1)
-> Index Scan using
qtest_posit_t1_p2022_09_02_posit_toi_security_tag_idx on
qtest_posit_t1_p2022_09_02 position_961 (cost=0.42..10309.24 rows=2
width=60) (actual time=0.167..1.320 rows=121 loops
=1)
Index Cond: ((posit &&
'0103000020E610000001000000050000002C9B3924B5EE504091F3FE3F4E782F40BEA25BAFE9894840EE7C3F355EFA23C0F47002D369FF434019A9F7544EDB0FC0BE88B663EACE4940AB08371955FA31402C9B3
924B5EE504091F3FE3F4E782F40'::geometry) AND (toi >= '2022-09-02
20:28:42.753+00'::timestamp with time zone) AND (toi <= '2022-09-03
20:28:42.753+00'::timestamp with time zone))
Filter: ((test.user_has_access(security_tag) = '1'::text) AND
st_intersects(posit,
'0103000020E610000001000000050000002C9B3924B5EE504091F3FE3F4E782F40BEA25BAFE9894840EE7C3F355EFA23C0F47002D36
9FF434019A9F7544EDB0FC0BE88B663EACE4940AB08371955FA31402C9B3924B5EE504091F3FE3F4E782F40'::geometry))
Rows Removed by Filter: 201
-> Index Scan using
qtest_posit_t1_p2022_09_03_posit_toi_security_tag_idx on
qtest_posit_t1_p2022_09_03 position_961_1 (cost=0.42..77881.49 rows=14
width=60) (actual time=0.484..4.876 rows=578 lo
ops=1)
Index Cond: ((posit &&
'0103000020E610000001000000050000002C9B3924B5EE504091F3FE3F4E782F40BEA25BAFE9894840EE7C3F355EFA23C0F47002D369FF434019A9F7544EDB0FC0BE88B663EACE4940AB08371955FA31402C9B3
924B5EE504091F3FE3F4E782F40'::geometry) AND (toi >= '2022-09-02
20:28:42.753+00'::timestamp with time zone) AND (toi <= '2022-09-03
20:28:42.753+00'::timestamp with time zone))
Filter: ((test.user_has_access(security_tag) = '1'::text) AND
st_intersects(posit,
'0103000020E610000001000000050000002C9B3924B5EE504091F3FE3F4E782F40BEA25BAFE9894840EE7C3F355EFA23C0F47002D36
9FF434019A9F7544EDB0FC0BE88B663EACE4940AB08371955FA31402C9B3924B5EE504091F3FE3F4E782F40'::geometry))
Rows Removed by Filter: 1151
Planning Time: 0.525 ms
Execution Time: 6.324 ms
(11 rows)
Is there something I can do to allow users queries to use the index with a
condition like that used for the table owner's query?
system: centos 7 PostgreSQL 12.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC)
4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
-- DB set steps
CREATE SCHEMA IF NOT EXISTS partman;
CREATE EXTENSION IF NOT EXISTS pg_partman SCHEMA partman;
CREATE ROLE test_owner LOGIN;
CREATE ROLE test_user LOGIN;
CREATE SCHEMA test AUTHORIZATION test_owner;
GRANT USAGE on SCHEMA partman to test_owner;
GRANT USAGE on SCHEMA test to test_user;
set role test_owner;
-- create the qtest_position table and its partitions
CREATE TABLE IF NOT EXISTS test.qtest_position (
position_id bigint NOT NULL,
tier smallint,
toi timestamptz(0) NOT NULL,
track_id bigint,
security_tag varchar(33),
posit public.geometry(Point)
) PARTITION BY LIST (tier);
CREATE TABLE IF NOT EXISTS test.qtest_posit_t1 PARTITION OF test.qtest_position
FOR VALUES IN (1) PARTITION BY RANGE (toi);
-- now prepare to create the partitions for tier=1 position data
-- Note: in practice there are other tier=x partitons but they are not needed
to replicate the problem
CREATE TABLE test.qtest_posit_t1_template ( LIKE test.qtest_posit_t1 INCLUDING
ALL );
ALTER TABLE test.qtest_posit_t1_template ADD PRIMARY KEY (position_id);
CREATE INDEX ON test.qtest_posit_t1_template (track_id, toi);
CREATE INDEX ON test.qtest_posit_t1_template USING GIST (posit, toi) include
(security_tag);
-- define a simple function as a standin for the complicated one used for
access control
CREATE OR REPLACE FUNCTION test.user_has_access(code varchar)
RETURNS text
AS $$
BEGIN
return '1'::text;
END;
$$ LANGUAGE plpgsql STABLE STRICT;
reset role;
-- create some partitons
SELECT partman.create_parent('test.qtest_posit_t1', 'toi', 'native', '1 days',
p_premake := 20, p_start_partition := '2022-09-01', p_template_table :=
'test.qtest_posit_t1_template');
set role test_owner;
grant select on test.qtest_position to test_owner;
grant select on test.qtest_position to test_user;
CREATE POLICY sec_select_on_qtest_position ON test.qtest_position
FOR ALL USING (test.user_has_access(security_tag) = '1') with check
(test.user_has_access(security_tag) = '1');
ALTER TABLE test.qtest_position ENABLE ROW LEVEL SECURITY;
CREATE POLICY sec_select_on_qtest_posit_t1 ON test.qtest_posit_t1
FOR ALL USING (test.user_has_access(security_tag) = '1') with check
(test.user_has_access(security_tag) = '1');
ALTER TABLE test.qtest_posit_t1 ENABLE ROW LEVEL SECURITY;
-- now insert some data into this table
insert into test.qtest_position
with trks as (select (s::BIGINT * 100::BIGINT) id from
generate_series(200001,600000,1) s),
pos as (select s::BIGINT id from generate_series(1,90, 1) s)
select (t.id + p.id), 1,
('2022-09-02'::timestamptz + (trunc(random() * 160000)::text || '
seconds')::interval),
t.id,
(case when random() <= .5 then 'ok' else 'other' end),
ST_SetSRID(ST_MakePoint((-179.9 + 359.9 * random()), -60.0 * 120.0 *
random()), 4326)
from trks t, pos p;
INSERT 0 36000000
-- Run analyze againts all the tables of concern
analyze test.qtest_position;
analyze test.qtest_posit_t1_p2022_09_01;
analyze test.qtest_posit_t1_p2022_09_02;
analyze test.qtest_posit_t1_p2022_09_03;
analyze test.qtest_posit_t1_p2022_09_03;
-- ===========================================================================
-- now run a spatial query a user might run
set role test_user;
SET
explain analyze SELECT
position_961.SECURITY_TAG,
position_961.TRACK_ID,
position_961.POSIT,
position_961.POSITION_ID,
position_961.TOI
FROM
test.qtest_position position_961
WHERE
(ST_Intersects( position_961.POSIT,
ST_SETSRID(ST_GeomFromGeoJSON('{"type":"Polygon","coordinates":[[[ 67.729806,
15.734972],[49.077444,-9.989],[39.995417,-3.982083],[51.616528,17.977861],[67.729806,15.734972]]]}')
, 4326) )
) AND
position_961.TOI BETWEEN '2022-09-02T20:28:42.753Z'::TIMESTAMPTZ AND
'2022-09-03T20:28:42.753Z'::TIMESTAMPTZ
and test.user_has_access(security_tag) = '1';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
Append (cost=0.42..495598323.78 rows=2 width=60) (actual
time=7246.524..31614.944 rows=699 loops=1)
-> Index Scan using qtest_posit_t1_p2022_09_02_posit_toi_security_tag_idx
on qtest_posit_t1_p2022_09_02 position_961 (cost=0.42..72721949.89 rows=1
width=60) (actual time=7246.523..9560.695 rows=
121 loops=1)
Index Cond: ((toi >= '2022-09-02 20:28:42.753+00'::timestamp with time
zone) AND (toi <= '2022-09-03 20:28:42.753+00'::timestamp with time zone))
Filter: ((test.user_has_access(security_tag) = '1'::text) AND
(test.user_has_access(security_tag) = '1'::text) AND st_intersects(posit,
'0103000020E610000001000000050000002C9B3924B5EE504091F3
FE3F4E782F40BEA25BAFE9894840EE7C3F355EFA23C0F47002D369FF434019A9F7544EDB0FC0BE88B663EACE4940AB08371955FA31402C9B3924B5EE504091F3FE3F4E782F40'::geometry))
Rows Removed by Filter: 2851335
-> Seq Scan on qtest_posit_t1_p2022_09_03 position_961_1
(cost=0.00..422876373.88 rows=1 width=60) (actual time=84.624..22054.050
rows=578 loops=1)
Filter: ((toi >= '2022-09-02 20:28:42.753+00'::timestamp with time
zone) AND (toi <= '2022-09-03 20:28:42.753+00'::timestamp with time zone) AND
(test.user_has_access(security_tag) = '1'::tex
t) AND (test.user_has_access(security_tag) = '1'::text) AND
st_intersects(posit,
'0103000020E610000001000000050000002C9B3924B5EE504091F3FE3F4E782F40BEA25BAFE9894840EE7C3F355EFA23C0F47002D369FF434019A9
F7544EDB0FC0BE88B663EACE4940AB08371955FA31402C9B3924B5EE504091F3FE3F4E782F40'::geometry))
Rows Removed by Filter: 16561241
Planning Time: 0.369 ms
Execution Time: 31615.459 ms
(10 rows)
-- run the same query as test_owner
set role test_owner;
SET
explain analyze SELECT
position_961.SECURITY_TAG,
position_961.TRACK_ID,
position_961.POSIT,
position_961.POSITION_ID,
position_961.TOI
FROM
test.qtest_position position_961
WHERE
(ST_Intersects( position_961.POSIT,
ST_SETSRID(ST_GeomFromGeoJSON('{"type":"Polygon","coordinates":[[[ 67.729806,
15.734972],[49.077444,-9.989],[39.995417,-3.982083],[51.616528,17.977861],[67.729806,15.734972]]]}')
, 4326) )
) AND
position_961.TOI BETWEEN '2022-09-02T20:28:42.753Z'::TIMESTAMPTZ AND
'2022-09-03T20:28:42.753Z'::TIMESTAMPTZ
and test.user_has_access(security_tag) = '1';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Append (cost=0.42..88190.82 rows=16 width=60) (actual time=0.167..6.259
rows=699 loops=1)
-> Index Scan using qtest_posit_t1_p2022_09_02_posit_toi_security_tag_idx
on qtest_posit_t1_p2022_09_02 position_961 (cost=0.42..10309.24 rows=2
width=60) (actual time=0.167..1.320 rows=121 loops
=1)
Index Cond: ((posit &&
'0103000020E610000001000000050000002C9B3924B5EE504091F3FE3F4E782F40BEA25BAFE9894840EE7C3F355EFA23C0F47002D369FF434019A9F7544EDB0FC0BE88B663EACE4940AB08371955FA31402C9B3
924B5EE504091F3FE3F4E782F40'::geometry) AND (toi >= '2022-09-02
20:28:42.753+00'::timestamp with time zone) AND (toi <= '2022-09-03
20:28:42.753+00'::timestamp with time zone))
Filter: ((test.user_has_access(security_tag) = '1'::text) AND
st_intersects(posit,
'0103000020E610000001000000050000002C9B3924B5EE504091F3FE3F4E782F40BEA25BAFE9894840EE7C3F355EFA23C0F47002D36
9FF434019A9F7544EDB0FC0BE88B663EACE4940AB08371955FA31402C9B3924B5EE504091F3FE3F4E782F40'::geometry))
Rows Removed by Filter: 201
-> Index Scan using qtest_posit_t1_p2022_09_03_posit_toi_security_tag_idx
on qtest_posit_t1_p2022_09_03 position_961_1 (cost=0.42..77881.49 rows=14
width=60) (actual time=0.484..4.876 rows=578 lo
ops=1)
Index Cond: ((posit &&
'0103000020E610000001000000050000002C9B3924B5EE504091F3FE3F4E782F40BEA25BAFE9894840EE7C3F355EFA23C0F47002D369FF434019A9F7544EDB0FC0BE88B663EACE4940AB08371955FA31402C9B3
924B5EE504091F3FE3F4E782F40'::geometry) AND (toi >= '2022-09-02
20:28:42.753+00'::timestamp with time zone) AND (toi <= '2022-09-03
20:28:42.753+00'::timestamp with time zone))
Filter: ((test.user_has_access(security_tag) = '1'::text) AND
st_intersects(posit,
'0103000020E610000001000000050000002C9B3924B5EE504091F3FE3F4E782F40BEA25BAFE9894840EE7C3F355EFA23C0F47002D36
9FF434019A9F7544EDB0FC0BE88B663EACE4940AB08371955FA31402C9B3924B5EE504091F3FE3F4E782F40'::geometry))
Rows Removed by Filter: 1151
Planning Time: 0.525 ms
Execution Time: 6.324 ms
(11 rows)
-- Note this example has relatively few rows in each table and returns results.
I have also culled over 70 columns from the qtest_position table to simplify
the example.
-- In the real case there are too many rows and the plan users get never
returns any results for users that aren't the owner of the table.
-- I have tried making the spatial index just contain the posit column but when
I do that the planner picks the track_toi index instead and again uses the toi
range condition.
-- I have also granted select access to all of the table partitions but that
doesn't help either.
-- Desire to know if there is anything that can be done to get the planner to
use the correct spatial condition for the spatial index.
