gmb <gmbouwer@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
