public inbox for [email protected]
help / color / mirror / Atom feedFrom: Tom Lane <[email protected]>
To: Jelte Fennema-Nio <[email protected]>
Cc: PostgreSQL-development <[email protected]>
Subject: Re: psql: Greatly speed up "\d tablename" when not using regexes
Date: Wed, 10 Apr 2024 14:06:38 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAGECzQRqysy0eJMKR5he3gwtLrT87f9u5CQQua6B_XNwMnUtFA@mail.gmail.com>
References: <CAGECzQRqysy0eJMKR5he3gwtLrT87f9u5CQQua6B_XNwMnUtFA@mail.gmail.com>
Jelte Fennema-Nio <[email protected]> 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
view thread (2+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: psql: Greatly speed up "\d tablename" when not using regexes
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox