From: Jeff Self <jself@nngov.com>
To: pgsql-sql@postgresql.org
Subject: SQL performance issue with PostgreSQL compared to MySQL
Date: 19 Jun 2002 09:26:52 -0400
Message-ID: <1024493212.11141.9.camel@jselfpc> (raw)
This popped up on a local mailing list and I was alarmed at what results
the user was getting in regards to MySQL and PostgreSQL. It seems that
the query he was running on both databases was much faster in MySQL than
PostgreSQL. Here's what he wrote:
**************************************************************************
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. The pgsql db is indexed on the
'authuser' field, and I've run 'analyze logfile'. 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 :)
**************************************************************************
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?
--
Jeff Self
Information Technology Analyst
Department of Personnel
City of Newport News
2400 Washington Avenue
Newport News, VA 23607
757-926-6930
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: jself@nngov.com
Subject: Re: SQL performance issue with PostgreSQL compared to MySQL
In-Reply-To: <1024493212.11141.9.camel@jselfpc>
* 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