Re: why do we need two snapshots per query?

On Sun, Nov 13, 2011 at 7:37 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> In my experience, it's hard enough as it is to convince developers to
>> use statement parameters instead of interpolating them into the SQL
>> string. Once word gets out that the simple protocol is now has less locking
>> overhead than the extended protocol, it's going to get even harder...
>
> [ discussion of convincing people to use
>
> At any rate, if you're concerned about the relative efficiency of the
> simple query protocol versus the extended protocol, it seems that the
> horse has already left the barn.

On further examination, it seems that the behavior of the current code
is as follows:

pgbench -n -S -t 2000 ==> ~4000 snapshots
pgbench -n -S -t 2000 -M extended ==> ~6000 snapshots
pgbench -n -S -t 2000 -M prepared ==> ~4000 snapshots

So it's already the case that simple protocol has less locking
overhead than the extended protocol, unless you're using prepared
queries. The -M prepared case appears to be doing just about exactly
the same thing that happens in the simple case: we take a snapshot in
exec_bind_message() and then release it a nanosecond before calling
PortalStart(), which promptly takes a new one. IOW, it looks like the
same optimization that applies to the simple case can be applied here
as well.

In the -M extended case, we take a snapshot from exec_parse_message(),
and the same two in the exec_bind_message() call that are taken in the
-M prepared case. So reducing the prepared case from two snapshots to
one will reduce the extended case from three snapshots to two, thus
saving one snapshot per query regardless of how it's executed.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company