Re: same query, two different approaches, vastly different performance
Puneet Kishor <[email protected]>
| Newsgroups | gmane.comp.lang.perl.modules.dbi.general |
|---|---|
| Message-ID | <[email protected]> |
On Feb 13, 2012, at 7:59 PM, Mike Blackwell wrote: > Yes. On the other hand, parameters are inherently safe from injection attacks and other data issues, so which to use depends on the situation. Yup, I am aware of this. In this case, definitely, there is a difference of a couple of hundred ms for a query vs. a couple of three seconds for the same query. So, I am going to stick with the inline params. > > I'm guessing you already know about EXPLAIN. There's an auto-explain extension in the contrib folder that might help you see what's going on as well. > Yeah, I will dig into it to see if I can figure out something. However, I am not DBI-savvy enough to understand it all. Thankfully, after much finagling, trials, errors, and various different kinds of indexes, I am now getting the results in usable amount of time. > > > On Feb 13, 2012, at 19:16, Puneet Kishor <[email protected]> wrote: > > > On Feb 13, 2012, at 7:12 PM, Mike Blackwell wrote: > >> Check the postgres-performance list archives. I believe the difference is due to the query planner having less info available with parameters vs inline args. >> > > > > ahhhh... that makes sense. I would like to confirm this, because, if true, then it is a strike against statements prepared with bind values. > > >> >> >> On Feb 13, 2012, at 19:07, Puneet Kishor <[email protected]> wrote: >> >> >> On Feb 13, 2012, at 6:57 PM, David Nicol wrote: >> >>> did you find >>> >>> http://stackoverflow.com/questions/1566717/postgresql-like-query-performance-variations >>> >>> in your research? >> >> >> Thanks for that link, but I don't think that applies in my case. I have two exact queries, with a postfix wildcard search. One, I prepare with the search term embedded in the query. The other is prepared with a bind variable and the search term is added during $sth->execute. >> >> The first one uses the index. The second does a full-table scan. >> >> Strange, but true. >> >> >> >> -- >> Puneet Kishor >