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.modifiedContentString,
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.
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:
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
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'
LIMIT 1000When 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
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=''
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
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,
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 Tue, Nov 20, 2012 at 3:53 PM, Tom Lane t...@sss.pgh.pa.us wrote:
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
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 that
22 matches
Mail list logo
