From: Sam Wong <sam@hellosam.net>
To: pgsql-performance@postgresql.org
Subject: Perl Binding affects speed?
Date: Fri, 25 Feb 2011 11:02:23 +0800
Message-ID: <019101cbd498$7192ba70$54b82f50$@hellosam.net> (raw)
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
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
Subject: Re: Perl Binding affects speed?
In-Reply-To: <019101cbd498$7192ba70$54b82f50$@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