From: PFC <lists@boutiquenumerique.com>
To: Joel Fradkin <jfradkin@wazagua.com>
To: 'Jan Wieck' <JanWieck@yahoo.com>
Cc: 'Amit V Shah' <ashah@tagaudit.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Need help to decide Mysql vs Postgres
Date: Tue, 07 Jun 2005 14:53:54 +0200
Message-ID: <op.srz3f4both1vuj@localhost> (raw)
In-Reply-To: <001c01c56acb$bc9ba730$797ba8c0@jfradkin>
References: <001c01c56acb$bc9ba730$797ba8c0@jfradkin>
> My tests included using aqua studios connection to both databases and
> .asp
> page using odbc connections.
Performance also depends a lot on the driver.
For instance, the PHP driver for MySQL is very very fast. It is also very
dumb, as it returns everything as a string and doesn't know about quoting.
For Python it's the reverse : the MySQL driver is slow and dumb, and the
postgres driver (psycopg 2) is super fast, handles all quoting, and knows
about type conversions, it will automatically convert a Python List into a
postgres Array and do the right thing with quoting, and it works both ways
(ie you select a TEXT[] you get a list of strings all parsed for you). It
knows about all the postgres types (yes even numeric <=> python Decimal)
and you can even add your own types. That's really cool, plus the
developer is a friendly guy.
------------------ in psql :
test=> CREATE TABLE typetests ( id SERIAL PRIMARY KEY, iarray INTEGER[]
NULL, narray NUMERIC[] NULL, tarray TEXT[] NULL,vnum NUMERIC NULL, vint
INTEGER NULL, vtext TEXT NULL) WITHOUT OIDS;
NOTICE: CREATE TABLE will create implicit sequence "typetests_id_seq" for
serial column "typetests.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"typetests_pkey" for table "typetests"
CREATE TABLE
------------------- in Python :
data = {
'myiarray' : [1,5,8,6],
'mytarray' : ['hello','world'],
'mynarray' : [Decimal("1.23"),Decimal("6.58")],
'mynum' : Decimal("66.66"),
'myint' : 555,
'mytext' :u "This is an Unicode String Портал по изучению иностранных"
}
cursor.execute( """INSERT INTO typetests
(iarray,narray,tarray,vnum,vint,vtext)
VALUES
(%(myiarray)s,%(mynarray)s,%(mytarray)s,%(mynum)s,%(myint)s,%(mytext)s)""",
data );
------------------ in psql :
test=> SELECT * FROM typetests;
id | iarray | narray | tarray | vnum | vint | vtext
----+-----------+-------------+---------------+-------+------+-----------
4 | {1,5,8,6} | {1.23,6.58} | {hello,world} | 66.66 | 555 | This is an
Unicode String Портал по изучению иностранных
(1 ligne)
------------------- in Python :
cursor.execute( "SELECT * FROM typetests" )
for row in cursor.fetchall():
for elem in row:
print type(elem), elem
------------------- output :
<type 'int'> 4
<type 'list'> [1, 5, 8, 6]
<type 'list'> [Decimal("1.23"), Decimal("6.58")]
<type 'list'> ['hello', 'world']
<class 'decimal.Decimal'> 66.66
<type 'int'> 555
<type 'str'> This is an Unicode String Портал по изучению иностранных
------------------- in Python :
cursor = db.cursor(cursor_factory = psycopg.extras.DictCursor)
cursor.execute( "SELECT * FROM typetests" )
for row in cursor.fetchall():
for key, value in row.items():
print key, ":", type(value), value
------------------- output :
iarray : <type 'list'> [1, 5, 8, 6]
tarray : <type 'list'> ['hello', 'world']
vtext : <type 'str'> This is an Unicode String Портал по изучению
иностранных
id : <type 'int'> 4
vnum : <class 'decimal.Decimal'> 66.66
vint : <type 'int'> 555
narray : <type 'list'> [Decimal("1.23"), Decimal("6.58")]
------------------- Timings :
Time to execute SELECT * FROM typetests and fetch the results, including
type conversions :
Plain query : 0.279 ms / request
Prepared query : 0.252 ms / request
(not that bad ! Pentium-M 1600 MHz laptop with local postgres).
Just doing SELECT id FROM typetests gives 0.1 ms for executing query and
fetching the result.
Who said Postgres was slow on small queries ?
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-performance@postgresql.org
Cc: lists@boutiquenumerique.com, jfradkin@wazagua.com, JanWieck@yahoo.com, ashah@tagaudit.com
Subject: Re: Need help to decide Mysql vs Postgres
In-Reply-To: <op.srz3f4both1vuj@localhost>
* 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