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

From: Tom Lane <[email-masked]>
Date: Wed, 10 Apr 2024 14:06:38 -0400

Jelte Fennema-Nio <[email-masked]> writes:
> Running "\d tablename" from psql could take multiple seconds when
> running on a system with 100k+ tables. The reason for this was that
> a sequence scan on pg_class takes place, due to regex matching being
> used.

> Regex matching is obviously unnecessary when we're looking for an exact
> match. This checks for this (common) case and starts using plain
> equality in that case.

Really?  ISTM this argument is ignoring an optimization the backend
has understood for a long time.

regression=# explain select * from pg_class where relname ~ '^foo$';
                                         QUERY PLAN                             
             
--------------------------------------------------------------------------------
-------------
 Index Scan using pg_class_relname_nsp_index on pg_class  (cost=0.28..8.30 rows=
1 width=739)
   Index Cond: (relname = 'foo'::text)
   Filter: (relname ~ '^foo$'::text)
(3 rows)

			regards, tom lane