DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:user-agent:mime-version:to:subject
	:content-type:content-transfer-encoding;
	b=uh/1Qyc5EbVccYz3J/13aKd/7Y2Am0gfORy+flKbh8McYF64fwig0JsXesQ9jhtOXY
	vnhAWF8ssp7nHiP7sCQDHfPB1dQHaBdQk0rqVDWHhF4cPHXPjIyin0oq3i3q0qgoccZ+
	ACrNPn0RDIIUAR18eyWVEUtnaXmTSzs2hgcjM=
Message-ID: <4BA23936.2030507@gmail.com>
Date: Thu, 18 Mar 2010 15:31:18 +0100
From: Corin <wakathane@gmail.com>
User-Agent: Thunderbird 2.0.0.23 (X11/20090817)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: mysql to postgresql, performance questions
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi all,

I'm running quite a large social community website (250k users, 16gb 
database). We are currently preparing a complete relaunch and thinking 
about switching from mysql 5.1.37 innodb to postgresql 8.4.2. The 
database server is a dual dualcore operton 2216 with 12gb ram running on 
debian amd64.

For a first impression I ran a simple query on our users table (snapshot 
with only ~ 45.000 records). The table has an index on birthday_age 
[integer]. The test executes 10 times the same query and simply discards 
the results. I ran the tests using a php and a ruby script, the results 
are almost the same.

Unluckily mysql seems to be around 3x as fast as postgresql for this 
simple query. There's no swapping, disc reading involved...everything is 
in ram.

query
select * from users where birthday_age between 12 and 13 or birthday_age 
between 20 and 22 limit 1000

mysql
{"select_type"=>"SIMPLE", "key_len"=>"1", "id"=>"1", "table"=>"users", 
"type"=>"range", "possible_keys"=>"birthday_age", "rows"=>"7572", 
"Extra"=>"Using where", "ref"=>nil, "key"=>"birthday_age"}
15.104055404663
14.209032058716
18.857002258301
15.714883804321
14.73593711853
15.048027038574
14.589071273804
14.847040176392
15.192985534668
15.115976333618

postgresql
{"QUERY PLAN"=>"Limit (cost=125.97..899.11 rows=1000 width=448) (actual 
time=0.927..4.990 rows=1000 loops=1)"}
{"QUERY PLAN"=>" -> Bitmap Heap Scan on users (cost=125.97..3118.00 
rows=3870 width=448) (actual time=0.925..3.420 rows=1000 loops=1)"}
{"QUERY PLAN"=>" Recheck Cond: (((birthday_age >= 12) AND (birthday_age 
<= 13)) OR ((birthday_age >= 20) AND (birthday_age <= 22)))"}
{"QUERY PLAN"=>" -> BitmapOr (cost=125.97..125.97 rows=3952 width=0) 
(actual time=0.634..0.634 rows=0 loops=1)"}
{"QUERY PLAN"=>" -> Bitmap Index Scan on birthday_age (cost=0.00..41.67 
rows=1341 width=0) (actual time=0.260..0.260 rows=1327 loops=1)"}
{"QUERY PLAN"=>" Index Cond: ((birthday_age >= 12) AND (birthday_age <= 
13))"}
{"QUERY PLAN"=>" -> Bitmap Index Scan on birthday_age (cost=0.00..82.37 
rows=2611 width=0) (actual time=0.370..0.370 rows=2628 loops=1)"}
{"QUERY PLAN"=>" Index Cond: ((birthday_age >= 20) AND (birthday_age <= 
22))"}
{"QUERY PLAN"=>"Total runtime: 5.847 ms"}
44.173002243042
41.156768798828
39.988040924072
40.470123291016
40.035963058472
40.077924728394
40.94386100769
40.183067321777
39.83211517334
40.256977081299

I also wonder why the reported runtime of 5.847 ms is so much different 
to the runtime reported of my scripts (both php and ruby are almost the 
same). What's the best tool to time queries in postgresql? Can this be 
done from pgadmin?

Thanks,
Corin