On Tue, Nov 20, 2012 at 02:24:01PM -0600, Merlin Moncure wrote:
> On Tue, Nov 20, 2012 at 1:53 PM, Jon Nelson 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 Frei
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
optimization
fence around CTEs is removed a lot of peop
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. WIT
On 22/11/12 13:08, Craig Ringer wrote:
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
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 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
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" напис.
> On 15/11/12 15:03, Peter Geoghegan wrote:
>
>> On 15 November 2012 01:46
On Thu, Nov 22, 2012 at 7:42 AM, Jeremy Harris 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 (SEL
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 ...),
SELECT ... ;
--
Jeremy
--
Sent via pgsql-performance mailing list
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 s
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
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 NO
Claudio Freire 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 are typically wanted fo
On Wed, Nov 21, 2012 at 1:35 PM, Andrew Dunstan 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
On 11/21/2012 11:32 AM, Claudio Freire wrote:
On Wed, Nov 21, 2012 at 1:24 PM, Andres Freund wrote:
On 2012-11-21 13:16:25 -0300, Claudio Freire wrote:
On Wed, Nov 21, 2012 at 12:32 PM, Andres Freund wrote:
+1
WITH foo AS (SELECT ...) (barrier=on|off)?
9.3 introduces the syntax, defaultin
On 2012-11-21 13:32:45 -0300, Claudio Freire wrote:
> On Wed, Nov 21, 2012 at 1:24 PM, Andres Freund wrote:
> > On 2012-11-21 13:16:25 -0300, Claudio Freire wrote:
> >> On Wed, Nov 21, 2012 at 12:32 PM, Andres Freund wrote:
> >> > +1
> >> >
> >> > WITH foo AS (SELECT ...) (barrier=on|off)?
> >> >
On Wed, Nov 21, 2012 at 1:24 PM, Andres Freund wrote:
> On 2012-11-21 13:16:25 -0300, Claudio Freire wrote:
>> On Wed, Nov 21, 2012 at 12:32 PM, Andres Freund wrote:
>> > +1
>> >
>> > WITH foo AS (SELECT ...) (barrier=on|off)?
>> >
>> > 9.3 introduces the syntax, defaulting to on
>> > 9.4 switche
On 2012-11-21 13:16:25 -0300, Claudio Freire wrote:
> On Wed, Nov 21, 2012 at 12:32 PM, Andres Freund 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?
>
> coll
On Wed, Nov 21, 2012 at 12:32 PM, Andres Freund 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 list (pgsql-per
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 us
On 21 November 2012 15:21, Andrew Dunstan 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 Development, 24x7 Supp
On 2012-11-21 10:21:16 -0500, Andrew Dunstan wrote:
>
> On 11/21/2012 09:59 AM, Tom Lane wrote:
> >Andrew Dunstan 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 ha
On 11/21/2012 09:59 AM, Tom Lane wrote:
Andrew Dunstan 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 peop
Andrew Dunstan 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 to do some ex
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 th
On 21 November 2012 13:04, Heikki Linnakangas 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
--
Peter Geoghegan
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.
Craig Ringer 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).
> Because peopl
On Tue, Nov 20, 2012 at 8:38 PM, Craig Ringer 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 (especially if hig
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 a
On 11/21/2012 03:53 AM, Jon Nelson wrote:
> 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 material
Jon Nelson 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
push conditions down int
On Tue, Nov 20, 2012 at 5:24 PM, Merlin Moncure wrote:
> On Tue, Nov 20, 2012 at 1:53 PM, Jon Nelson 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
> wro
On Tue, Nov 20, 2012 at 1:53 PM, Jon Nelson 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 wrote:
> It *could* just be a lack of imagination on my part. But if i
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 with
On Tue, Nov 20, 2012 at 4:22 PM, Merlin Moncure wrote:
> On Wed, Nov 14, 2012 at 8:03 PM, Peter Geoghegan
> wrote:
>> On 15 November 2012 01:46, Andrew Dunstan wrote:
>>> It cuts both ways. I have used CTEs a LOT precisely because this behaviour
>>> lets me get better plans. Without that I'll b
On Wed, Nov 14, 2012 at 8:03 PM, Peter Geoghegan wrote:
> On 15 November 2012 01:46, Andrew Dunstan 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" ha
On Tue, Nov 20, 2012 at 12:23 PM, Merlin Moncure wrote:
> On Tue, Nov 20, 2012 at 9:10 AM, Claudio Freire
> wrote:
>> On Tue, Nov 20, 2012 at 12:04 PM, Merlin Moncure wrote:
>>> The problem here is very clear. Oracle is optimizing through the CTE.
>>> PostgreSQL does not do this by design --
On Tue, Nov 20, 2012 at 9:10 AM, Claudio Freire wrote:
> On Tue, Nov 20, 2012 at 12:04 PM, Merlin Moncure 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 l
On Tue, Nov 20, 2012 at 12:04 PM, Merlin Moncure 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 problems for huge
quer
On Tue, Nov 13, 2012 at 2:57 PM, 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 fedexinvoi
On 15/11/12 15:03, Peter Geoghegan wrote:
On 15 November 2012 01:46, Andrew Dunstan 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 be
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 data and
normalizes it into a SETOF some record ty
On 15 November 2012 01:46, Andrew Dunstan 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
that for years,
On 11/14/2012 08:17 PM, Craig Ringer wrote:
On 11/15/2012 12:29 AM, Tom Lane wrote:
David Greco 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 flat
Craig Ringer 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 believe it's requi
On 11/15/2012 12:29 AM, Tom Lane wrote:
> David Greco 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'
David Greco 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 functions in both cases,
it's
-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 righ
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 CORRECTIO
ednesday, November 14, 2012 10:51 AM
To: David Greco
Cc: 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
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 dat
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 data and
normalizes it into a SETOF some record ty
52 matches
Mail list logo
