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). -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
