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

On Wed, 10 Apr 2024 at 22:11, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> There may be an argument for psql to do what you suggest,
> but so far it seems like duplicative complication.
>
> 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. The reason is that these underlying shards cluttered the
output of \d and PgAdmin etc. Users also tended to get confused by
them, sometimes badly enough to remove them (and thus requiring
restore from backup).

We have a GUC to turn this filtering off for advanced users:
SET citus.show_shards_for_app_name_prefixes = '*';

If you set that the index is used and the query is fast again. Just
like what is happening for you. Not using the regex search also worked
as a way to trigger an index scan.

I'll think/research a bit tomorrow and try some stuff out to see if
this is fixable in Citus. That would definitely be preferable to me as
it would solve this issue on all Postgres/psql versions that citus
supports.

If I cannot think of a way to address this in Citus, would it be
possible to still consider to merge this patch (assuming comments
explaining that Citus is the reason)? Because this planner issue that
Citus its behaviour introduces is fixed by the change I proposed in my
Patch too (I'm not yet sure how exactly).