From: | Martin Kjeldsen <martin(at)martinkjeldsen(dot)dk> |
---|---|
To: | Marti Raudsepp <marti(at)juffo(dot)org> |
Cc: | Sam Wong <sam(at)hellosam(dot)net>, pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Perl Binding affects speed? |
Date: | 2011-02-25 12:58:55 |
Message-ID: | E520CD71-04E2-4B07-B28E-50255585EF8B@martinkjeldsen.dk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On 25/02/2011, at 13.25, Marti Raudsepp wrote:
> On Fri, Feb 25, 2011 at 05:02, Sam Wong <sam(at)hellosam(dot)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.
Regards,
Martin
From | Date | Subject | |
---|---|---|---|
Next Message | Sam Wong | 2011-02-25 13:24:16 | Re: Perl Binding affects speed? |
Previous Message | Marti Raudsepp | 2011-02-25 12:25:32 | Re: Perl Binding affects speed? |