From: Craig James <craig_james@emolecules.com>
To: pgsql-performance@postgresql.org
Subject: Re: Does FILTER in SEQSCAN short-circuit AND?
Date: Thu, 27 May 2010 14:13:50 -0700
Message-ID: <4BFEE08E.2040804@emolecules.com> (raw)
In-Reply-To: <hsv49v$312j$1@news.hub.org>
References: <hsv49v$312j$1@news.hub.org>
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
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-performance@postgresql.org
Cc: craig_james@emolecules.com
Subject: Re: Does FILTER in SEQSCAN short-circuit AND?
In-Reply-To: <4BFEE08E.2040804@emolecules.com>
* 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