Merlin Moncure wrote:
> Kevin Grittner wrote:
>> Merlin Moncure wrote:
>>
>>> Hm, I bet it's possible (although probably not easy) to deduce
>>> volatility from the function body...maybe through the validator.
>>> If you could do that (perhaps warning in cases where you can't),
>>> then the perfo
On Thu, Sep 20, 2012 at 9:24 AM, Kevin Grittner
wrote:
> Merlin Moncure wrote:
>
>> Hm, I bet it's possible (although probably not easy) to deduce
>> volatility from the function body...maybe through the validator.
>> If you could do that (perhaps warning in cases where you can't),
>> then the pe
On Wed, Sep 19, 2012 at 2:39 PM, Kevin Grittner
wrote:
> Tom Lane wrote:
>> Robert Haas writes:
>>> It still seems like awfully weird behavior.
>>
>> Why? The WHERE condition relates only to the output of the _stats
>> subquery, so why shouldn't it be evaluated there, rather than
>> after the j
Tom Lane wrote:
> florian.schoppm...@emc.com (Florian Schoppmann) writes:
> > [VOLATILE function in WHERE clause *does* get optimized]
>
> I can't get excited about this. Any time you put a volatile function
> into WHERE, you're playing with fire. The docs warn against it:
> http://www.postgre
> Lane'
> Subject: RE: [HACKERS] Invalid optimization of VOLATILE function in WHERE
> clause?
>
> "David Johnston" wrote:
>
> > VOLATILE: "A Volatile function used in an ORDER BY or WHERE clause
> > without referencing any columns from the query itself (i.
"David Johnston" wrote:
> VOLATILE: "A Volatile function used in an ORDER BY or WHERE clause
> without referencing any columns from the query itself (i.e., no
> parameters or all constants) will be evaluated a single time and
> the result treated as a constant (i.e., all rows will have
> identic
> -Original Message-
> >> | VOLATILE indicates that the function value can change even within a
> >> | single table scan, so no optimizations can be made.
> >> | Relatively few database functions are volatile in this sense; some
> >> | examples are random(), [...]
>
> > What are the argu
"David Johnston" wrote:
>> | VOLATILE indicates that the function value can change even
>> | within a single table scan, so no optimizations can be made.
>> | Relatively few database functions are volatile in this sense;
>> | some examples are random(), [...]
> What are the arguments against a
> -Original Message-
> There really needs to be some way to specify that when an expression is
> evaluated for each row in a set, a function used within that expression is
not
> optimized away for some rows. Fortunately we have a way:
>
> http://www.postgresql.org/docs/9.2/interactive/sql
"Kevin Grittner" wrote:
> There is a workaround, if you don't mind ugly:
Or, better:
WITH source AS (
SELECT i, random() AS r FROM generate_series(1,10) AS i
)
SELECT
i
FROM
source, (
SELECT
count(*) AS _n
FROM source
) AS _stats
WHERE
r < 5::DOU
"k...@rice.edu" wrote:
> On Wed, Sep 19, 2012 at 02:39:12PM -0500, Kevin Grittner wrote:
>> In another thread, Tom Lane wrote:
>>> 2. Apply the WHERE condition to each row from 1, and drop rows
>>> that don't pass it.
>>
>> People expect that the results will be consistent with this
>> model,
On Wed, Sep 19, 2012 at 02:39:12PM -0500, Kevin Grittner wrote:
> Tom Lane wrote:
> > Robert Haas writes:
> >> It still seems like awfully weird behavior.
> >
> > Why? The WHERE condition relates only to the output of the _stats
> > subquery, so why shouldn't it be evaluated there, rather than
Tom Lane wrote:
> Robert Haas writes:
>> It still seems like awfully weird behavior.
>
> Why? The WHERE condition relates only to the output of the _stats
> subquery, so why shouldn't it be evaluated there, rather than
> after the join?
In another thread, Tom Lane wrote:
> It's easier to unde
On Wed, Sep 19, 2012 at 1:26 PM, Tom Lane wrote:
> Robert Haas writes:
>> It still seems like awfully weird behavior.
>
> Why? The WHERE condition relates only to the output of the _stats
> subquery, so why shouldn't it be evaluated there, rather than after
> the join?
Because my mental model (
Robert Haas writes:
> It still seems like awfully weird behavior.
Why? The WHERE condition relates only to the output of the _stats
subquery, so why shouldn't it be evaluated there, rather than after
the join?
In a green field I might agree that we should de-optimize such cases,
but the problem
On Wed, Sep 19, 2012 at 12:34 PM, Tom Lane wrote:
> Robert Haas writes:
>> On Wed, Sep 19, 2012 at 10:30 AM, Tom Lane wrote:
>>> To do what you want, I'd suggest wrapping the join into a sub-select
>>> with an "OFFSET 0" clause, which will serve as an optimization fence
>>> that prevents the ran
Robert Haas writes:
> On Wed, Sep 19, 2012 at 10:30 AM, Tom Lane wrote:
>> To do what you want, I'd suggest wrapping the join into a sub-select
>> with an "OFFSET 0" clause, which will serve as an optimization fence
>> that prevents the random() call from being pushed down.
> You've repeatedly o
On Wed, Sep 19, 2012 at 10:30 AM, Tom Lane wrote:
> florian.schoppm...@emc.com (Florian Schoppmann) writes:
>> In PostgreSQL 9.1 and 9.2 (possibly also in earlier versions), the query
>
>> --8<--
>> WITH source AS (
>> SELECT i FROM generate_series(1,10) AS i
>> )
>> SELECT
>> i
>> FROM
>>
On Wed, Sep 19, 2012 at 9:30 AM, Tom Lane wrote:
> florian.schoppm...@emc.com (Florian Schoppmann) writes:
>> In PostgreSQL 9.1 and 9.2 (possibly also in earlier versions), the query
>
>> --8<--
>> WITH source AS (
>> SELECT i FROM generate_series(1,10) AS i
>> )
>> SELECT
>> i
>> FROM
>>
florian.schoppm...@emc.com (Florian Schoppmann) writes:
> In PostgreSQL 9.1 and 9.2 (possibly also in earlier versions), the query
> --8<--
> WITH source AS (
> SELECT i FROM generate_series(1,10) AS i
> )
> SELECT
> i
> FROM
> source, (
> SELECT
> count(*) AS _n
>
Hi all,
In PostgreSQL 9.1 and 9.2 (possibly also in earlier versions), the query
--8<--
WITH source AS (
SELECT i FROM generate_series(1,10) AS i
)
SELECT
i
FROM
source, (
SELECT
count(*) AS _n
FROM source
) AS _stats
WHERE
random() < 5::DOUBLE PREC
21 matches
Mail list logo
