From: | PFC <lists(at)boutiquenumerique(dot)com> |
---|---|
To: | "Joel Fradkin" <jfradkin(at)wazagua(dot)com>, "'Jan Wieck'" <JanWieck(at)yahoo(dot)com> |
Cc: | "'Amit V Shah'" <ashah(at)tagaudit(dot)com>, pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Need help to decide Mysql vs Postgres |
Date: | 2005-06-07 12:53:54 |
Message-ID: | op.srz3f4both1vuj@localhost |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
> 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 ?
From | Date | Subject | |
---|---|---|---|
Next Message | Marty Scholes | 2005-06-07 15:30:13 | Re: Postgresql and Software RAID/LVM |
Previous Message | Russell Smith | 2005-06-07 09:43:16 | Re: Performance nightmare with dspam (urgent) |