The following bug has been logged on the website:
Bug reference: 15251
Logged by: Peter
Email address: pgsqlpl@gmail.com
PostgreSQL version: 10.4
Operating system: Ubuntu 14.04
Description:
Description:
Plan of SELECT query on a parent table, which actually get data from an
inherited table (partition) depends on grants for a partition.
- When current_user is missing a grant to SELECT from a partition then
planner incorrectly decides to use BitmapAnd on two GIN Indexes (see "Bad
plan" below).
- When current_user has grant to SELECT from a partition then planner
correctly decides to use one of two GIN Indexes which is more reductive.
Reproducible on:
- Ubuntu 10.4-2.pgdg14.04+1
- Ubuntu 9.6.9-2.pgdg14.04+1
- Ubuntu 9.5.13-2.pgdg14.04+1
Code:
CREATE EXTENSION IF NOT EXISTS pg_trgm; -- for gin_trgm_ops
-- base table
DROP TABLE IF EXISTS c CASCADE;
CREATE TABLE c
(
id bigserial NOT NULL PRIMARY KEY
, ts timestamptz NOT NULL --
partitioning
, p text NOT NULL -- trigram based
search
, t int[] NOT NULL DEFAULT '{0}' -- row level
security
);
CREATE INDEX g3_c_p ON c USING GIN (p gin_trgm_ops);
CREATE INDEX gx_c_t ON c USING GIN(t);
-- create partition
CREATE TABLE cc201806
(
CHECK (ts >= '2018-06-01 00:00:00'
AND ts < '2018-07-01 00:00:00')
) INHERITS (c);
CREATE EXTENSION IF NOT EXISTS pgcrypto; -- gen_random_bytes
-- populate partition
INSERT INTO cc201806 (id, ts, p) SELECT i, clock_timestamp(),
encode(gen_random_bytes(11), 'hex') FROM generate_series(1, 1000000) AS
gs(i);
-- make indexes after instert to speedup above insert
CREATE INDEX g3_cc201806_p ON cc201806 USING GIN (p gin_trgm_ops);
CREATE INDEX gx_cc201806_t ON cc201806 USING GIN(t);
ANALYZE; -- have good statistics
-- use restricted role (in actual DB, it is an owner of SECURITY DEFINER
function, executing following query as a dynamic statement)
DROP ROLE IF EXISTS u;
CREATE ROLE u;
GRANT SELECT ON c TO u;
;
-- exec once to cache data
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM c
WHERE p ~ 'bbbb'
AND t && '{1, 2, 0}'::int[] -- this array is actually a function
call
;
-- missing GRANT SELECT ON partition
REVOKE SELECT ON cc201806 FROM u;
SET ROLE u;
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM c
WHERE p ~ 'bbbb'
AND t && '{1, 2, 0}'::int[] -- this array is actually a function
call
;
RESET ROLE;
GRANT SELECT ON cc201806 TO u;
SET ROLE u;
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM c
WHERE p ~ 'bbbb'
AND t && '{1, 2, 0}'::int[] -- this array is actually a function
call
;
SELECT version();
SHOW work_mem; -- 4MB
SHOW effective_cache_size; -- 4GB
SHOW shared_buffers; -- 256MB
Examples of resulting plans:
Bad plan:
-------------------------------------------------------------------------------------------------------------------------------------------------
Append (cost=0.00..2233.50 rows=120 width=64) (actual
time=157.626..167.316 rows=276 loops=1)
Buffers: shared hit=3866
-> Seq Scan on c (cost=0.00..0.00 rows=1 width=80) (actual
time=0.001..0.001 rows=0 loops=1)
Filter: ((p ~ 'bbbb'::text) AND (t && '{1,2,0}'::integer[]))
-> Bitmap Heap Scan on cc201806 (cost=1792.25..2233.50 rows=119
width=64) (actual time=157.624..167.284 rows=276 loops=1)
Recheck Cond: ((p ~ 'bbbb'::text) AND (t &&
'{1,2,0}'::integer[]))
Rows Removed by Index Recheck: 4212
Heap Blocks: exact=3723
Buffers: shared hit=3866
-> BitmapAnd (cost=1792.25..1792.25 rows=119 width=0) (actual
time=157.019..157.019 rows=0 loops=1)
Buffers: shared hit=143
-> Bitmap Index Scan on g3_cc201806_p (cost=0.00..84.00
rows=8000 width=0) (actual time=1.608..1.608 rows=4488 loops=1)
Index Cond: (p ~ 'bbbb'::text)
Buffers: shared hit=7
-> Bitmap Index Scan on gx_cc201806_t (cost=0.00..1707.94
rows=14925 width=0) (actual time=155.163..155.163 rows=1000000 loops=1)
Index Cond: (t && '{1,2,0}'::integer[])
Buffers: shared hit=136
Planning time: 0.316 ms
Execution time: 167.372 ms
Good plan:
-----------------------------------------------------------------------------------------------------------------------------------
Append (cost=0.00..390.13 rows=101 width=64) (actual time=2.192..11.944
rows=276 loops=1)
Buffers: shared hit=3730
-> Seq Scan on c (cost=0.00..0.00 rows=1 width=80) (actual
time=0.001..0.001 rows=0 loops=1)
Filter: ((p ~ 'bbbb'::text) AND (t && '{1,2,0}'::integer[]))
-> Bitmap Heap Scan on cc201806 (cost=16.77..390.13 rows=100 width=64)
(actual time=2.189..11.912 rows=276 loops=1)
Recheck Cond: (p ~ 'bbbb'::text)
Rows Removed by Index Recheck: 4212
Filter: (t && '{1,2,0}'::integer[])
Heap Blocks: exact=3723
Buffers: shared hit=3730
-> Bitmap Index Scan on g3_cc201806_p (cost=0.00..16.75 rows=100
width=0) (actual time=1.565..1.565 rows=4488 loops=1)
Index Cond: (p ~ 'bbbb'::text)
Buffers: shared hit=7
Planning time: 0.413 ms
Execution time: 11.996 ms
