From: Josh Berkus <josh@agliodbs.com>
To: Jeff Self <jself@nngov.com>
To: pgsql-sql@postgresql.org
Subject: Re: SQL performance issue with PostgreSQL compared to
Date: Wed, 19 Jun 2002 07:58:25 -0700
Message-ID: <web-1532976@davinci.ethosmedia.com> (raw)
In-Reply-To: <1024493212.11141.9.camel@jselfpc>
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
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: josh@agliodbs.com, jself@nngov.com
Subject: Re: SQL performance issue with PostgreSQL compared to
In-Reply-To: <web-1532976@davinci.ethosmedia.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