pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSQL performance issue with PostgreSQL compared to MySQL
6+ messages / 5 participants
[nested] [flat]
* SQL performance issue with PostgreSQL compared to MySQL
@ 2002-06-19 13:26 Jeff Self <jself@nngov.com>
2002-06-19 14:18 ` Re: SQL performance issue with PostgreSQL compared to MySQL Tom Lane <tgl@sss.pgh.pa.us>
2002-06-19 14:58 ` Re: SQL performance issue with PostgreSQL compared to Josh Berkus <josh@agliodbs.com>
0 siblings, 2 replies; 6+ messages in thread
From: Jeff Self @ 2002-06-19 13:26 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: SQL performance issue with PostgreSQL compared to MySQL
2002-06-19 13:26 SQL performance issue with PostgreSQL compared to MySQL Jeff Self <jself@nngov.com>
@ 2002-06-19 14:18 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-06-19 16:43 ` Re: SQL performance issue with PostgreSQL compared to MySQL Bruce Momjian <pgman@candle.pha.pa.us>
1 sibling, 1 reply; 6+ messages in thread
From: Tom Lane @ 2002-06-19 14:18 UTC (permalink / raw)
To: Jeff Self <jself@nngov.com>; +Cc: pgsql-sql
Jeff Self <jself@nngov.com> writes:
> 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"
> [is slow]
What datatype are the columns used in the query? And what PG version
are we talking about?
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: SQL performance issue with PostgreSQL compared to MySQL
2002-06-19 13:26 SQL performance issue with PostgreSQL compared to MySQL Jeff Self <jself@nngov.com>
2002-06-19 14:18 ` Re: SQL performance issue with PostgreSQL compared to MySQL Tom Lane <tgl@sss.pgh.pa.us>
@ 2002-06-19 16:43 ` Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Bruce Momjian @ 2002-06-19 16:43 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Jeff Self <jself@nngov.com>; pgsql-sql
Tom Lane wrote:
> Jeff Self <jself@nngov.com> writes:
> > 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"
> > [is slow]
>
> What datatype are the columns used in the query? And what PG version
> are we talking about?
My guess is that is the lack of hashing for GROUP BY aggregating that is
killing us. TODO has:
* Add hash for evaluating GROUP BY aggregates
and I specifically remember MySQL has such a hash. The big question is
how many unique values are there in the group. If it is large, the
missing hashing could be the problem.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: SQL performance issue with PostgreSQL compared to
2002-06-19 13:26 SQL performance issue with PostgreSQL compared to MySQL Jeff Self <jself@nngov.com>
@ 2002-06-19 14:58 ` Josh Berkus <josh@agliodbs.com>
2002-06-20 07:16 ` Re: SQL performance issue with PostgreSQL compared to Achilleus Mantzios <achill@matrix.gatewaynet.com>
1 sibling, 1 reply; 6+ messages in thread
From: Josh Berkus @ 2002-06-19 14:58 UTC (permalink / raw)
To: Jeff Self <jself@nngov.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: SQL performance issue with PostgreSQL compared to
2002-06-19 13:26 SQL performance issue with PostgreSQL compared to MySQL Jeff Self <jself@nngov.com>
2002-06-19 14:58 ` Re: SQL performance issue with PostgreSQL compared to Josh Berkus <josh@agliodbs.com>
@ 2002-06-20 07:16 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2002-06-21 00:00 ` Re: SQL performance issue with PostgreSQL compared to Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 6+ messages in thread
From: Achilleus Mantzios @ 2002-06-20 07:16 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Jeff Self <jself@nngov.com>; pgsql-sql
On Wed, 19 Jun 2002, Josh Berkus wrote:
> > 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
^^^^^^^
Index on an aggregate function???
I though we could only index plain normal column iscachable functions.
Am i missing something?
>
> -Josh Berkus
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
--
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
tel: +30-10-8981112
fax: +30-10-8981877
email: achill@matrix.gatewaynet.com
mantzios@softlab.ece.ntua.gr
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: SQL performance issue with PostgreSQL compared to
2002-06-19 13:26 SQL performance issue with PostgreSQL compared to MySQL Jeff Self <jself@nngov.com>
2002-06-19 14:58 ` Re: SQL performance issue with PostgreSQL compared to Josh Berkus <josh@agliodbs.com>
2002-06-20 07:16 ` Re: SQL performance issue with PostgreSQL compared to Achilleus Mantzios <achill@matrix.gatewaynet.com>
@ 2002-06-21 00:00 ` Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 6+ messages in thread
From: Josh Berkus @ 2002-06-21 00:00 UTC (permalink / raw)
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>; +Cc: Jeff Self <jself@nngov.com>; pgsql-sql
Achilleus,
> > > 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
> ^^^^^^^
>
> Index on an aggregate function???
>
> I though we could only index plain normal column iscachable functions.
No, you're correct. I meant just to index on the bytes field.
--
-Josh Berkus
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2002-06-21 00:00 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-06-19 13:26 SQL performance issue with PostgreSQL compared to MySQL Jeff Self <jself@nngov.com>
2002-06-19 14:18 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-06-19 16:43 ` Bruce Momjian <pgman@candle.pha.pa.us>
2002-06-19 14:58 ` Re: SQL performance issue with PostgreSQL compared to Josh Berkus <josh@agliodbs.com>
2002-06-20 07:16 ` Re: SQL performance issue with PostgreSQL compared to Achilleus Mantzios <achill@matrix.gatewaynet.com>
2002-06-21 00:00 ` Re: SQL performance issue with PostgreSQL compared to Josh Berkus <josh@agliodbs.com>
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