Date: Tue, 07 Jun 2005 14:53:54 +0200
To: "Joel Fradkin" <jfradkin@wazagua.com>, "'Jan Wieck'" <JanWieck@yahoo.com>
Subject: Re: Need help to decide Mysql vs Postgres
Cc: "'Amit V Shah'" <ashah@tagaudit.com>,
	pgsql-performance@postgresql.org
References: <001c01c56acb$bc9ba730$797ba8c0@jfradkin>
From: PFC <lists@boutiquenumerique.com>
Organization: =?utf-8?Q?La_Boutique_Num=C3=A9rique?=
Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Message-ID: <op.srz3f4both1vuj@localhost>
In-Reply-To: <001c01c56acb$bc9ba730$797ba8c0@jfradkin>
User-Agent: Opera M2(BETA2)/8.0 (Linux, build 987)


> 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 ?