Message-ID: <4BFEE08E.2040804@emolecules.com>
Date: Thu, 27 May 2010 14:13:50 -0700
From: Craig James <craig_james@emolecules.com>
User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.6; en-US;
	rv:1.9.1.9) Gecko/20100317 Thunderbird/3.0.4
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Does FILTER in SEQSCAN short-circuit AND?
References: <hsv49v$312j$1@news.hub.org>
In-Reply-To: <hsv49v$312j$1@news.hub.org>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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