Re: slow queries over information schema.tables

čt 20. 12. 2018 v 5:29 odesílatel Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> napsal:

> 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 ...
>

this plan looks great

Pavel

> regards, tom lane
>