Re: psql: Greatly speed up "\d tablename" when not using regexes

Jelte Fennema-Nio <postgres(at)jeltef(dot)nl> writes:
> On Wed, 10 Apr 2024 at 22:11, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> If there's a case you can demonstrate where "\d foo" doesn't optimize
>> into an indexscan, we should look into exactly why that's happening,
>> because I think the cause must be more subtle than this.

> Hmm, okay so I took a closer look and you're completely right: It's
> quite a lot more subtle than I initially thought. The query from "\d
> foo" is fast as long as you don't have Citus installed. It turns out
> that Citus breaks this regex index search optimization somehow by
> adding "NOT relation_is_a_known_shard(c.oid)" to the securityQuals of
> the rangeTableEntry for pg_class in its planner hook. Citus does this
> to filter out the underlying shards of a table for every query on
> pg_class.

Huh. Okay, but then why does it work for a simple comparison?

Actually, I bet I know why: texteq is marked leakproof which lets
it drop below a security qual, while regex matches aren't leakproof.

Is it really necessary for Citus' filter to be a security qual rather
than a plain ol' filter condition?

The direction I'd be inclined to think about if this can't be fixed
inside Citus is whether we can generate the derived indexqual
condition despite the regex being backed up behind a security qual.
That is, as long as the derived condition is leakproof, there's no
reason not to let it go before the security qual. We're probably
failing to consider generating derived quals for anything that isn't
qualified to become an indexqual, and this example shows that that's
leaving money on the table.

regards, tom lane