Thread: Why Performance of SQL Query is *much* Slower in GUI PgAdmin
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? Thanks and Regards Yan Cheng CHEOK
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: > From: Guillaume Lelarge <guillaume@lelarge.info> > Subject: Re: [GENERAL] Why Performance of SQL Query is *much* Slower in GUI PgAdmin > To: "Yan Cheng CHEOK" <yccheok@yahoo.com> > Cc: pgsql-general@postgresql.org > Date: Monday, May 17, 2010, 1:22 PM > 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 > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
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 lifetimeof a database? It's like saying "The car from manufacturer A is slower than the one from manufacturer B, because it takes 1 second longerto 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 differencebecause 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 ourproprietary 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 informationproprietary 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 thatany unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have receivedthis e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mailimmediately. Thank you.
Attachment
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