From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Jelte Fennema-Nio <postgres(at)jeltef(dot)nl> |
Cc: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: psql: Greatly speed up "\d tablename" when not using regexes |
Date: | 2024-04-10 20:11:51 |
Message-ID: | 4160332.1712779911@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Jelte Fennema-Nio <postgres(at)jeltef(dot)nl> writes:
> On Wed, 10 Apr 2024 at 20:06, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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=0.27..8.29 rows=1 width=263)
Index Cond: (relname = '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 = c.relnamespace
WHERE c.relname OPERATOR(pg_catalog.~) '^(foo)$' COLLATE pg_catalog.default
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 = c.relnamespace
WHERE c.relname OPERATOR(pg_catalog.~) '^(foo)$' COLLATE pg_catalog.default
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;
QUERY PLAN
----------------------------------------------------------------------------------------------------------
Sort (cost=9.42..9.42 rows=1 width=132)
Sort Key: n.nspname, c.relname
-> Nested Loop Left Join (cost=0.27..9.41 rows=1 width=132)
Join Filter: (n.oid = c.relnamespace)
-> Index Scan using pg_class_relname_nsp_index on pg_class c (cost=0.27..8.32 rows=1 width=72)
Index Cond: (relname = 'foo'::text)
Filter: ((relname ~ '^(foo)$'::text) AND pg_table_is_visible(oid))
-> Seq Scan on pg_namespace n (cost=0.00..1.04 rows=4 width=68)
(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
From | Date | Subject | |
---|---|---|---|
Next Message | Dmitry Dolgov | 2024-04-10 20:15:27 | Re: broken JIT support on Fedora 40 |
Previous Message | Tom Lane | 2024-04-10 20:05:21 | Re: Issue with the PRNG used by Postgres |