Re: PostgreSQL client api

On Tue, 2006-03-28 at 19:01, Antimon wrote:
> Hi,
> I was testing MySQL and PgSQL performances on my home box (amd athlon
> 64 3000, 1gig ddr ram, sata I hdd, win xp (x86)), select and insert
> times seeemed identical with innoDB.
>
> But when i try to query both using php, there's a huge difference even
> for a funny query like "select 1"
>
> Here's the code:
>
> <?php
> $mtime = microtime(true);
>
> $pdo = new PDO('pgsql:host=localhost;dbname=test', "testacc", "pw");
> for ($i = 0; $i < 10000; $i++)
> {
> $result = $pdo->query("Select "+$i);
> }
>
> echo microtime(true) - $mtime;
> echo "<br>";
>
> $mtime = microtime(true);
>
> $pdo = new PDO('mysql:host=localhost;dbname=test', "testacc", "pw");
> for ($i = 0; $i < 10000; $i++)
> {
> $result = $pdo->query("Select "+$i);
> }
>
> echo microtime(true) - $mtime;
> echo "<br>";
> ?>
>
> output is:
> 2.7696590423584
> 0.89393591880798
>
> Nearly 3 times slower even w/o any table queries. But i could not
> reproduce this by writing stored procs on both which selects 0-10000 in
> a loop to a variable. results were almost same.
> (I tried pg_ and mysqli_ functions too, results were not too different)
>
> Is it mysql client libraries performs better? Or postgre stored procs
> are 3x faster? I cannot understand, since there is not even an io
> operation or any query planning stuff, what is the cause of this?

MySQL's client libs, connection speed, and parser are known to be quite
fast. PostgreSQL's aren't exactly slow, but they have more to do. As
you start running actual queries, you'll see the advantage start to
disappear, and by the time you're running queries full of subselects and
unions, you'll notice the it's the tortoise and the hare all over again.

As someone else mentioned, factor out the connection time. But really,
this test is fairly bogus. Unless you're writing an app that has a lot
of "select 1" in it that is.

At least test something fairly realistic. Maybe some kind of thing like
having 10,000,000 rows, pick a number at random from 1 to 10,000,000 and
use it to select one row at a time. Better yet, make a union view with
a subselect on a function, create a functional index for the underlying
tables, and see if your outside where clause gets pushed down inside the
query by the planner.

Run updates in the background at the same time.

Pull the power plug whilst doing it, see which database comes back up.

Just benchmark what you're really gonna use the database for, cause if
you pick one or the other from this test, you might as well flip a coin.