Re: Query is fast and function is slow

Richard Ray <rray(at)mstc(dot)state(dot)ms(dot)us> writes:
> Changing both parameters to char(9) and name fixed the problem
> It appears to be using the index
> If time allows could you explain this a bit

EXPLAIN will show you what's going on:

regression=# create table foo (f1 char(9) unique);
NOTICE: CREATE TABLE / UNIQUE will create implicit index "foo_f1_key" for table "foo"
CREATE TABLE
regression=# explain select * from foo where f1 = 'bar';
QUERY PLAN
-----------------------------------------------------------------------
Index Scan using foo_f1_key on foo (cost=0.00..8.02 rows=1 width=13)
Index Cond: (f1 = 'bar'::bpchar)
(2 rows)

regression=# explain select * from foo where f1 = 'bar'::text;
QUERY PLAN
-----------------------------------------------------
Seq Scan on foo (cost=0.00..35.95 rows=9 width=13)
Filter: ((f1)::text = 'bar'::text)
(2 rows)

The second case is unable to use the index because the query is not
really interrogating the value of f1, but the value of CAST(f1 AS text),
and that's not what's indexed. This is not just an academic point,
because the semantics of comparison for char(n) and text are actually
different --- text is sensitive to trailing whitespace, char(n) isn't.
So if we ignored the distinction and tried to use the index anyway,
we'd probably get wrong answers.

The reason the handwritten query comes out OK is that you've got an
untyped literal constant, and the heuristic the parser likes to use
for resolving the type of such a literal is "make it the same type
as whatever it's being compared to". So 'bar' is assumed to be char(n)
and all is well. In your function, though, the parameter is
specifically declared to be text, so you wrote a char(n) vs text
comparison, and that's resolved to mean "promote the char(n) to text
and do a text comparison". Which is exactly what we can see it doing
in my second example above.

Same problem with the other thing: pg_user.usename is type name, not
type text.

regards, tom lane