Skip site navigation (1) Skip section navigation (2)

Re: Does FILTER in SEQSCAN short-circuit AND?

From: Craig James <craig_james(at)emolecules(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Does FILTER in SEQSCAN short-circuit AND?
Date: 2010-05-27 21:13:50
Message-ID: 4BFEE08E.2040804@emolecules.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

Responses

pgsql-performance by date

Next:From: Thomas KellererDate: 2010-05-27 21:26:31
Subject: Re: Does FILTER in SEQSCAN short-circuit AND?
Previous:From: Kevin GrittnerDate: 2010-05-27 20:27:11
Subject: Re: Does FILTER in SEQSCAN short-circuit AND?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group