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'
On Wed, Nov 14, 2012 at 12:04 PM, Jon Nelson wrote:
> On Wed, Nov 14, 2012 at 1:01 PM, Jeff Janes wrote:
>>
>> While the WAL is suppressed for the table inserts, it is not
>> suppressed for the index inserts, and the index WAL traffic is enough
>> to lead to contention.
>
> Aha!
>
>> I don't know
Robert Haas writes:
> Shouldn't there be a separate estimator for scalarlesel? Or should
> the existing estimator be adjusted to handle the two cases
> differently?
Well, it does handle it differently to some extent, in that the operator
itself is invoked when checking the MCV values, so we get
On Wed, Nov 14, 2012 at 5:36 PM, Robert Haas wrote:
> Shouldn't there be a separate estimator for scalarlesel? Or should
> the existing estimator be adjusted to handle the two cases
> differently?
Woulnd't adding eqsel to scalar(lt|gt)sel work? (saving duplication
with mvc_selectivity)
--
Sen
On Fri, Oct 26, 2012 at 5:08 PM, Tom Lane wrote:
> So the bottom line is that this is a case where you need a lot of
> resolution in the histogram. I'm not sure there's anything good
> we can do to avoid that. I spent a bit of time thinking about whether
> we could use n_distinct to get some id
On Wed, Nov 14, 2012 at 1:01 PM, Jeff Janes wrote:
> On Wed, Nov 14, 2012 at 6:41 AM, Jon Nelson wrote:
>>
>> UPDATE: I have been able to replicate the issue. The parent table (the
>> one referenced in the LIKE portion of the CREATE TABLE statement) had
>> three indices.
>>
>> Now that I've been
On Wed, Nov 14, 2012 at 6:41 AM, Jon Nelson wrote:
>
> UPDATE: I have been able to replicate the issue. The parent table (the
> one referenced in the LIKE portion of the CREATE TABLE statement) had
> three indices.
>
> Now that I've been able to replicate the issue, are there tests that I
> can pe
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 right. I was transla
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
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 CORRECTION CHARGE','ADDRESS CORRECTION'). Should return
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
On Tue, Nov 13, 2012 at 7:10 PM, Jon Nelson wrote:
> I had moved on to a different approach to importing the data which
> does not work concurrently. However, I went back and tried to
> re-create the situation and - at least a naive attempt failed. I'll
> give it a few more tries -- I was creating
On 14 November 2012 06:47, Craig Ringer wrote:
> Yes, it's absurd that it's so hard to work out how much memory Pg uses. It'd
> be nice if Pg provided better tools for this by allowing the postmaster to
> interrogate backends' memory contexts, though that'd only report how much
> memory Pg thought
18 matches
Mail list logo
