We use Postgres at work, as the back end database for the New Project. (The same project I'm writing the compiler for) It's nice, has the features we need (need those views and triggers), and is snappy. Well... mostly snappy.
I've been finding that my SQL is just... not fast. Slow, in fact. Really, really slow. And the weird thing is that it isn't slow when I EXPLAIN the stuff. Not slow in a "six orders of magnitude less slow" sense. Which, you've got to admit, is getting into the darned significant range. I did the sensible thing here--I asked someone else what the heck's going on.
It turns out that my SQL (nearly all of it, dammit!) trips over an interesting quirk of Postgres' query optimizer. Y'see, Postgres' optimizer only optimizes LIKE conditions if it gets handed a string constant, so:
foo LIKE 'F%'
gets optimized, while
foo LIKE $1
where $1 is 'F%' doesn't. Now, all my SQL uses LIKE, and all of it uses placeholders, so I can use the PQexecParams call rather than PQexec. Doing it this way means I don't have to do any string escaping or suchlike stuff, which I'm all for not doing. The speed hit, though... Yow.
So, be aware, LIKE doesn't get optimized when you've a non-string-constant you're LIKE-ing against. (Those of you who switched over to PQexecParams to avoid SQL injection attacks for your apps may now groan if you like :)
Does Postgres do separate prepare/execute phases? If yes, then you're stuck I guess. If not, though, you could add a layer that does quoting+interpolation when it sees a LIKE transparently. Apps using an abstraction layer (as in DBI module) could immediately profit. Hmm, might be a suggestion worth making to the DBD::pg maintainer..
Posted by: Aristotle at September 11, 2004 12:22 PMDBD::Pg already does its own interpolation. If you run it under DBI_TRACE=2, you'll see that it sends fully expanded SQL to postgres. So, it won't be affected by this find.
-Dom
Posted by: Dominic Mitchell at September 11, 2004 01:39 PMPosgres does separate prepare and execute phases, yep, at least in this case. (I looked at the code in the Postgres 7.4.5 tree -- it sends the SQL down for preparation, then executes the resulting prepared statement with the passed parameters) No big deal in general, but it does make LIKE processing a bit sub-optimal in this case.
No big deal, I'll just have to do a little preprocessing of the code first. (Granted, in Parrot assembly, which makes it a little troublesome, but not that bad)
Posted by: Dan at September 11, 2004 02:33 PM