Content-Type: multipart/mixed;
 boundary=d0bcf6a84f8342a99f6f8896a62b8fcfa9cb23e115dda24b65762034b0e3
Mime-Version: 1.0
Date: Sat, 22 Nov 2025 19:43:40 -0500
Message-Id: <DEFNP3WX7BLX.1X7DS1P9X6JIF@nmfay.com>
Subject: Re: pg_plan_advice
From: "Dian Fay" <di@nmfay.com>
To: "Robert Haas" <robertmhaas@gmail.com>, "Matheus Alcantara"
 <matheusssilv97@gmail.com>
Cc: "Jakub Wartak" <jakub.wartak@enterprisedb.com>, "PostgreSQL Hackers"
 <pgsql-hackers@lists.postgresql.org>
References: 
 <CA+TgmoZ-Jh1T6QyWoCODMVQdhTUPYkaZjWztzP1En4=ZHoKPzw@mail.gmail.com>
 <CAKZiRmxtJAFG7e1+Vs9B8ngON=AOzJbuws+1ZeH4LsbJh5AzoQ@mail.gmail.com>
 <CA+TgmoY9Ne_Sh10u6LSPc3wvOQPLp3kF9nBp3nqJEG2JGF2QiA@mail.gmail.com>
 <CA+Tgmoa57S6mP=aTOXH2-gDAL4TMO1WbGgrHSg0s6J4zUH=04g@mail.gmail.com>
 <DEB1SAGVDX7S.JMV96X4R0RCS@gmail.com>
 <CA+Tgmoaf__2B0BUL+vrg28P+3buX=Ti-kybqkHiLTtFrrCfzuA@mail.gmail.com>
 <CA+TgmoYpcLNOuypOTdgCSLW7FuA=t6BtB3meTARHX2-Dj_81xQ@mail.gmail.com>
In-Reply-To: 
 <CA+TgmoYpcLNOuypOTdgCSLW7FuA=t6BtB3meTARHX2-Dj_81xQ@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/DEFNP3WX7BLX.1X7DS1P9X6JIF%40nmfay.com>
Precedence: bulk

--d0bcf6a84f8342a99f6f8896a62b8fcfa9cb23e115dda24b65762034b0e3
Content-Type: multipart/alternative;
 boundary=f6fccbbacc5e6c91aa144a91b230cbc2e3a865d812074e8dc02de035bfd4

--f6fccbbacc5e6c91aa144a91b230cbc2e3a865d812074e8dc02de035bfd4
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Content-Type: text/plain; charset=UTF-8

On Tue Nov 18, 2025 at 11:19 AM EST, Robert Haas wrote:
> Here's v4. This version has some bug fixes and test case changes to
> 0005 and 0006, with the goal of getting CI to pass cleanly (which it
> now does for me, but let's see if cfbot agrees).

Thanks for working on this, Robert! I think the design seems solid (and
very powerful) from a user perspective. I was curious what would happen
with row-level security interactions so I tried it out on a toy example
I put together a while back. I found one case where scan advice fails on
an intentionally naive/bad policy implementation, but I'm not sure why
and it seems like the kind of weird corner case that might be useful to
reason about. See attached for the setup script, then:

set pg_plan_advice.advice =3D 'BITMAP_HEAP_SCAN(item public.item_tags_idx)'=
;
set item_reader.allowed_tags =3D '{alpha,beta}';
set role item_reader;

explain (plan_advice, analyze, verbose, costs, timing)
select * from item
where value ilike 'a%' and tags && array[1];

Seq Scan on public.item  (cost=3D0.00..41777312.00 rows=3D54961 width=3D67)=
 (actual time=3D2.947..8603.333 rows=3D6762.00 loops=3D1)
 Disabled: true
 Output: item.id, item.value, item.tags
 Filter: (EXISTS(SubPlan exists_1) AND (item.value ~~* 'a%'::text) AND (ite=
m.tags && '{1}'::integer[]))
 Rows Removed by Filter: 993238
 Buffers: shared hit=3D1012312
 SubPlan exists_1
   ->  Seq Scan on public.tag  (cost=3D0.00..41.75 rows=3D1 width=3D0) (act=
ual time=3D0.008..0.008 rows=3D0.21 loops=3D1000000)
         Filter: ((current_setting('item_reader.allowed_tags'::text) IS NOT=
 NULL) AND ((current_setting('item_reader.allowed_tags'::text))::text[] @> =
ARRAY[tag.name]) AND (item.tags @> ARRAY[tag.id]))
         Rows Removed by Filter: 18
         Buffers: shared hit=3D1000000
Planning Time: 1.168 ms
Supplied Plan Advice:
 BITMAP_HEAP_SCAN(item public.item_tags_idx) /* matched, failed */
Generated Plan Advice:
 SEQ_SCAN(item tag@exists_1)
 NO_GATHER(item tag@exists_1)
Execution Time: 8603.615 ms

Since the policies don't contain any execution boundaries, all the quals
should be going into a single bucket for planning if I understand the
process correctly. The bitmap heap scan should be a candidate given the
`tags &&` predicate (and indeed if I switch to a privileged role, the
advice matches successfully without any policies in the mix), but gdb
shows the walker bouncing out of pgpa_walker_contains_scan without any
candidate scans for the BITMAP_HEAP_SCAN strategy.

I do want to avoid getting bikesheddy about the advice language so I'll
forbear from syntax discussion, but one design thought with lower-level
implications did occur to me as I was playing with this: it might be
useful in some situations to influence the planner _away_ from known
worse paths while leaving it room to decide on the best other option. I
think the work you did in path management should make this pretty
straightforward for join and scan strategies, since it looks like you've
basically made the enable_* gucs a runtime-configurable bitmask (which
seems like a perfectly reasonable approach to my "have done some source
diving but not an internals hacker" eyes), and could disable one as
easily as forcing one.

"Don't use this one index" sounds more fiddly to implement, but also
less valuable since in that case you probably already know which other
index it should be using.

--f6fccbbacc5e6c91aa144a91b230cbc2e3a865d812074e8dc02de035bfd4--

--d0bcf6a84f8342a99f6f8896a62b8fcfa9cb23e115dda24b65762034b0e3
Content-Transfer-Encoding: base64
Content-Disposition: attachment; filename=rls-demo-item-tags.sql
Content-Type: application/sql; name=rls-demo-item-tags.sql

Y3JlYXRlIHRhYmxlIHRhZyAoCiAgaWQgaW50IGdlbmVyYXRlZCBhbHdheXMgYXMgaWRlbnRpdHkg
cHJpbWFyeSBrZXksCiAgbmFtZSB0ZXh0Cik7CgppbnNlcnQgaW50byB0YWcgKG5hbWUpCnNlbGVj
dCAqIGZyb20gdW5uZXN0KGFycmF5WwogICAgJ2FscGhhJywgJ2JldGEnLCAnZ2FtbWEnLCAnZGVs
dGEnLCAnZXBzaWxvbicsICd6ZXRhJywgJ2V0YScsICdpb3RhJywgJ2thcHBhJywgJ2xhbWJkYScs
ICdtdScsCiAgICAnbnUnLCAneGknLCAnb21pY3JvbicsICdwaScsICdyaG8nLCAnc2lnbWEnLCAn
dGF1JywgJ3Vwc2lsb24nLCAncGhpJywgJ2NoaScsICdwc2knLCAnb21lZ2EnCl0pOwoKY3JlYXRl
IHRhYmxlIGl0ZW0gKAogIGlkIGludCBnZW5lcmF0ZWQgYWx3YXlzIGFzIGlkZW50aXR5IHByaW1h
cnkga2V5LAogIHZhbHVlIHRleHQsCiAgdGFncyBpbnRbXQopOwoKaW5zZXJ0IGludG8gaXRlbSAo
dmFsdWUsIHRhZ3MpCnNlbGVjdAogIG1kNShyYW5kb20oKTo6dGV4dCksCiAgYXJyYXlfc2FtcGxl
KChzZWxlY3QgYXJyYXlfYWdnKGlkKSBmcm9tIHRhZyksIHRydW5jKHJhbmRvbSgpICogNCk6Omlu
dCArIDEpCmZyb20gZ2VuZXJhdGVfc2VyaWVzKDEsIDEwMDAwMDApOwoKY3JlYXRlIGluZGV4IG9u
IGl0ZW0gdXNpbmcgZ2luICh0YWdzKTsKCmFsdGVyIHRhYmxlIHRhZyBlbmFibGUgcm93IGxldmVs
IHNlY3VyaXR5OwphbHRlciB0YWJsZSBpdGVtIGVuYWJsZSByb3cgbGV2ZWwgc2VjdXJpdHk7Cgpj
cmVhdGUgcm9sZSBpdGVtX3JlYWRlcjsKZ3JhbnQgc2VsZWN0IG9uIGl0ZW0gdG8gaXRlbV9yZWFk
ZXI7CmdyYW50IHNlbGVjdCBvbiB0YWcgdG8gaXRlbV9yZWFkZXI7CgpjcmVhdGUgcG9saWN5IGl0
ZW1fcmVhZGVyX3RhZ19wb2xpY3kgb24gdGFnCmZvciBzZWxlY3QgdG8gaXRlbV9yZWFkZXIKdXNp
bmcgKAogICAgY3VycmVudF9zZXR0aW5nKCdpdGVtX3JlYWRlci5hbGxvd2VkX3RhZ3MnKSBpcyBu
b3QgbnVsbCBhbmQKICAgIGN1cnJlbnRfc2V0dGluZygnaXRlbV9yZWFkZXIuYWxsb3dlZF90YWdz
Jyk6OnRleHRbXSBAPiBhcnJheVtuYW1lXQopOwoKY3JlYXRlIHBvbGljeSBpdGVtX3JlYWRlcl9p
dGVtX3BvbGljeSBvbiBpdGVtCmZvciBzZWxlY3QgdG8gaXRlbV9yZWFkZXIKdXNpbmcgKAogICAg
ZXhpc3RzICgKICAgICAgICBzZWxlY3QgMSBmcm9tIHRhZwogICAgICAgIHdoZXJlIGl0ZW0udGFn
cyBAPiBhcnJheVt0YWcuaWRdCiAgICApCik7Cg==
--d0bcf6a84f8342a99f6f8896a62b8fcfa9cb23e115dda24b65762034b0e3--