From: Scott Marlowe <smarlowe@g2switchworks.com>
To: Thomas Kellerer <spam_eater@gmx.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: SELECT very slow
Date: Thu, 16 Jun 2005 09:00:39 -0500
Message-ID: <1118930439.3382.91.camel@state.g2switchworks.com> (raw)
In-Reply-To: <d8q8iq$pfg$1@sea.gmane.org>
References: <d87ood$8vq$1@sea.gmane.org>
<d8pqbi$a5q$1@sea.gmane.org>
<op.ssc0t7wrth1vuj@localhost>
<op.ssfgo6xath1vuj@localhost>
<d8q8iq$pfg$1@sea.gmane.org>
On Wed, 2005-06-15 at 17:08, Thomas Kellerer wrote:
> PFC wrote on 15.06.2005 22:04:
>
> >
> >> It's not the program or Java. The same program takes about 20 seconds
> >> with Firebird and the exactly same data.
> >
> >
> > Hm, that's still very slow (it should do it in a couple seconds like
> > my PC does... maybe the problem is common to postgres and firebird ?)
> >
> > Try eliminating disk IO by writing a set returning function which
> > returns 1000000 rows, something simple like just a sequence number and
> > a text value... if this is slow too... i don't know...
>
> > do you have an antivirus or zonealarm or something ?
> Wouldn't that affect all DB access not only PG? And as I said, all other
>
> The 20 seconds are ok. This includes processing of the data in the
> application. If I simply loop over the result set and get each column's
> value without further processing it takes 4 seconds with Firebird.
>
> Basically I'm doing the following:
>
> rs = stmt.executeQuery("select * from foo");
> while (rs.next())
> {
> for (int i=0; i < 4; i++)
> {
> Object o = rs.getObject(i+1);
> }
> }
>
> As I said in my other post, the behaviour/performance in PG is dependent on
> the autocommit setting for the connection.
>
> With autocommit set to false the above code takes about 3 seconds in PG
> but wit autocommit set to true, PG takes 3 minutes! It seems that it also
> is very dependent on the fetchsize (apparently the number of rows that are
> cached by the driver). Anything above 100 seems to slow down the overall
> process.
There's got to be more happening than what this is showing us. A
select, and looping through it, should involve no writes, and therefore
no real performance difference from autocommit versus not. Is there
some underlying trigger on the view or something like that? Some kind
of auditing function?
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: smarlowe@g2switchworks.com, spam_eater@gmx.net
Subject: Re: SELECT very slow
In-Reply-To: <1118930439.3382.91.camel@state.g2switchworks.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox