Re: slow queries over information schema.tables

Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
> čt 20. 12. 2018 v 0:14 odesílatel Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> napsal:
>> After my last few commits, the only issue that's left here is the
>> cast-to-varchar implied by casting to sql_identifier. Upthread
>> I showed a possible planner hack to get rid of that, and we could
>> still solve it that way so far as allowing indexscans on catalogs
>> is concerned. However, I wonder what people would think of a
>> more aggressive approach, viz:
>> -CREATE DOMAIN sql_identifier AS character varying COLLATE "C";
>> +CREATE DOMAIN sql_identifier AS name;

> The very common will be compare with text type - some like
> SELECT * FROM information_schema.tables WHERE table_name =
> lower('somename');

Yeah, that's not really an issue. After applying the above one-liner
to HEAD, I get plans like this:

regression=# explain SELECT * FROM information_schema.tables WHERE table_name =
lower('somename');
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=8.76..18.60 rows=1 width=608)
-> Hash Join (cost=8.34..10.07 rows=1 width=141)
Hash Cond: (nc.oid = c.relnamespace)
-> Seq Scan on pg_namespace nc (cost=0.00..1.62 rows=33 width=68)
Filter: (NOT pg_is_other_temp_schema(oid))
-> Hash (cost=8.33..8.33 rows=1 width=77)
-> Index Scan using pg_class_relname_nsp_index on pg_class c (cost=0.28..8.33 rows=1 width=77)
Index Cond: ((relname)::name = 'somename'::text)
Filter: ((relkind = ANY ('{r,v,f,p}'::"char"[])) AND (pg_has_role(relowner, 'USAGE'::text) OR has_table_privilege(oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text) OR has_any_column_privilege(oid, 'SELECT, INSERT, UPDATE, REFERENCES'::text)))
-> Nested Loop (cost=0.42..8.46 rows=1 width=132)
-> Index Scan using pg_type_oid_index on pg_type t (cost=0.28..8.29 rows=1 width=72)
Index Cond: (c.reloftype = oid)
-> Index Scan using pg_namespace_oid_index on pg_namespace nt (cost=0.14..0.16 rows=1 width=68)
Index Cond: (oid = t.typnamespace)
(14 rows)

You could surely argue about whether this is too complicated, but it's not
the planner's fault that we've got so many conditions here ...

regards, tom lane