From: Tom Lane <tgl@sss.pgh.pa.us>
To: Pavel Stehule <pavel.stehule@gmail.com>
cc: PostgreSQL Hackers <pgsql-hackers@postgresql.org>,
        Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
Subject: Re: slow queries over information schema.tables
In-reply-to: 
 <CAFj8pRDrfRR8MNxPTuPnN27FUcNeKwiPgiFttzt5K_XVWKOH7g@mail.gmail.com>
References: 
 <CAFj8pRBUCX4LZ2rA2BbEkdD6NN59mgx+BLo1gO08Wod4RLtcTg@mail.gmail.com>
 <5978.1544030694@sss.pgh.pa.us> <9288.1545261277@sss.pgh.pa.us>
 <CAFj8pRDrfRR8MNxPTuPnN27FUcNeKwiPgiFttzt5K_XVWKOH7g@mail.gmail.com>
Comments: In-reply-to Pavel Stehule <pavel.stehule@gmail.com>
	message dated "Thu, 20 Dec 2018 04:31:59 +0100"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <21661.1545280175.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Wed, 19 Dec 2018 23:29:35 -0500
Message-ID: <21662.1545280175@sss.pgh.pa.us>
Precedence: bulk

Pavel Stehule <pavel.stehule@gmail.com> writes:
> =C4=8Dt 20. 12. 2018 v 0:14 odes=C3=ADlatel 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 =3D
> lower('somename');

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

regression=3D# explain SELECT * FROM information_schema.tables WHERE table=
_name =3D
lower('somename');
                                                                          =
                                                                  QUERY PL=
AN                                                                        =
                                                                    =

--------------------------------------------------------------------------=
--------------------------------------------------------------------------=
--------------------------------------------------------------------------=
--------------------------------------------------------------------
 Nested Loop Left Join  (cost=3D8.76..18.60 rows=3D1 width=3D608)
   ->  Hash Join  (cost=3D8.34..10.07 rows=3D1 width=3D141)
         Hash Cond: (nc.oid =3D c.relnamespace)
         ->  Seq Scan on pg_namespace nc  (cost=3D0.00..1.62 rows=3D33 wid=
th=3D68)
               Filter: (NOT pg_is_other_temp_schema(oid))
         ->  Hash  (cost=3D8.33..8.33 rows=3D1 width=3D77)
               ->  Index Scan using pg_class_relname_nsp_index on pg_class=
 c  (cost=3D0.28..8.33 rows=3D1 width=3D77)
                     Index Cond: ((relname)::name =3D 'somename'::text)
                     Filter: ((relkind =3D ANY ('{r,v,f,p}'::"char"[])) AN=
D (pg_has_role(relowner, 'USAGE'::text) OR has_table_privilege(oid, 'SELEC=
T, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text) OR has_an=
y_column_privilege(oid, 'SELECT, INSERT, UPDATE, REFERENCES'::text)))
   ->  Nested Loop  (cost=3D0.42..8.46 rows=3D1 width=3D132)
         ->  Index Scan using pg_type_oid_index on pg_type t  (cost=3D0.28=
..8.29 rows=3D1 width=3D72)
               Index Cond: (c.reloftype =3D oid)
         ->  Index Scan using pg_namespace_oid_index on pg_namespace nt  (=
cost=3D0.14..0.16 rows=3D1 width=3D68)
               Index Cond: (oid =3D 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