Re: Perl::DBI and interval syntax [side question] - Mailing list pgsql-general

Martijn van Oosterhout wrote:
> On Mon, Nov 07, 2005 at 05:58:04PM -0500, Tom Lane wrote:
>> Allen <dba@girders.org> writes:
>> > SELECT count(*) from post where post_ts >= current_date - interval ?
>> This is not right, and never has been right, even though it may have
>> accidentally failed to fail with some client libraries.  Try
>> CAST(? as interval)                  (SQL standard)
>> ?::interval                          (Postgres-ism)
[...]
> This seems to be an example of breakage caused by DBI switching from
> "substitute params in client" to "use new protocol to substitute params
> in server" (prepare/execute).
[...]
Can this be the cause of a huge loss of perf? I have the following query in
a Perl script using DBI + DBD::Pg, AutoCommit => 0:
SELECT stats_put_sources(?, ?, int4(?), int4(?))
This syntax runs almost 10x faster than:
SELECT stats_put_sources(?, ?, ?::int4, ?::int4)

I can find where is the real difference, maybe this protocol stuff. In the
facts the speed difference was so great that there were no need to use
EXPLAIN to know what was the best option.

N.B.: The stats_put_source(varchar,varchar,int4,int4) function is a rewrite
of the INSERT OR UPDATE function described in the docs.

Thanks,
--
MaXX