Why Performance of SQL Query is *much* Slower in GUI PgAdmin

Yan Cheng CHEOK <yccheok@yahoo.com> writes:

Recently, I try to introduce my friend to use PostgreSQL.
However, he first impression is that. PostgreSQL is much slower compared to MySQL. He realize he has to wait for 7 seconds, to create a tmp database.

Just for the record, CREATE DATABASE in postgres has approximately
nothing to do with CREATE DATABASE in mysql --- the latter is more or
less equivalent to what we call CREATE SCHEMA. So if you're going to
form speed impressions based on just this one operation, try comparing
those two commands.

regards, tom lane

Le 17/05/2010 03:21, Yan Cheng CHEOK a �crit :

Recently, I try to introduce my friend to use PostgreSQL.

However, he first impression is that. PostgreSQL is much slower compared to MySQL. He realize he has to wait for 7 seconds, to create a tmp database.

He run a command through pgadmin GUI.

"CREATE DATABASE TMP;"

I try to run through command prompt instead. It tooks 2 seconds.

May I know what is the reason, SQL query execution is *much* slower in GUI PgAdmin?

593ms in pgAdmin, 562ms in psql. A bit slower, but not that much (in 8.4).

--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com

The pgadmin result seems different with my machine. My friend and I are using Windows machine. Are you using Linux machine?

Thanks and Regards
Yan Cheng CHEOK

--- On Mon, 5/17/10, Guillaume Lelarge <guillaume@lelarge.info> wrote:

On Sun, May 16, 2010 at 7:21 PM, Yan Cheng CHEOK <yccheok@yahoo.com> wrote:

Recently, I try to introduce my friend to use PostgreSQL.

However, he first impression is that. PostgreSQL is much slower compared to MySQL. He realize he has to wait for 7 seconds, to create a tmp database.

He run a command through pgadmin GUI.

"CREATE DATABASE TMP;"

I try to run through command prompt instead. It tooks 2 seconds.

Maybe he's running an older version that hasn't had create db
optimized? Maybe he's looking at exactly the wrong place to be
deciding which db is faster? Seriously, from the perspective of a DBA
with a db in the hundreds of gigabytes, how long it takes to create an
empty one is exactly 0% interesting to me, unless it somehow becomes
minutes or something weird, which it hasn't.

How well a db runs with hundreds of active connections and queries,
and running complex operations is interesting. This? Not so much.

Here's a more useful test.

write a simple script that creates a 100 or so dbs. Run it against
each database, and while it's running, pull the power plug. Plug back
in, see if your database comes back up uncorrupted.

If pgsql is on proper db hardware (i.e. it obeys fsync, has battery
backed cache on a RAID controller, etc) it should come up no problems.
Since the system catalogs are stored in the same transactional engine
/ table type as everything else. On MySQL, maybe, maybe not, as the
system catalogs are always stored in MyISAM tables even if you dont'
use them for anything else.

Yan Cheng CHEOK, 17.05.2010 03:21:

Recently, I try to introduce my friend to use PostgreSQL.

However, he first impression is that. PostgreSQL is much slower
compared to MySQL. He realize he has to wait for 7 seconds, to create
a tmp database.

Why on earth would anybody compare database performance using a command that is usually executed only once in the lifetime of a database?

It's like saying "The car from manufacturer A is slower than the one from manufacturer B, because it takes 1 second longer to start the engine..."

Thomas

On Mon, May 17, 2010 at 2:35 AM, Thomas Kellerer <spam_eater@gmx.net> wrote:

Yan Cheng CHEOK, 17.05.2010 03:21:

Recently, I try to introduce my friend to use PostgreSQL.

However, he first impression is that. PostgreSQL is much slower
compared to MySQL. He realize he has to wait for 7 seconds, to create
a tmp database.

Why on earth would anybody compare database performance using a command that
is usually executed only once in the lifetime of a database?

It's like saying "The car from manufacturer A is slower than the one from
manufacturer B, because it takes 1 second longer to start the engine..."

I refer you to this classic post on the subject:
http://www.mail-archive.com/pgsql-general@postgresql.org/msg93043.html

Scott Marlowe, 17.05.2010 10:58:

Why on earth would anybody compare database performance using a command that
is usually executed only once in the lifetime of a database?

It's like saying "The car from manufacturer A is slower than the one from
manufacturer B, because it takes 1 second longer to start the engine..."

I refer you to this classic post on the subject:
http://www.mail-archive.com/pgsql-general@postgresql.org/msg93043.html

Nice one :)

That reminds me of a "fun" car test in a German car magazine some years ago.

They were comparing the cheapest and the most expensive car from the Fiat group:
a Fiat 127 (similar to a Fiat 500 nowadays) against a Ferarri Testarossa.

The first test was to get 3 people from Munich to Augsburg (approx. 70km), the Ferarri lost with about 30 minutes difference because it had to drive two times ;)

Regards
Thomas

On Mon, May 17, 2010 at 12:02 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:

On Sun, May 16, 2010 at 7:21 PM, Yan Cheng CHEOK <yccheok@yahoo.com> wrote:

Recently, I try to introduce my friend to use PostgreSQL.

However, he first impression is that. PostgreSQL is much slower compared to MySQL. He realize he has to wait for 7 seconds, to create a tmp database.

He run a command through pgadmin GUI.

"CREATE DATABASE TMP;"

I try to run through command prompt instead. It tooks 2 seconds.

Maybe he's running an older version that hasn't had create db
optimized?

Just to point out, there was a discussion I can't find right now about
fsync and create database optimization earlier this year. Apparently
grouping everything together resulted in a big performance gain for
create database. So it really is likely that a different version
could be much slower. However, if you're both hitting the same db,
then the slowness is in the gui tool, not pgsql.

On 5/17/2010 12:52 AM, Yan Cheng CHEOK wrote:

The pgadmin result seems different with my machine. My friend and I are using Windows machine. Are you using Linux machine?

Thanks and Regards
Yan Cheng CHEOK

**snip**

I use both windows and Linux using pgadmin, and on occasion use psql

I take my work home so I backup then drop then recreate the databasse on
my computers at home. So I create the database at least 2 or 3 times a
week. I see no meaningful difference between machines or client used to
create a DB using either Windows or Linux. I have never timed it. Why
would anyone time creating a DB??? But i would say its less than second.

So something weird is going with your friends computer.

All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by our proprietary quotation system. Quotations received via any other form of communication will not be honored.

CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally privileged, confidential or other information proprietary to Magwerks Corporation and is intended solely for the use of the individual to whom it addresses. If the reader of this e-mail is not the intended recipient or authorized agent, the reader is hereby notified that any unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mail immediately.
Thank you.

Le 17/05/2010 07:52, Yan Cheng CHEOK a �crit :

The pgadmin result seems different with my machine. My friend and I are using Windows machine. Are you using Linux machine?

My tests this morning were on a Linux machine. I tried tonight with a
Windows PC. Here are the results for three "create database":

pgAdmin: 7.6s ; 8.5s ; 7.6s
psql: 6.8s ; 7.4s ; 7.4s

Not much of a difference either (between the GUI tool (pgAdmin) and the
CLI tool (psql)). But a really big one between Linux and Windows.

PG 8.4.4, pgAdmin 1.12b1+ (both on Linux and Windows).

--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com