pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPerl Binding affects speed?
4+ messages / 3 participants
[nested] [flat]
* Perl Binding affects speed?
@ 2011-02-25 03:02 Sam Wong <sam@hellosam.net>
2011-02-25 12:25 ` Re: Perl Binding affects speed? Marti Raudsepp <marti@juffo.org>
0 siblings, 1 reply; 4+ messages in thread
From: Sam Wong @ 2011-02-25 03:02 UTC (permalink / raw)
To: pgsql-performance
I run into performance problem when I pass the condition/variable in binding
ways, however if I put them in the query string it's absolutely fine.
----
Here is my table and index:
CREATE TABLE shipment_lookup
(
shipment_id text NOT NULL,
lookup text NOT NULL
);
CREATE INDEX shipment_lookup_prefix
ONshipment_lookup
USING btree
(upper(lookup));
----
I have 10 million rows in the table.
* My query is:
$dbh->selectall_arrayref("SELECT * from shipment_lookup WHERE (UPPER(lookup)
LIKE '0GURG5YGVQA9%')");
Here is the explain I get by using Perl and pgAdmin III.
Index Scan using shipment_lookup_prefix on shipment_lookup (cost=0.01..5.00
rows=921 width=28)
Index Cond: ((upper(lookup) >= '0GURG5YGVQA9'::text) AND (upper(lookup) <
'0GURG5YGVQA:'::text))
Filter: (upper(lookup) ~~ '0GURG5YGVQA9%'::text)
Index is used, and it just takes 50ms to execute. So far so good.
* 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.
So what would be the issue...? I can't isolate if it's the Perl or pgsql.
Thanks,
Sam
----
Version Info:
Postgresql: "PostgreSQL 8.4.5, compiled by Visual C++ build 1400, 32-bit" on
Windows 2003
Perl: 5.10.1
DBD::Pg: 2.17.2
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Perl Binding affects speed?
2011-02-25 03:02 Perl Binding affects speed? Sam Wong <sam@hellosam.net>
@ 2011-02-25 12:25 ` Marti Raudsepp <marti@juffo.org>
2011-02-25 12:58 ` Re: Perl Binding affects speed? Martin Kjeldsen <martin@martinkjeldsen.dk>
0 siblings, 1 reply; 4+ messages in thread
From: Marti Raudsepp @ 2011-02-25 12:25 UTC (permalink / raw)
To: Sam Wong <sam@hellosam.net>; +Cc: pgsql-performance
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.
Regards,
Marti
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Perl Binding affects speed?
2011-02-25 03:02 Perl Binding affects speed? Sam Wong <sam@hellosam.net>
2011-02-25 12:25 ` Re: Perl Binding affects speed? Marti Raudsepp <marti@juffo.org>
@ 2011-02-25 12:58 ` Martin Kjeldsen <martin@martinkjeldsen.dk>
2011-02-25 13:24 ` Re: Perl Binding affects speed? Sam Wong <sam@hellosam.net>
0 siblings, 1 reply; 4+ messages in thread
From: Martin Kjeldsen @ 2011-02-25 12:58 UTC (permalink / raw)
To: Marti Raudsepp <marti@juffo.org>; +Cc: Sam Wong <sam@hellosam.net>; 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Perl Binding affects speed?
2011-02-25 03:02 Perl Binding affects speed? Sam Wong <sam@hellosam.net>
2011-02-25 12:25 ` Re: Perl Binding affects speed? Marti Raudsepp <marti@juffo.org>
2011-02-25 12:58 ` Re: Perl Binding affects speed? Martin Kjeldsen <martin@martinkjeldsen.dk>
@ 2011-02-25 13:24 ` Sam Wong <sam@hellosam.net>
0 siblings, 0 replies; 4+ messages in thread
From: Sam Wong @ 2011-02-25 13:24 UTC (permalink / raw)
To: 'Martin Kjeldsen' <martin@martinkjeldsen.dk>; 'Marti Raudsepp' <marti@juffo.org>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2011-02-25 13:24 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-02-25 03:02 Perl Binding affects speed? Sam Wong <sam@hellosam.net>
2011-02-25 12:25 ` Marti Raudsepp <marti@juffo.org>
2011-02-25 12:58 ` Martin Kjeldsen <martin@martinkjeldsen.dk>
2011-02-25 13:24 ` Sam Wong <sam@hellosam.net>
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