On Wed, 10 Apr 2024 at 22:11, Tom Lane <t...@sss.pgh.pa.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).
