Merlin Moncure wrote:
Kevin Grittner kevin.gritt...@wicourts.gov wrote:
Merlin Moncure mmonc...@gmail.com 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
On Wed, Sep 19, 2012 at 2:39 PM, Kevin Grittner
kevin.gritt...@wicourts.gov wrote:
Tom Lane t...@sss.pgh.pa.us wrote:
Robert Haas robertmh...@gmail.com writes:
It still seems like awfully weird behavior.
Why? The WHERE condition relates only to the output of the _stats
subquery, so why
On Thu, Sep 20, 2012 at 9:24 AM, Kevin Grittner
kevin.gritt...@wicourts.gov wrote:
Merlin Moncure mmonc...@gmail.com 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
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
FROM
On Wed, Sep 19, 2012 at 9:30 AM, Tom Lane t...@sss.pgh.pa.us 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 10:30 AM, Tom Lane t...@sss.pgh.pa.us 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
Robert Haas robertmh...@gmail.com writes:
On Wed, Sep 19, 2012 at 10:30 AM, Tom Lane t...@sss.pgh.pa.us 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
On Wed, Sep 19, 2012 at 12:34 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Robert Haas robertmh...@gmail.com writes:
On Wed, Sep 19, 2012 at 10:30 AM, Tom Lane t...@sss.pgh.pa.us 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
Robert Haas robertmh...@gmail.com 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
On Wed, Sep 19, 2012 at 1:26 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Robert Haas robertmh...@gmail.com 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
Tom Lane t...@sss.pgh.pa.us wrote:
Robert Haas robertmh...@gmail.com 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
On Wed, Sep 19, 2012 at 02:39:12PM -0500, Kevin Grittner wrote:
Tom Lane t...@sss.pgh.pa.us wrote:
Robert Haas robertmh...@gmail.com 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
k...@rice.edu k...@rice.edu wrote:
On Wed, Sep 19, 2012 at 02:39:12PM -0500, Kevin Grittner wrote:
In another thread, Tom Lane t...@sss.pgh.pa.us 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
Kevin Grittner kevin.gritt...@wicourts.gov 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
-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:
David Johnston pol...@yahoo.com 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
-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 arguments against
David Johnston pol...@yahoo.com 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
-Original Message-
From: Kevin Grittner [mailto:kevin.gritt...@wicourts.gov]
Sent: Wednesday, September 19, 2012 5:51 PM
To: k...@rice.edu; David Johnston
Cc: 'Florian Schoppmann'; 'Robert Haas'; pgsql-hackers@postgresql.org;
'Tom
Lane'
Subject: RE: [HACKERS] Invalid optimization
Tom Lane t...@sss.pgh.pa.us 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:
20 matches
Mail list logo