Different query plans using different roles
| От | Charles |
|---|---|
| Тема | Different query plans using different roles |
| Дата | |
| Msg-id | CADBa3wbkFAw5EVYskKU-aJv1K2CN72FPQVDrLvszGHdWErGJ4w@mail.gmail.com обсуждение исходный текст |
| Список | pgsql-general |
I have a situation where the same query generates two different plans depending on which user executes it. Two of the tables do have row-level security enabled, the policy is roughly
POLICY "policy_qai_U0002_7900_0" FOR SELECT
TO qai_U0002
USING (true)
Here is the plan when the owner executes it
Nested Loop (cost=1.30..1078.74 rows=1 width=24)
-> Nested Loop (cost=0.72..1070.34 rows=1 width=30)
-> Index Scan using vw_secmap_eid_idx1 on vw_secmap map (cost=0.29..982.62 rows=3 width=12)
Index Cond: (eid = ANY ('{1000050689,1000050721,1000050761,1000050824,1000050827,1000050973,1000051050,1000051140,1000051165,1000051354,1000051356,1000051411,1000051646,1000052098,1000052194,1000052204,1000052355,1000052517,1000052671,1000052947,1000053062,1000053272,1000053430,1000053575,1000053585,1000053608,1000053614,1000053732,1000054102,1000054183,1000054195,1000054373,1000054472,1000054608,1000054647,1000054666,1000054864,1000054951,1000054996,1000055021,1000055062,1000055066,1000055073,1000055078,1000055132,1000055156,1000055274,1000055299,1000055328,1000055345,1000055401,1000055937,1000056046,1000056179,1000056230,1000056344,1000056415,1000056578,1000056653,1000056797,1000056812,1000057027,1000057056,1000057184,1000057508,1000057708,1000057713,1000057736,1000057888,1000057905,1000058022,1000058032,1000058132,1000058439,1000058479,1000058484,1000058518,1000058586,1000058590,1000058635,1000058654,1000058901,1000058973,1000059049,1000059099,1000059138,1000059198,1000059204,1000059262,1000059324,1000059383,1000059458,1000059689,1000059902,1000060330,1000060424,1000060428,1000060484,1000060537,1000060569,1000060678,1000060769,1000061003,1000061234,1000061433,1000061441,1000061685,1000061718,1000061758,1000061824,1000061840,1000061866,1000068093,1000068114,1000068176,1000068180,1000068372,1000068398,1000068409,1000068423,1000068498,1000068542,1000068633,1000068839,1000068868,1000068871,1000069196,1000069501,1000069625,1000069680,1000069854,1000070036,1000070166,1000070212,1000070221,1000070322,1000070618,1000070743,1000070751,1000070816,1000070839,1000071164,1000071169,1000072079,1000072168,1000072199,1000072246,1000072457,1000072598,1000073018,1000073149,1000073269,1000073354,1000073378,1000073455,1000073535,1000073743,1000073746,1000073828,1000073861,1000074022,1000074143,1000074197,1000074290,1000074504,1000074753,1000074961,1000075006,1000075072,1000075138,1000075143,1000075201,1000075225,1000075295,1000075386,1000075457,1000075523,1000075613,1000075732,1000075775,1000075783,1000075798,1000075856,1000076009,1000076046,1000076136,1000076212,1000076223,1000076393,1000076616,1000076752,1000077019,1000077140,1000077157,1000077168,1000077325,1000077346,1000077350,1000077632,1000077746,1000077787,1000077840,1000077848,1000077910,1000078004,1000078130,1000078150,1000078216,1000078322,1000078331,1000078373,1000078517,1000078542,1000078545,1000078553,1000078594,1000078702,1000078786,1000079523,1000079618,1000079726,1000079730,1000079732,1000079785,1000079846,1000080214,1000080266,1000080457,1000080493,1000080567,1000080650,1000080663,1000080721,1000080738,1000080885,1000080910,1000080935,1000080967,1000080974,1000081174,1000081175,1000081183,1000081517,1000081721,1000081912,1000081951,1000082022,1000082103,1000082119,1000082220,1000082239,1000082332,1000082588,1000082589,1000082629,1000082655}'::bigint[]))
-> Index Scan using wsddata_code_freq_value__idx on wsddata r (cost=0.43..29.14 rows=10 width=18)
Index Cond: ((code = map.vencode) AND (value_ >= '2015-04-30 00:00:00'::timestamp without time zone) AND (value_ <= '2016-04-30 00:00:00'::timestamp without time zone))
-> Index Scan using wsndata_pkey on wsndata n (cost=0.57..8.38 rows=1 width=18)
Index Cond: ((code = r.code) AND (item = 3501) AND ((freq)::text = 'A'::text) AND (year_ = r.year_) AND (seq = r.seq))
Filter: ((r.freq)::text = (CASE freq WHEN 'R'::text THEN 'Q'::character varying ELSE freq END)::text)
(9 rows)
However, when "qai_U0002" executes the query it gets this plan:
Nested Loop (cost=117777.22..22751803.43 rows=1 width=24)
Join Filter: ((r.code = n.code) AND (r.year_ = n.year_) AND (r.seq = n.seq) AND ((r.freq)::text = (CASE n.freq WHEN 'R'::text THEN 'Q'::character varying ELSE n.freq END)::text))
-> Hash Join (cost=117777.22..266330.48 rows=1 width=30)
Hash Cond: (r.code = map.vencode)
-> Bitmap Heap Scan on wsddata r (cost=117010.56..260781.62 rows=294289 width=18)
Recheck Cond: ((value_ >= '2015-04-30 00:00:00'::timestamp without time zone) AND (value_ <= '2016-04-30 00:00:00'::timestamp without time zone) AND (item = 5905))
-> Bitmap Index Scan on wsddata_code_freq_value__idx (cost=0.00..116936.99 rows=294289 width=0)
Index Cond: ((value_ >= '2015-04-30 00:00:00'::timestamp without time zone) AND (value_ <= '2016-04-30 00:00:00'::timestamp without time zone))
-> Hash (cost=766.62..766.62 rows=3 width=12)
-> Index Scan using vw_secmap_eid_idx1 on vw_secmap map (cost=0.29..766.62 rows=3 width=12)
Index Cond: (eid = ANY ('{1000050689,1000050721,1000050761,1000050824,1000050827,1000050973,1000051050,1000051140,1000051165,1000051354,1000051356,1000051411,1000051646,1000052098,1000052194,1000052204,1000052355,1000052517,1000052671,1000052947,1000053062,1000053272,1000053430,1000053575,1000053585,1000053608,1000053614,1000053732,1000054102,1000054183,1000054195,1000054373,1000054472,1000054608,1000054647,1000054666,1000054864,1000054951,1000054996,1000055021,1000055062,1000055066,1000055073,1000055078,1000055132,1000055156,1000055274,1000055299,1000055328,1000055345,1000055401,1000055937,1000056046,1000056179,1000056230,1000056344,1000056415,1000056578,1000056653,1000056797,1000056812,1000057027,1000057056,1000057184,1000057508,1000057708,1000057713,1000057736,1000057888,1000057905,1000058022,1000058032,1000058132,1000058439,1000058479,1000058484,1000058518,1000058586,1000058590,1000058635,1000058654,1000058901,1000058973,1000059049,1000059099,1000059138,1000059198,1000059204,1000059262,1000059324,1000059383,1000059458,1000059689,1000059902,1000060330,1000060424,1000060428,1000060484,1000060537,1000060569,1000060678,1000060769,1000061003,1000061234,1000061433,1000061441,1000061685,1000061718,1000061758,1000061824,1000061840,1000061866,1000068093,1000068114,1000068176,1000068180,1000068372,1000068398,1000068409,1000068423,1000068498,1000068542,1000068633,1000068839,1000068868,1000068871,1000069196,1000069501,1000069625,1000069680,1000069854,1000070036,1000070166,1000070212,1000070221,1000070322,1000070618,1000070743,1000070751,1000070816,1000070839,1000071164,1000071169,1000072079,1000072168,1000072199,1000072246,1000072457,1000072598,1000073018,1000073149,1000073269,1000073354,1000073378,1000073455,1000073535,1000073743,1000073746,1000073828,1000073861,1000074022,1000074143,1000074197,1000074290,1000074504,1000074753,1000074961,1000075006,1000075072,1000075138,1000075143,1000075201,1000075225,1000075295,1000075386,1000075457,1000075523,1000075613,1000075732,1000075775,1000075783,1000075798,1000075856,1000076009,1000076046,1000076136,1000076212,1000076223,1000076393,1000076616,1000076752,1000077019,1000077140,1000077157,1000077168,1000077325,1000077346,1000077350,1000077632,1000077746,1000077787,1000077840,1000077848,1000077910,1000078004,1000078130,1000078150,1000078216,1000078322,1000078331,1000078373,1000078517,1000078542,1000078545,1000078553,1000078594,1000078702,1000078786,1000079523,1000079618,1000079726,1000079730,1000079732,1000079785,1000079846,1000080214,1000080266,1000080457,1000080493,1000080567,1000080650,1000080663,1000080721,1000080738,1000080885,1000080910,1000080935,1000080967,1000080974,1000081174,1000081175,1000081183,1000081517,1000081721,1000081912,1000081951,1000082022,1000082103,1000082119,1000082220,1000082239,1000082332,1000082588,1000082589,1000082629,1000082655}'::bigint[]))
-> Seq Scan on wsndata n (cost=0.00..22437445.52 rows=1477767 width=18)
Filter: ((item = 3501) AND ((freq)::text = 'A'::text))
(13 rows)
I am quite perplexed, why is this happening?
postgresql-server version: 9.5.2
Best
Charles
В списке pgsql-general по дате отправления: