pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedDoes FILTER in SEQSCAN short-circuit AND?
6+ messages / 4 participants
[nested] [flat]
* Does FILTER in SEQSCAN short-circuit AND?
@ 2010-05-18 22:28 Carlo Stonebanks <stonec.register@sympatico.ca>
2010-05-27 20:27 ` Re: Does FILTER in SEQSCAN short-circuit AND? Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-05-27 21:13 ` Re: Does FILTER in SEQSCAN short-circuit AND? Craig James <craig_james@emolecules.com>
0 siblings, 2 replies; 6+ messages in thread
From: Carlo Stonebanks @ 2010-05-18 22:28 UTC (permalink / raw)
To: pgsql-performance
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') ?
Thanks!
Carlo
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Does FILTER in SEQSCAN short-circuit AND?
2010-05-18 22:28 Does FILTER in SEQSCAN short-circuit AND? Carlo Stonebanks <stonec.register@sympatico.ca>
@ 2010-05-27 20:27 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
1 sibling, 0 replies; 6+ messages in thread
From: Kevin Grittner @ 2010-05-27 20:27 UTC (permalink / raw)
To: pgsql-performance; Carlo Stonebanks <stonec.register@sympatico.ca>
"Carlo Stonebanks" <stonec.register@sympatico.ca> wrote:
> 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') ?
For that example, I'm pretty sure it will skip the slow function for
rows which fail the first test. A quick test confirmed that for me.
If you create a sufficiently slow function, you shouldn't have much
trouble testing that yourself. :-)
-Kevin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Does FILTER in SEQSCAN short-circuit AND?
2010-05-18 22:28 Does FILTER in SEQSCAN short-circuit AND? Carlo Stonebanks <stonec.register@sympatico.ca>
@ 2010-05-27 21:13 ` Craig James <craig_james@emolecules.com>
2010-05-27 21:26 ` Re: Does FILTER in SEQSCAN short-circuit AND? Thomas Kellerer <spam_eater@gmx.net>
2010-05-27 21:28 ` Re: Does FILTER in SEQSCAN short-circuit AND? Kevin Grittner <Kevin.Grittner@wicourts.gov>
1 sibling, 2 replies; 6+ messages in thread
From: Craig James @ 2010-05-27 21:13 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Does FILTER in SEQSCAN short-circuit AND?
2010-05-18 22:28 Does FILTER in SEQSCAN short-circuit AND? Carlo Stonebanks <stonec.register@sympatico.ca>
2010-05-27 21:13 ` Re: Does FILTER in SEQSCAN short-circuit AND? Craig James <craig_james@emolecules.com>
@ 2010-05-27 21:26 ` Thomas Kellerer <spam_eater@gmx.net>
1 sibling, 0 replies; 6+ messages in thread
From: Thomas Kellerer @ 2010-05-27 21:26 UTC (permalink / raw)
To: pgsql-performance
Craig James wrote on 27.05.2010 23:13:
> It would be nice if Postgres had a way to assign a cost to every
> function.
Isn't that what the COST parameter is intended to be:
http://www.postgresql.org/docs/current/static/sql-createfunction.html
Thomas
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Does FILTER in SEQSCAN short-circuit AND?
2010-05-18 22:28 Does FILTER in SEQSCAN short-circuit AND? Carlo Stonebanks <stonec.register@sympatico.ca>
2010-05-27 21:13 ` Re: Does FILTER in SEQSCAN short-circuit AND? Craig James <craig_james@emolecules.com>
@ 2010-05-27 21:28 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-05-27 21:44 ` Re: Does FILTER in SEQSCAN short-circuit AND? Craig James <craig_james@emolecules.com>
1 sibling, 1 reply; 6+ messages in thread
From: Kevin Grittner @ 2010-05-27 21:28 UTC (permalink / raw)
To: Craig James <craig_james@emolecules.com>; pgsql-performance
Craig James <craig_james@emolecules.com> wrote:
> It would be nice if Postgres had a way to assign a cost to every
> function.
The COST clause of CREATE FUNCTION doesn't do what you want?
http://www.postgresql.org/docs/8.4/interactive/sql-createfunction.html
-Kevin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Does FILTER in SEQSCAN short-circuit AND?
2010-05-18 22:28 Does FILTER in SEQSCAN short-circuit AND? Carlo Stonebanks <stonec.register@sympatico.ca>
2010-05-27 21:13 ` Re: Does FILTER in SEQSCAN short-circuit AND? Craig James <craig_james@emolecules.com>
2010-05-27 21:28 ` Re: Does FILTER in SEQSCAN short-circuit AND? Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2010-05-27 21:44 ` Craig James <craig_james@emolecules.com>
0 siblings, 0 replies; 6+ messages in thread
From: Craig James @ 2010-05-27 21:44 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: pgsql-performance
On 5/27/10 2:28 PM, Kevin Grittner wrote:
> Craig James<craig_james@emolecules.com> wrote:
>
>> It would be nice if Postgres had a way to assign a cost to every
>> function.
>
> The COST clause of CREATE FUNCTION doesn't do what you want?
>
> http://www.postgresql.org/docs/8.4/interactive/sql-createfunction.html
Cool ... I must have missed it when this feature was added. Nice!
Craig
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2010-05-27 21:44 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-05-18 22:28 Does FILTER in SEQSCAN short-circuit AND? Carlo Stonebanks <stonec.register@sympatico.ca>
2010-05-27 20:27 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-05-27 21:13 ` Craig James <craig_james@emolecules.com>
2010-05-27 21:26 ` Thomas Kellerer <spam_eater@gmx.net>
2010-05-27 21:28 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-05-27 21:44 ` Craig James <craig_james@emolecules.com>
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