To support migration of existing queries, it would be nice not to have
to rewrite EXISTS clauses as IN clauses. Here is one example of a query
which optimizes poorly:
DELETE FROM "CaseDispo"
WHERE EXISTS
(
SELECT * FROM "Consolidation" "C"
WHERE "C
Am Freitag, den 03.11.2006, 14:38 + schrieb Richard Huxton:
> [EMAIL PROTECTED] wrote:
> >> If you can keep your numbers of clients down below the critical
> >> level, you should find the overall workload is fine.
> >
> > We have at about 600 connections. Is this a case to use a connection
>
[EMAIL PROTECTED] writes:
> And why this happens only with 8.0 and 8.1 and not with the 7.4?
8.0 and 8.1 are vulnerable to this behavior because of conflicts for
access to pg_subtrans (which didn't exist in 7.4). The problem occurs
when you have old open transactions, causing the window over whic
On 11/3/06, Richard Huxton wrote:
There's a GUI debugger from EnterpriseDB I believe, but I've no idea how
good it is. Any users/company bods care to let us know?
If you visit:
http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/edb-debugger/#dirlist
We have both a PL/pgSQL profiler and tracer availa
Arjen van der Meijden <[EMAIL PROTECTED]> writes:
> ... Rewriting it to something like this made the last iteration about as
> fast as the first:
> SELECT docid, (SELECT work to be done for each document)
> FROM documents
> WHERE docid IN (SELECT docid FROM documents
> ORDER BY docid
>
[EMAIL PROTECTED] wrote:
If you can keep your numbers of clients down below the critical
level, you should find the overall workload is fine.
We have at about 600 connections. Is this a case to use a connection
pool (pg_pool) system?
Possibly - that should help. I'm assuming that most of your
> If you can keep your numbers of clients down below the critical > level, > you should find the overall workload is fine.
We have at about 600 connections. Is this a case to use a connection pool (pg_pool) system?
And why this happens only with 8.0 and 8.1 and not with the 7.4?
Richard Huxton wrote:
[EMAIL PROTECTED] wrote:
Hi,
We've migrated one of our servers from pg 7.4 to 8.1 and from times
to times (4 hours) the server start doing a lot of context switching
and all transactions become very slow.
The average context switching for this server as vmstat shows is 1
Richard Troy wrote:
On Fri, 3 Nov 2006, Richard Huxton wrote:
It's memory bandwidth issues on the older Xeons. If you search the
archives you'll see a lot of discussion of this. I'd have thought 8.1
would be better than 7.4 though.
Hmmm... I just checked; one of our production systems is a mul
On Fri, 3 Nov 2006, Richard Huxton wrote:
>
> It's memory bandwidth issues on the older Xeons. If you search the
> archives you'll see a lot of discussion of this. I'd have thought 8.1
> would be better than 7.4 though.
Hmmm... I just checked; one of our production systems is a multi-cpu Xeon
bas
The solution for us has been twofold:
upgrade to the newest PG version available at the time while we waited
for our new Opteron-based DB hardware to arrive.
Andreas
Am Freitag, den 03.11.2006, 13:29 + schrieb Richard Huxton:
> Cosimo Streppone wrote:
> > Richard Huxton wrote:
> >
> >> [EM
Am Donnerstag, den 02.11.2006, 09:41 -0600 schrieb Scott Marlowe:
> Sometimes it's the simple solutions that work best. :) Welcome to the
> world of pgsql, btw...
OTOH, there are also non-simple solutions to this, which might make
sense anyway: Install slony, and run your queries against a reado
Cosimo Streppone wrote:
Richard Huxton wrote:
[EMAIL PROTECTED] wrote:
The average context switching for this server as vmstat shows is 1
but when the problem occurs it goes to 25.
You'll tend to see it when you have multiple clients and most queries
can use RAM rather than disk I/O. M
[EMAIL PROTECTED] wrote:
Hi,
We've migrated one of our servers from pg 7.4 to 8.1 and from times
to times (4 hours) the server start doing a lot of context switching
and all transactions become very slow.
The average context switching for this server as vmstat shows is 1
but when the problem oc
Based on what other people have posted, hyperthreading seems not to be
beneficial for postgres -- try searching through the archives of this list.
(And then turn it off and see if it helps.)
You might also post a few details:
config settings (shared_buffers, work_mem, maintenance_work_mem, wal
Hi,
We've migrated one of our servers from pg 7.4 to 8.1 and from times to times (4 hours) the server start doing a lot of context switching and all transactions become very slow.
The average context switching for this server as vmstat shows is 1 but when the problem occurs it goes to 25.
A. Kretschmer wrote:
am Fri, dem 03.11.2006, um 3:12:14 -0800 mailte Drew Wilson folgendes:
I have 700 lines of non-performant pgSQL code that I'd like to
profile to see what's going on.
What's the best way to profile stored procedures?
RAISE NOTICE, you can raise the aktual time within a
am Fri, dem 03.11.2006, um 3:12:14 -0800 mailte Drew Wilson folgendes:
> I have 700 lines of non-performant pgSQL code that I'd like to
> profile to see what's going on.
>
> What's the best way to profile stored procedures?
RAISE NOTICE, you can raise the aktual time within a transaction with
I have 700 lines of non-performant pgSQL code that I'd like to
profile to see what's going on.
What's the best way to profile stored procedures?
Thanks,
Drew
---(end of broadcast)---
TIP 4: Have you searched our list archives?
ht
Alvaro Herrera wrote:
Performance analysis of strange queries is useful, but the input queries
have to be meaningful as well. Otherwise you end up optimizing bizarre
and useless cases.
I had a similar one a few weeks ago. I did some batch-processing over a
bunch of documents and discovered p
20 matches
Mail list logo
