X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 9941C9FA279 for ; Fri, 26 May 2006 11:22:46 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 52216-07 for ; Fri, 26 May 2006 11:22:40 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 07BC99F9B01 for ; Fri, 26 May 2006 11:22:40 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id k4QEMc1E025669; Fri, 26 May 2006 10:22:38 -0400 (EDT) To: "Merlin Moncure" cc: "Postgresql Performance" Subject: Re: is it possible to make this faster? In-reply-to: References: <15919.1148590347@sss.pgh.pa.us> <16119.1148591497@sss.pgh.pa.us> Comments: In-reply-to "Merlin Moncure" message dated "Fri, 26 May 2006 00:47:35 -0400" Date: Fri, 26 May 2006 10:22:38 -0400 Message-ID: <25668.1148653358@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/492 X-Sequence-Number: 19279 "Merlin Moncure" writes: > On 5/25/06, Tom Lane wrote: >> "Merlin Moncure" writes: >>> recent versions of mysql do much better, returning same set in < 20ms. >> Are you sure you measured that right? I tried to duplicate this using >> mysql 5.0.21, and I see runtimes of 0.45 sec without an index and >> 0.15 sec with. This compares to psql times around 0.175 sec. Doesn't >> look to me like we're hurting all that badly, even without using the >> index. > Well, my numbers were approximate, but I tested on a few different > machines. the times got closer as the cpu speed got faster. pg > really loves a quick cpu. on 600 mhz p3 I got 70ms on mysql and > 1050ms on pg. Mysql query cache is always off for my performance > testing. Well, this bears looking into, because I couldn't get anywhere near 20ms with mysql. I was using a dual Xeon 2.8GHz machine which ought to be quick enough, and the stock Fedora Core 5 RPM of mysql. (Well, actually that SRPM built on FC4, because this machine is still on FC4.) I made a MyISAM table with three integer columns as mentioned, and filled it with about 300000 rows with 2000 distinct values of (a,b) and random values of c. I checked the timing both in the mysql CLI, and with a trivial test program that timed mysql_real_query() plus mysql_store_result(), getting pretty near the same timings each way. BTW, in pgsql it helps a whole lot to raise work_mem a bit for this example --- at default work_mem it wants to do sort + group_aggregate, while with work_mem 2000 or more it'll use a hash_aggregate plan which is quite a bit faster. It seems possible that there is some equivalently simple tuning on the mysql side that you did and I didn't. This is an utterly stock mysql install, just "rpm -i" and "service mysqld start". regards, tom lane