From: Tom Lane <tgl@sss.pgh.pa.us>
To: Richard Ray <rray@mstc.state.ms.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: Query is fast and function is slow
Date: Thu, 07 Dec 2006 17:59:56 -0500
Message-ID: <21499.1165532396@sss.pgh.pa.us> (raw)
In-Reply-To: <Pine.LNX.4.64.0612071100530.16909@rray.drdc.mstc.ms.gov>
References: <Pine.LNX.4.64.0612060934410.15200@rray.drdc.mstc.ms.gov>
<200612071053.01287.mlists@rp-online.de>
<Pine.LNX.4.64.0612070826040.16909@rray.drdc.mstc.ms.gov>
<16166.1165509626@sss.pgh.pa.us>
<Pine.LNX.4.64.0612071100530.16909@rray.drdc.mstc.ms.gov>
Richard Ray <rray@mstc.state.ms.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, rray@mstc.state.ms.us
Subject: Re: Query is fast and function is slow
In-Reply-To: <21499.1165532396@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox