Prepared statements v2

[max-hoffman]

Prepared statements currently divide analysis between two sections in a best-effort attempt to run analyzer rules ahead of time. When successful, EXECUTE runs fewer rules. This makes many queries faster but is currently subject to correctness and performance issues:

1. We cache table schemas that may be stale (including ASOF bindings)
2. Complex expression and reference types are hard to update after binding.
3. Pushdown and join planning are two of the most expensive rules but best done after binding.

Compensating goals might look like:

1. Flush cached plan when we detect schema change for a table in plan.
2. Do not attempt to cache plans with ambiguous expression types.
3. Differentiate "general" plan from "custom" plans. A general plan is the result of join planning and pushdown assuming average selectivity values. A custom plan is the result of join planning and pushdown using a specific set of binding literals. We prefer the "general" plan when the time saved avoiding re-planning exceeds the execution latency improvement of a custom plan. Postgres by default collects latencies for 5 custom plans to compare to the general plan.

Individually, these features involve:

1. Check tables for schema compliance when copying a cached plan. For example, if we use mydb.xy and mydb.uv in a cached plan, we should verify that the schemas of xy and uv at plan creation time are compliant with the current values. Equivalent check for ASOF bindings vs the root value table schemas at caching time.

2. Binding expressions either inherit type casts or abort plan caching. Two examples where we insert type casts and continue: insert into xy (x) values ($1), select 2 + $1. Two examples where the scalar expression type is ambiguous: select 1 where $1 = $2, select $1 + $2.

3. The general plan copies and direct-substitutes bindings literals for execution. Custom plans rerun analysis (or only exploration, see below). Ambiguous plans re-run analysis. We use plan costs to trade-off the general cached plan vs. re-running analysis. Top-level query costs are not currently available.

"General" and "custom" plans could share an intermediate phase of analysis between AST conversion and join planning when schemas and expression types are valid. We have to move the index-selection half of pushdown into costing and the filter-pushdown half into plan building before this will work.

[max-hoffman]

moving to google doc
https://docs.google.com/document/d/1e1xfO8tVYNAPn3G_gh5-0pzDBqyRyoti-8KwLfu9ZlY/edit