On Feb 6, 2014, at 2:43 PM, Claudio Freire <klaussfre...@gmail.com> wrote:
> On Thu, Feb 6, 2014 at 4:38 PM, Michael Bayer <mike...@zzzcomputing.com> > wrote: >> On Feb 6, 2014, at 1:31 PM, Claudio Freire <klaussfre...@gmail.com> wrote: >> >>> >>>> There are various performance concerns related to SELECT queries and >>>> solving them depends much on identifying where a particular performance >>>> issue resides. There's overhead on the side of Python/SQLAlchemy >>>> formulating the string statement, theres overhead on the side of passing >>>> it to the database to be parsed (which is by far the most infinitesimal >>>> part) >>> >>> That part is most definitely not always so infinitesimally small. I >>> recall one case (granted, one among so many) of a complex query that >>> was very fast to execute. The query had dozens of joins, so it took >>> considerable planning time on the database side. Planning took around >>> 300ms, where execution took only 25ms. So, having spotted the >>> bottleneck, I switched to using prepared statements (it was a query >>> that was run several times in the session, not millions of times, but >>> several) with different arguments. The benefit was considerable. >> >> ideally the database would know how to cache plans based on the string form >> of the statement. Oracle does this. It appears that Postrgresql does not >> unless you specifically use a prepared statement. > > Well, it cannot. At least not simplistically, because postgres uses > the literals in the statement (the ones likely to change) > to > specialize plan cost, and different values can produce different plans > (depending on stats). For instance, a query with a where clause > "deleted=false" that selects 99% of a table would do a sequential > scan, whereas "deleted=true" might use an index (if there was such an > index). > > They thus don't cache plans unless specifically asked for (with > prepare), because those plans may very well be inferior (they don't > use value-specific knowledge). right, but from an API POV, its more tedious since we have to track the handle on the outside. This could be implemented as a cursor event even in conjunction with a custom execution option and possibly using compiled_cache as well.
signature.asc
Description: Message signed with OpenPGP using GPGMail
