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: <CAGECzQS7-564414-vV+XrPBj=4gHW6mPT8eObjq5=RrEseRcAw@mail.gmail.com>
References: <CAGECzQRqysy0eJMKR5he3gwtLrT87f9u5CQQua6B_XNwMnUtFA@mail.gmail.com> <4091190.1712772398@sss.pgh.pa.us> <CAGECzQS7-564414-vV+XrPBj=4gHW6mPT8eObjq5=RrEseRcAw@mail.gmail.com>
Comments: In-reply-to Jelte Fennema-Nio <postgres@jeltef.nl>
	message dated "Wed, 10 Apr 2024 20:31:57 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <4160331.1712779911.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Wed, 10 Apr 2024 16:11:51 -0400
Message-ID: <4160332.1712779911@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/4160332.1712779911%40sss.pgh.pa.us>
Precedence: bulk

Jelte Fennema-Nio <postgres@jeltef.nl> writes:
> On Wed, 10 Apr 2024 at 20:06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Really?  ISTM this argument is ignoring an optimization the backend
>> has understood for a long time.

> Interesting. I didn't know about that optimization. I can't check
> right now, but probably the COLLATE breaks that optimization.

Not for me.

# explain select * from pg_class where relname ~ '^(foo)$' collate "en_US"=
;
                                         QUERY PLAN                       =
                   =

--------------------------------------------------------------------------=
-------------------
 Index Scan using pg_class_relname_nsp_index on pg_class  (cost=3D0.27..8.=
29 rows=3D1 width=3D263)
   Index Cond: (relname =3D 'foo'::text)
   Filter: (relname ~ '^(foo)$'::text COLLATE "en_US")
(3 rows)

Also, using -E:

# \d foo
/******** QUERY *********/
SELECT c.oid,
  n.nspname,
  c.relname
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid =3D c.relnamespace
WHERE c.relname OPERATOR(pg_catalog.~) '^(foo)$' COLLATE pg_catalog.defaul=
t
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;
/************************/

# explain SELECT c.oid,
  n.nspname,
  c.relname
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid =3D c.relnamespace
WHERE c.relname OPERATOR(pg_catalog.~) '^(foo)$' COLLATE pg_catalog.defaul=
t
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;
                                                QUERY PLAN                =
                                =

--------------------------------------------------------------------------=
--------------------------------
 Sort  (cost=3D9.42..9.42 rows=3D1 width=3D132)
   Sort Key: n.nspname, c.relname
   ->  Nested Loop Left Join  (cost=3D0.27..9.41 rows=3D1 width=3D132)
         Join Filter: (n.oid =3D c.relnamespace)
         ->  Index Scan using pg_class_relname_nsp_index on pg_class c  (c=
ost=3D0.27..8.32 rows=3D1 width=3D72)
               Index Cond: (relname =3D 'foo'::text)
               Filter: ((relname ~ '^(foo)$'::text) AND pg_table_is_visibl=
e(oid))
         ->  Seq Scan on pg_namespace n  (cost=3D0.00..1.04 rows=3D4 width=
=3D68)
(8 rows)


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.

			regards, tom lane