From: "Josh Berkus" <josh@agliodbs.com>
Subject: Re: SQL performance issue with PostgreSQL compared to
To: Jeff Self <jself@nngov.com>, pgsql-sql@postgresql.org
Date: Wed, 19 Jun 2002 07:58:25 -0700
Message-ID: <web-1532976@davinci.ethosmedia.com>
In-Reply-To: <1024493212.11141.9.camel@jselfpc>
MIME-Version: 1.0
Content-Type: text/plain; charset="ISO-8859-1"
Content-Transfer-Encoding: 8bit

Jeff,

 **************************************************************************
> I'll post this message to the list, in case anyone wants to follow -
> or,
> if it's too OT, let me know, and I'll stop. The query "SELECT
> authuser,
> SUM(bytes) AS traffic FROM logfile GROUP BY authuser ORDER BY
> traffic"
> on a DB of approx. 1.8 million rows (same data in Pgsql and in mysql)
> takes 1.83min. in mysql, and 7.36min. on pgsql. The mysql db is
> raw...no
> indexes or anything 'tuning' done.

First off, this is nonsense.  One of its benefits for web developers is
that MySQL automatically indexes everything.

 > The pgsql db is indexed on the
> 'authuser' field, and I've run 'analyze logfile'. 

He also needs to index the bytes field and the traffic field.  And run
VACUUM, not just ANALYZE, if this is a high-activitly table, which I
suspect.

The machine is a
> PIII
> 600 w/728Mb RAM - and it's definitely CPU bound (both scream up to
> 100%
> and stay ;). As far as effecient queries, I'm not too sure how much
> more
> efficient that query can be made  - I'm a netadmin, not a DBA :)

We can tell.  To be blunt, MySQL is the database for non-DBAs.  He
should probably stick to using it rather than Postgres, which requires
some knowledge of performance tuning and query structure.

> My guess is he hasn't optimized PostgreSQL at all on his system. I
> will
> try and find out more from him as to what version of PostgreSQL he's
> running and try and get a copy of his postgresql.conf file. But can
> anyone think of how the SQL statement could be written to be more
> efficient? 

He also needs to up his sort_mem to the max his system and load will
allow.  He's sorting 1.8 million rows.

-Josh Berkus