"Merlin Moncure" <mmoncure@xxxxxxxxx> writes: > On 5/25/06, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: >> "Merlin Moncure" <mmoncure@xxxxxxxxx> 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
