-----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 g...@turnstep.com 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-----