Re: eWeek Poll: Which database is most critical to

F Harvell <fharvell(at)fts(dot)net> writes:
> The query plan is not going to be interested at all in
> the literal value of the parameters and therefore will be the same for
> any query of the same form.

Unfortunately, this is completely false.

> For example, from above:

> SELECT shirt, color, backorder_qty FROM garments WHERE color like
> 'BLUE%'

> should become something on the order of:

> SELECT shirt, color, backorder_qty FROM garments WHERE color like
> '{param0}%'

You managed to pick an example that's perfectly suited to demolish your
assertion. The query with "color like 'BLUE%'" can be optimized into an
indexscan (using index quals of the form "color >= 'BLUE' and color <
'BLUF'), at least in C locale. The parameterized query cannot be
optimized at all, because the planner cannot know whether the
substituted parameter string will provide a left-anchored pattern.
What if param0 contains '_FOO' at runtime? An indexscan will be
useless in that case.

In general, Postgres' query plans *do* depend on the values of
constants, and it's not always possible to produce an equally good plan
that doesn't assume anything about constants. This is why I think it's
a lousy idea for the system to try to automatically abstract a
parameterized query plan from the actual queries it sees. On the other
hand, an application programmer will have a very good idea of which
parts of a repeated query are really constant and which are parameters.
So what we really need is preparable parameterized queries, wherein the
application tells us what to parameterize, rather than having to guess
about it.

regards, tom lane