Subject: SQL performance issue with PostgreSQL compared to MySQL
From: Jeff Self <jself@nngov.com>
To: pgsql-sql@postgresql.org
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Date: 19 Jun 2002 09:26:52 -0400
Message-Id: <1024493212.11141.9.camel@jselfpc>
Mime-Version: 1.0

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