-----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@xxxxxxxxxxxx
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-----
