From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jelte Fennema-Nio <postgres@jeltef.nl>
cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: psql: Greatly speed up "\d tablename" when not using regexes
In-reply-to: <CAGECzQRqysy0eJMKR5he3gwtLrT87f9u5CQQua6B_XNwMnUtFA@mail.gmail.com>
References: <CAGECzQRqysy0eJMKR5he3gwtLrT87f9u5CQQua6B_XNwMnUtFA@mail.gmail.com>
Comments: In-reply-to Jelte Fennema-Nio <postgres@jeltef.nl>
	message dated "Wed, 10 Apr 2024 17:50:42 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <4091189.1712772398.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Wed, 10 Apr 2024 14:06:38 -0400
Message-ID: <4091190.1712772398@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/4091190.1712772398%40sss.pgh.pa.us>
Precedence: bulk

Jelte Fennema-Nio <postgres@jeltef.nl> 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=3D# explain select * from pg_class where relname ~ '^foo$';
                                         QUERY PLAN                       =
      =

             =

--------------------------------------------------------------------------=
------
-------------
 Index Scan using pg_class_relname_nsp_index on pg_class  (cost=3D0.28..8.=
30 rows=3D
1 width=3D739)
   Index Cond: (relname =3D 'foo'::text)
   Filter: (relname ~ '^foo$'::text)
(3 rows)

			regards, tom lane