Re: Perl Binding affects speed?
От | Martin Kjeldsen |
---|---|
Тема | Re: Perl Binding affects speed? |
Дата | |
Msg-id | E520CD71-04E2-4B07-B28E-50255585EF8B@martinkjeldsen.dk обсуждение исходный текст |
Ответ на | Re: Perl Binding affects speed? (Marti Raudsepp <marti@juffo.org>) |
Ответы |
Re: Perl Binding affects speed?
|
Список | pgsql-performance |
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. Regards, Martin
В списке pgsql-performance по дате отправления: