From: Sam Wong <sam@hellosam.net>
To: 'Martin Kjeldsen' <martin@martinkjeldsen.dk>
To: 'Marti Raudsepp' <marti@juffo.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Perl Binding affects speed?
Date: Fri, 25 Feb 2011 21:24:16 +0800
Message-ID: <01e601cbd4ef$4eb702c0$ec250840$@hellosam.net> (raw)
In-Reply-To: <E520CD71-04E2-4B07-B28E-50255585EF8B@martinkjeldsen.dk>
References: <019101cbd498$7192ba70$54b82f50$@hellosam.net>
<AANLkTi=ZUN+T3h+QT1ff1krbaxj62qVpQda4ECEjo1Ou@mail.gmail.com>
<E520CD71-04E2-4B07-B28E-50255585EF8B@martinkjeldsen.dk>
From: Martin Kjeldsen, Sent: 2011/2/25, 20:59
>
> On 25/02/2011, at 13.25, Marti Raudsepp wrote:
>
> > On Fri, Feb 25, 2011 at 05:02, Sam Wong <sam@hellosam.net> wrote:
> >> * But if I do this - using binding:
> >> $dbh->selectall_arrayref("SELECT * from shipment_lookup WHERE
> >> (UPPER(lookup) LIKE ?)", undef, '0GURG5YGVQA9%'); It took 10 seconds
> >> to finish the query, just like it was using full table scan instead!
> >> Even though the 'explain' shows the same query plan.
> >
> > This is a pretty common shortcoming with placeholders. Since planning
> > of parameterized queries is done *before* binding parameters, the
> > planner has no knowledge of what the "?" placeholder actually is. Thus
> > it often gets the selectivity statistics wrong and produces worse
> > plans for your values.
> >
> > AFAIK the only workaround is to not use variable binding in these
> > cases, but escape and insert your variables straight it into the SQL
> > query.
>
> Instead of not using the placeholder syntax you can use:
>
> local $dbh->{pg_server_prepare} = 0;
>
> which disables prepared queries serverside in the current scope and
> therefore doesn't have the late variable binding issue, but allows you to
> avoid SQL injection attacks.
>
Thanks, I will look into that.
Sam
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: sam@hellosam.net, martin@martinkjeldsen.dk, marti@juffo.org
Subject: Re: Perl Binding affects speed?
In-Reply-To: <01e601cbd4ef$4eb702c0$ec250840$@hellosam.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox