News for hackers. Stuff that matters.
Re:Following a Select Statement Through Postgres Internals by mvc on Monday October 13, 2014 @01:32PM (#8449636)
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.

Re:Following a Select Statement Through Postgres Internals by fdr on Monday October 13, 2014 @01:43PM (#8449682)
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 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.
Re:Following a Select Statement Through Postgres Internals by cheez on Monday October 13, 2014 @02:37PM (#8449922)
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.