Skip site navigation (1) Skip section navigation (2)

Re: Perl/DBI vs Native

From: "Greg Sabino Mullane" <greg(at)turnstep(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Perl/DBI vs Native
Date: 2008-07-21 20:37:45
Message-ID: (view raw or whole thread)
Lists: pgsql-performance
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->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->{pg_server_prepare} = 0;
$sth->{pg_server_prepare} = 1;





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


DEALLOCATE dbdpg_p22988_1;


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


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(at)turnstep(dot)com
End Point Corporation
PGP Key: 0x14964AC8 200807211637


In response to


pgsql-performance by date

Next:From: Francisco ReyesDate: 2008-07-21 21:27:31
Subject: Re: A guide/tutorial to performance monitoring and tuning
Previous:From: LeviDate: 2008-07-21 19:01:04
Subject: Re: [BACKUPS]Little backups

Privacy Policy | About PostgreSQL
Copyright © 1996-2015 The PostgreSQL Global Development Group