3 ms·

The Ruby AST -> SQL String -> PG SQL AST -> Plan process is a nice reminder of why we should use prepared queries wherever possible.

• mvc 12y ago
  I'm not a postgres hacker but as I understand it, query plans generated at "exec time" can be better than those generated at "prepare time" because they have more information to hand about the expected size of nodes within the plan. There are other reasons to use prepared queries but if performance is your only one it may not be worth it unless your query is either very complex, or runs in a tight loop.
  • fdr 12y ago
    You are absolutely right. That's because when a parametric query arrives, the parameters are unbound and the planner cannot take advantage of fit-to-purpose selectivity estimation. It must instead estimate generically. Newish versions of Postgres (9.2+ I believe) try to paper over this surprising effect by re-planning queries a few times to check for cost stability before really saving the plan. It has proved very practical. See http://www.postgresql.org/docs/9.2/static/sql-prepare.html's http://www.postgresql.org/docs/9.2/static/sql-prepare.html's notes section, reproduced here: Notes If a prepared statement is executed enough times, the server may eventually decide to save and re-use a generic plan rather than re-planning each time. This will occur immediately if the prepared statement has no parameters; otherwise it occurs only if the generic plan appears to be not much more expensive than a plan that depends on specific parameter values. Typically, a generic plan will be selected only if the query's performance is estimated to be fairly insensitive to the specific parameter values supplied.
  • cheez 12y ago
    The way you describe it, could prepared queries still delay the creation of the plans till exec time? There are some things that cause plans to change like sort order but usually, if they are just parameterized, no need to do so.
• sanderjd 12y ago
  That's one possibility. Another would be for a client to transfer an encoding of a query that can be cheaply decoded, instead of requiring parsing. The translation of a query from an application AST to that encoding could skip the SQL representation altogether! Of course this sort of approach would give up the (debatable) interoperability capabilities of SQL and I'm not sure parsing is enough of a bottleneck for it to really be worthwhile. A "binary SQL" spec would also be interesting (and maybe exists already?).
  • yangyang 12y ago
    The time taken parsing the query is insignificant compared to the time planning. Not sure if it's exactly what you're getting at, but SQL Server has a "USE PLAN" hint that lets you set the entire query plan (using an XML string). PostgreSQL's policy is strongly against hints, so we're unlikely to see anything like that in Postgres.
    • sanderjd 12y ago
      I was purely talking about "pre-compiling" the queries themselves, so it would only effect parsing, not planning. I have no trouble at all believing that this would be a totally negligible improvement.