[prev in list] [next in list] [prev in thread] [next in thread] 

List:       pgsql-performance
Subject:    Re: [PERFORM] Perl Binding affects speed?
From:       "Sam Wong" <sam () hellosam ! net>
Date:       2011-02-25 13:24:16
Message-ID: 01e601cbd4ef$4eb702c0$ec250840$ () hellosam ! net
[Download RAW message or body]

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


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
[prev in list] [next in list] [prev in thread] [next in thread]