On 5/18/10 3:28 PM, Carlo Stonebanks wrote:
Sample code:
SELECT *
FROM MyTable
WHERE foo = 'bar' AND MySlowFunc('foo') = 'bar'
Let's say this required a SEQSCAN because there were no indexes to
support column foo. For every row where foo <> 'bar' would the filter on
the SEQSCAN short-circuit the AND return false right away, or would it
still execute MySlowFunc('foo') ?
I asked a similar question a few years back, and the answer is that the planner
just makes a guess and applies it to all functions. It has no idea whether
your function is super fast or incredibly slow, they're all assigned the same
cost.
In this fairly simple case, the planner might reasonably guess that "foo = 'bar'" will
always be faster than "AnyFunc(foo) = 'bar'". But for real queries, that might not be
the case.
In my case, I have a function that is so slow that it ALWAYS is good to avoid
it. Unfortunately, there's no way to explain that to Postgres, so I have to
use other tricks to force the planner not to use it.
select * from
(select * from MyTable where foo = 'bar' offset 0)
where MySlowFunc(foo) = 'bar';
The "offset 0" prevents the planner from collapsing this query back into your
original syntax. It will only apply MySlowFunc() to rows where you already know that foo
= 'bar'.
It would be nice if Postgres had a way to assign a cost to every function.
Until then, you have to use convoluted SQL if you have a really slow function.
Craig
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
