Re: slow queries over information schema.tables

Pavel Stehule <pavel.stehule@gmail.com> writes:
> čt 20. 12. 2018 v 0:14 odesílatel Tom Lane <tgl@sss.pgh.pa.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