Re: [GENERAL] optimize pg_tables query ( text vs varchar ) ...why ?

gmb <gmbouwer@gmail.com> writes:
> CREATE FUNCTION tableexists( s TEXT , t TEXT ) returns boolean as
> $$
>   SELECT count(tablename) = 1 FROM pg_tables WHERE schemaname=$1 and
> tablename=$2;
> $$
> language sql

> When change the params of above function to VARCHAR (instead of TEXT),
> performance improved dramatically.
> We then changed params to NAME ( as per pg_tables column type ) , but the
> performance stayed more or less the same.

> Can somebody explain this to me ?

The parser has two plausible choices for interpreting the "=" operators
in your WHERE clause: they could mean the text = text operator, or the
name = name operator.  (Type varchar has no operators of its own.)

When the presented situation is name = text, the parser will choose
the text = text operator because text is a preferred type.  When the
presented situation is name = varchar or name = name, it will choose
the name = name operator due to being a closer match.  See
https://www.postgresql.org/docs/current/static/typeconv.html
So you end up with either something like "schemaname::text = param"
or "schemaname = param::name".

After that, the planner has to implement the query, and the problem
is that the available indexes are on "schemaname" not "schemaname::text",
and they can only use the name = name operator anyway.  So you're
getting either a plan like

regression=# explain SELECT count(tablename) = 1 FROM pg_tables WHERE schemaname='foo'::text and tablename='bar'::text;
                                           QUERY PLAN
-------------------------------------------------------------------------------------------------
 Aggregate  (cost=116.23..116.24 rows=1 width=1)
   ->  Nested Loop  (cost=0.00..116.22 rows=1 width=64)
         Join Filter: (c.relnamespace = n.oid)
         ->  Seq Scan on pg_namespace n  (cost=0.00..1.72 rows=1 width=4)
               Filter: ((nspname)::text = 'foo'::text)
         ->  Seq Scan on pg_class c  (cost=0.00..114.48 rows=2 width=72)
               Filter: ((relkind = ANY ('{r,p}'::"char"[])) AND ((relname)::text = 'bar'::text))
(7 rows)

or one like

regression=# explain SELECT count(tablename) = 1 FROM pg_tables WHERE schemaname='foo'::name and tablename='bar'::name;
                                                QUERY PLAN
----------------------------------------------------------------------------------------------------------
 Aggregate  (cost=9.91..9.92 rows=1 width=1)
   ->  Nested Loop  (cost=0.28..9.91 rows=1 width=64)
         Join Filter: (c.relnamespace = n.oid)
         ->  Index Scan using pg_class_relname_nsp_index on pg_class c  (cost=0.28..8.30 rows=1 width=72)
               Index Cond: (relname = 'bar'::name)
               Filter: (relkind = ANY ('{r,p}'::"char"[]))
         ->  Seq Scan on pg_namespace n  (cost=0.00..1.60 rows=1 width=4)
               Filter: (nspname = 'foo'::name)
(8 rows)

You don't generally have to worry about this when you're writing queries
with simple literal comparison values, because the parser will interpret
untyped literals as having the appropriate type automatically.  But in a
function, those parameters already have types, and they might not be the
most desirable ones for the purpose.

Personally I'd have left the function parameters as text and inserted
explicit coercions:

  SELECT count(tablename) = 1 FROM pg_tables
  WHERE schemaname = $1::name and tablename = $2::name;

            regards, tom lane