From: "Greg Sabino Mullane" <greg@turnstep.com>
To: pgsql-performance@postgresql.org
Subject: Re: Perl/DBI vs Native
In-Reply-To: <16163.1216665872@sss.pgh.pa.us>
Date: Mon, 21 Jul 2008 20:37:45 -0000
Message-ID: <04db0f32170da679850a07d8b127e3b6@biglumber.com>


-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160


Tom Lane wrote:
> Sure, but so does psql (unless you've turned on the magic FETCH_COUNT
> setting).  I think the theories about prepared versus literal statements
> were more promising; but I don't know DBI well enough to know exactly
> what it was sending to the server.

Almost certainly a prepared_statement unless no placeholders were being
used at all. Another way to test (from the DBI side) is to set
$sth->{pg_server_prepare} = 0, which will send the SQL directly to the
backend, just as if you've typed it in at a command prompt. You can also
use the tracing mechanism of DBI to see what's going on behind the scenes.
For example:

$dbh->trace('SQL');

$dbh->do("SELECT 1234 FROM pg_class WHERE relname = 'bob'");
$dbh->do("SELECT 1234 FROM pg_class WHERE relname = ?", undef, 'mallory');

$sth = $dbh->prepare("SELECT 4567 FROM pg_class WHERE relname = ?");
$sth->execute('alice');
$sth->{pg_server_prepare} = 0;
$sth->execute('eve1');
$sth->{pg_server_prepare} = 1;
$sth->execute('eve2');

$dbh->commit;

Outputs:

===

begin;

SELECT 1234 FROM pg_class WHERE relname = 'bob';

EXECUTE SELECT 1234 FROM pg_class WHERE relname = $1 (
$1: mallory
);

PREPARE dbdpg_p22988_1 AS SELECT 4567 FROM pg_class WHERE relname = $1;

EXECUTE dbdpg_p22988_1 (
$1: alice
);

SELECT 4567 FROM pg_class WHERE relname = 'eve1';

EXECUTE dbdpg_p22988_1 (
$1: eve2
);

commit;

DEALLOCATE dbdpg_p22988_1;

===

You can even view exactly which libpq calls are being used at each point with:

$dbh->trace('SQL,libpq');

To get back to the original poster's complaint, you may want to figure out why
the difference is so great for a prepared plan. It may be that you need to
cast the placeholder(s) to a specific type, for example.

- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation
PGP Key: 0x14964AC8 200807211637
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAkiE83wACgkQvJuQZxSWSsiGrwCdGMLgauGwR2UzfoMPrTH/mrRg
nxsAnjx14goMV23a9yRjtSw+ixJWQkuI
=gjVE
-----END PGP SIGNATURE-----