On 22/11/12 04:32, Andres Freund wrote:
On 2012-11-21 10:21:16 -0500, Andrew Dunstan wrote:
On 11/21/2012 09:59 AM, Tom Lane wrote:
Andrew Dunstan and...@dunslane.net writes:
If we're going to do it can we please come up with something more
intuitive and much, much more documented than OFFSET
On 22/11/12 08:42, Andrew Dunstan wrote:
On 11/21/2012 02:30 PM, Gavin Flower wrote:
WITH FENCE foo AS (SELECT ...)
default?
WITHOUT FENCE foo AS (SELECT ...) :-)
Nah!
I prefer this, but it is too specific to 'WITH',
and very unSQL standardish!
Alternatively one of the following
1.
On Tue, Nov 20, 2012 at 02:24:01PM -0600, Merlin Moncure wrote:
On Tue, Nov 20, 2012 at 1:53 PM, Jon Nelson jnelson+pg...@jamponi.net wrote:
As can be seen by the current conversation, not everyone is convinced
that CTEs ought to be an explicit optimization barrier
On Tue, Nov 20, 2012 at
On Thu, Nov 22, 2012 at 7:42 AM, Jeremy Harris j...@wizmail.org wrote:
On 22/11/2012 00:08, Craig Ringer wrote:
WITH
FENCE foo AS (SELECT ...),
bar AS (SELECT ...)
SELECT * FROM bar;
Are we fencing just foo? Or all expressions?
WITH foo AS (FENCED SELECT ...),
bar AS (SELECT
I'd also add ANALYZED/NOT ANALYZED. This should force it behave like
'create table, analyze, select' with statistics used in second query plan.
P.S. defaults can be configurable.
20 лист. 2012 02:22, Gavin Flower gavinflo...@archidevsys.co.nz напис.
On 15/11/12 15:03, Peter Geoghegan wrote:
On 21.11.2012 01:53, Tom Lane wrote:
I think the more interesting question is what cases wouldn't be covered
by such a rule. Typically you need to use OFFSET 0 in situations where
the planner has guessed wrong about costs or rowcounts, and I think
people are likely using WITH for that as well.
On 21 November 2012 13:04, Heikki Linnakangas hlinnakan...@vmware.com wrote:
Yes, I strongly feel that we should. Writing a query using WITH often makes
it more readable. It would be a shame if people have to refrain from using
it, because the planner treats it as an optimization fence.
+1
--
On 11/21/2012 08:04 AM, Heikki Linnakangas wrote:
On 21.11.2012 01:53, Tom Lane wrote:
I think the more interesting question is what cases wouldn't be covered
by such a rule. Typically you need to use OFFSET 0 in situations where
the planner has guessed wrong about costs or rowcounts, and I
Andrew Dunstan and...@dunslane.net writes:
If we're going to do it can we please come up with something more
intuitive and much, much more documented than OFFSET 0? And if/when we
do this we'll need to have big red warnings all over then release notes,
since a lot of people I know will need
On 11/21/2012 09:59 AM, Tom Lane wrote:
Andrew Dunstan and...@dunslane.net writes:
If we're going to do it can we please come up with something more
intuitive and much, much more documented than OFFSET 0? And if/when we
do this we'll need to have big red warnings all over then release notes,
On 2012-11-21 10:21:16 -0500, Andrew Dunstan wrote:
On 11/21/2012 09:59 AM, Tom Lane wrote:
Andrew Dunstan and...@dunslane.net writes:
If we're going to do it can we please come up with something more
intuitive and much, much more documented than OFFSET 0? And if/when we
do this we'll need
On 21 November 2012 15:21, Andrew Dunstan and...@dunslane.net wrote:
And I continue to think that spelling it OFFSET 0 is horribly obscure.
I'm not sure that it's any more obscure than the very idea of an
optimisation fence.
--
Peter Geoghegan http://www.2ndQuadrant.com/
PostgreSQL
On 21.11.2012 17:42, Gavin Flower wrote:
On 22/11/12 04:32, Andres Freund wrote:
On 2012-11-21 10:21:16 -0500, Andrew Dunstan wrote:
I wasn't talking about removing it. My point was that if the
optimization
fence around CTEs is removed a lot of people will need to rework apps
where
they have
On Wed, Nov 21, 2012 at 12:32 PM, Andres Freund and...@anarazel.de wrote:
+1
WITH foo AS (SELECT ...) (barrier=on|off)?
9.3 introduces the syntax, defaulting to on
9.4 switches the default to off.
Why syntax? What about a guc?
collapse_cte_limit?
--
Sent via pgsql-performance mailing
On 2012-11-21 13:16:25 -0300, Claudio Freire wrote:
On Wed, Nov 21, 2012 at 12:32 PM, Andres Freund and...@anarazel.de wrote:
+1
WITH foo AS (SELECT ...) (barrier=on|off)?
9.3 introduces the syntax, defaulting to on
9.4 switches the default to off.
Why syntax? What about a guc?
On Wed, Nov 21, 2012 at 1:24 PM, Andres Freund and...@2ndquadrant.com wrote:
On 2012-11-21 13:16:25 -0300, Claudio Freire wrote:
On Wed, Nov 21, 2012 at 12:32 PM, Andres Freund and...@anarazel.de wrote:
+1
WITH foo AS (SELECT ...) (barrier=on|off)?
9.3 introduces the syntax, defaulting
On 2012-11-21 13:32:45 -0300, Claudio Freire wrote:
On Wed, Nov 21, 2012 at 1:24 PM, Andres Freund and...@2ndquadrant.com wrote:
On 2012-11-21 13:16:25 -0300, Claudio Freire wrote:
On Wed, Nov 21, 2012 at 12:32 PM, Andres Freund and...@anarazel.de wrote:
+1
WITH foo AS (SELECT ...)
On 11/21/2012 11:32 AM, Claudio Freire wrote:
On Wed, Nov 21, 2012 at 1:24 PM, Andres Freund and...@2ndquadrant.com wrote:
On 2012-11-21 13:16:25 -0300, Claudio Freire wrote:
On Wed, Nov 21, 2012 at 12:32 PM, Andres Freund and...@anarazel.de wrote:
+1
WITH foo AS (SELECT ...)
On Wed, Nov 21, 2012 at 1:35 PM, Andrew Dunstan and...@dunslane.net wrote:
Why syntax? What about a guc?
collapse_cte_limit?
Because there are very good reasons to want to current behaviour. A guc
is a global either/or so I don't see it helping much.
set collapse_cte_limit=8;
with blah as
Claudio Freire klaussfre...@gmail.com writes:
collapse_cte_limit?
The join collapse limits address a completely different problem (ie,
explosion of planning time with too many relations), and are pretty much
useless as a model for this. As multiple people told you already,
optimization fences
On 11/21/2012 02:30 PM, Gavin Flower wrote:
WITH FENCE foo AS (SELECT ...)
default?
WITHOUT FENCE foo AS (SELECT ...) :-)
Nah!
I prefer this, but it is too specific to 'WITH',
and very unSQL standardish!
Alternatively one of the following
1. WITH UNFENCED foo AS (SELECT ...)
2. WITH
On 11/22/2012 03:30 AM, Gavin Flower wrote:
On 22/11/12 04:56, Heikki Linnakangas wrote:
On 21.11.2012 17:42, Gavin Flower wrote:
On 22/11/12 04:32, Andres Freund wrote:
On 2012-11-21 10:21:16 -0500, Andrew Dunstan wrote:
I wasn't talking about removing it. My point was that if the
On 11/22/2012 08:38 AM, Gavin Flower wrote:
I suspect most people are blissfully unaware of CTE's being fenced, or
at least not really sure what it means. So I suspect NOT FENCE would
be the better default.
It's also probably more standard, and a better fit with what other DBs do.
Pg would
On Tue, Nov 13, 2012 at 2:57 PM, David Greco
david_gr...@harte-hanks.com wrote:
Have a query using a CTE that is performing very poorly. The equivalent
query against the same data in an Oracle database runs in under 1 second, in
Postgres it takes 2000 seconds.
The
On Tue, Nov 20, 2012 at 12:04 PM, Merlin Moncure mmonc...@gmail.com wrote:
The problem here is very clear. Oracle is optimizing through the CTE.
PostgreSQL does not do this by design -- CTE's are used as a forced
materialization step.
While I love that design (it lets me solve lots of
On Tue, Nov 20, 2012 at 9:10 AM, Claudio Freire klaussfre...@gmail.com wrote:
On Tue, Nov 20, 2012 at 12:04 PM, Merlin Moncure mmonc...@gmail.com wrote:
The problem here is very clear. Oracle is optimizing through the CTE.
PostgreSQL does not do this by design -- CTE's are used as a forced
On Tue, Nov 20, 2012 at 12:23 PM, Merlin Moncure mmonc...@gmail.com wrote:
On Tue, Nov 20, 2012 at 9:10 AM, Claudio Freire klaussfre...@gmail.com
wrote:
On Tue, Nov 20, 2012 at 12:04 PM, Merlin Moncure mmonc...@gmail.com wrote:
The problem here is very clear. Oracle is optimizing through the
On Wed, Nov 14, 2012 at 8:03 PM, Peter Geoghegan pe...@2ndquadrant.com wrote:
On 15 November 2012 01:46, Andrew Dunstan and...@dunslane.net wrote:
It cuts both ways. I have used CTEs a LOT precisely because this behaviour
lets me get better plans. Without that I'll be back to using the offset 0
On Tue, Nov 20, 2012 at 4:22 PM, Merlin Moncure mmonc...@gmail.com wrote:
On Wed, Nov 14, 2012 at 8:03 PM, Peter Geoghegan pe...@2ndquadrant.com
wrote:
On 15 November 2012 01:46, Andrew Dunstan and...@dunslane.net wrote:
It cuts both ways. I have used CTEs a LOT precisely because this
My perspective on this is that CTEs *should* be just like creating a
temporary table and then joining to it, but without the
materialization costs. In that respect, they seem like they should be
like nifty VIEWs. If I wanted the behavior of materialization and then
join, I'd do that explicitly
On Tue, Nov 20, 2012 at 1:53 PM, Jon Nelson jnelson+pg...@jamponi.net wrote:
As can be seen by the current conversation, not everyone is convinced
that CTEs ought to be an explicit optimization barrier
On Tue, Nov 20, 2012 at 1:26 PM, Claudio Freire klaussfre...@gmail.com wrote:
It *could* just
On Tue, Nov 20, 2012 at 5:24 PM, Merlin Moncure mmonc...@gmail.com wrote:
On Tue, Nov 20, 2012 at 1:53 PM, Jon Nelson jnelson+pg...@jamponi.net wrote:
As can be seen by the current conversation, not everyone is convinced
that CTEs ought to be an explicit optimization barrier
On Tue, Nov 20,
Jon Nelson jnelson+pg...@jamponi.net writes:
... Perhaps even including a
small blurb about what an optimization barrier even means (my
understanding is that it merely forces materialization of that part of
the query).
FWIW, it has nothing to do with materialization; it means that we don't
On 11/21/2012 12:06 AM, Claudio Freire wrote:
I meant for postgres to do automatically. Rewriting as a join wouldn't
work as an optimization fence the way we're used to, but pushing
constraints upwards can only help (especially if highly selective).
Because people are now used to using CTEs as
On Tue, Nov 20, 2012 at 8:38 PM, Craig Ringer cr...@2ndquadrant.com wrote:
On 11/21/2012 12:06 AM, Claudio Freire wrote:
I meant for postgres to do automatically. Rewriting as a join wouldn't
work as an optimization fence the way we're used to, but pushing
constraints upwards can only help
Craig Ringer cr...@2ndquadrant.com writes:
On 11/21/2012 12:06 AM, Claudio Freire wrote:
I meant for postgres to do automatically. Rewriting as a join wouldn't
work as an optimization fence the way we're used to, but pushing
constraints upwards can only help (especially if highly selective).
On 15/11/12 15:03, Peter Geoghegan wrote:
On 15 November 2012 01:46, Andrew Dunstan and...@dunslane.net wrote:
It cuts both ways. I have used CTEs a LOT precisely because this behaviour
lets me get better plans. Without that I'll be back to using the offset 0
hack.
Is the OFFSET 0 hack really
On 11/14/2012 10:23 AM, David Greco wrote:
Have a query using a CTE that is performing very poorly. The
equivalent query against the same data in an Oracle database runs in
under 1 second, in Postgres it takes 2000 seconds.
The smp_pkg.get_invoice_charges queries fedexinvoices for some
: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Poor performance using CTE
On 11/14/2012 10:23 AM, David Greco wrote:
Have a query using a CTE that is performing very poorly. The
equivalent query against the same data in an Oracle database runs in
under 1 second, in Postgres it takes
On 11/14/2012 10:56 AM, David Greco wrote:
You're right. I was translating an oracle query , but looks like PG will allow
some syntax that is different. Trying to find entries in fedexinvoices where
smp_pkg.get_invoice_charges(id) returns a record containing charge_name in
('ADDRESS
-Original Message-
From: Andrew Dunstan [mailto:and...@dunslane.net]
Sent: Wednesday, November 14, 2012 11:08 AM
To: David Greco
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Poor performance using CTE
On 11/14/2012 10:56 AM, David Greco wrote:
You're right. I
David Greco david_gr...@harte-hanks.com writes:
Thanks, that did the trick. Though I'm still not clear as to why.
PG treats WITH as an optimization fence --- the WITH query will be
executed pretty much as-is. It may be that Oracle flattens the query
somehow; though if you're using black-box
On 11/15/2012 12:29 AM, Tom Lane wrote:
David Greco david_gr...@harte-hanks.com writes:
Thanks, that did the trick. Though I'm still not clear as to why.
PG treats WITH as an optimization fence --- the WITH query will be
executed pretty much as-is. It may be that Oracle flattens the query
Craig Ringer cr...@2ndquadrant.com writes:
I was looking through the latest spec drafts I have access to and
couldn't find any reference to Pg's optimisation-fence-for-CTEs
behaviour being required by the standard, though I've repeatedly seen it
said that there is such a requirement.
I don't
On 11/14/2012 08:17 PM, Craig Ringer wrote:
On 11/15/2012 12:29 AM, Tom Lane wrote:
David Greco david_gr...@harte-hanks.com writes:
Thanks, that did the trick. Though I'm still not clear as to why.
PG treats WITH as an optimization fence --- the WITH query will be
executed pretty much as-is.
On 15 November 2012 01:46, Andrew Dunstan and...@dunslane.net wrote:
It cuts both ways. I have used CTEs a LOT precisely because this behaviour
lets me get better plans. Without that I'll be back to using the offset 0
hack.
Is the OFFSET 0 hack really so bad? We've been telling people to do
46 matches
Mail list logo
