-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160
> my $sth = $dbh->prepare(qq{SELECT a, b, c FROM t WHERE Lower(a) LIKE ?});
> $sth->execute($q);
>
> The above takes 3 to 4 seconds. Pretty much identical queries
> with identical results by vastly difference performance.
>
> Isn't this a DBI issue instead of a Postgres issue?
It is neither. A query with a ? in it can be *anything*, so Postgres
must design a plan that can handle it (in this case, a plan that
does a sequential scan). It cannot know in advance if it the query
is to be anchored or not. It's also not an option to expect DBI to
keep track of what arguments you pass and create new plans based
on the arguments.
When you do that prepare and execute, DBD::Pg is asking Postgres to
create a prepared statement, such that it can send just the arguments,
and not the full statement, each time execute() is called. You can
force it to *not* do so by issuing:
$dbh->{pg_server_prepare} = 0;
You can also do this at the statement handle generation time: see the
DBD::Pg docs for more information.
There are a lot of other considerations and things to think about when
doing a "LIKE $1" in Postgres, and in general the use of an unconstrained
parameter indicates a need for something like full-text search, but the
above attribute should allow you to see the same behavior on both of your
original queries.
- --
Greg Sabino Mullane [email protected]
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201202132246
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----
iEYEAREDAAYFAk852mcACgkQvJuQZxSWSsh/TACgkmvbALYR4WsvaglWn1nWD050
8jEAoPuY/Imn0eYOGll1+gWY8UHdKHwz
=UI4w
-----END PGP SIGNATURE-----
