> explain analyze
> select a.ID, a.provider, a.hostname, a.username, a.eventTimeStamp,
> a.AIPGUID, a.submissionGUID, a.parentSubmissionGUID, a.sizeArchived,
> a.addedContentString, a.addedContentSizesString, a.removedContentString,
> a.removedContentSizesString, a.modifiedContentStr
Maria L. Wilson wrote:
> Can someone shed some light on the following query.
> any help would certainly be appreciated!
The query text and EXPLAIN ANALYZE output are a good start, but a lot
of other information is needed to really understand the issue.
http://wiki.postgresql.org/wiki/SlowQue
Sergio Mayoral wrote:
> I cannot use persistent connections. I must open/close a connection
> anytime I want to insert something new.
That's odd. Why is that?
> do i have to configure something different? Am i missing something?
You could use pgbouncer to hold database connections open for you
If you are inserting a lot of data into the same table, table extension locks
are a problem, and will be extended in only 8k increments which if you have a
lot of clients hitting/expanding the same table you are going to have a lot of
overhead.
-Original Message-
From: pgsql-performance
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
I have database with few hundred millions of rows. I'm running the following
query:
select * from "Payments" as p
inner join "PaymentOrders" as po
on po."Id" = p."PaymentOrderId"
inner join "Users" as u
On u."Id" = po."UserId"
INNER JOIN "Roles" as r
on u."RoleId" = r."Id"
Where r."Name" = 'Moses'
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
Hi,
i am running some tests to check performance between postgresql and mysql.
one important issue is PQconnectdb (PQconnectStart/PQconnectPoll) against
mysql_init/mysql_real_connect functions. (debian platform/C application).
PQconnectdb("host=localhost dbname=my_db user=my_user password=''
s
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: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 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 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
Can someone shed some light on the following query.
any help would certainly be appreciated!
thanks -
*
Maria Wilson
Nasa/Langley Research Center
Hampton, Virginia
m.l.wil...@nasa.gov
*
explain analyze
select a.ID, a.provider, a.hostname, a.username, a.eventTimeStamp,
a.AIPGUID, a.
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 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
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
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 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
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 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
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 3:53 PM, Tom Lane wrote:
> 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
On 11/21/2012 09:35 AM, Craig James wrote:
> Why not make an explicit hint syntax and document it? I've still don't
> understand why "hint" is a dirty word in Postgres. There are a
> half-dozen or so ways in common use to circumvent or correct
> sub-optimal plans.
>
The reason usually given is th
23 matches
Mail list logo
