Re: pgsql vs mysql

On Tue, 2006-07-11 at 21:11, Alvaro Herrera wrote:
> Maybe we could check the MCVs, and store those for which the plan would
> be A (say indexscan) and those for which it would be B (say bitmap
> indexscan), etc; so we'd save more than one plan and choose at execution
> time depending on the actual parameters. For all values not on the
> MCV set, use the same plan as the least common of the MCVs.

I think Tom had some plans to more aggressively cache query plans. I
wonder if this concept could be extended to cache some kind of decision
trees for query templates, i.e. make a decision tree for a specific SQL
fragment containing different plans based on the values of the contained
parameters. This could be made to match sqls with constant values too,
by considering the constant values to be potential parameters. Then a
next call with a different (constant or parameterized) value would still
match the cached decision tree...

The decisions could be done based on value ranges of the parameters, or
value lists, or whatever the planner finds to make a plan difference
based on the statistics it has when planning.

The whole thing is like discovering the main plan possibilities once,
save it, and then choose one on runtime based on the actual value of the
parameters which have been decided to matter on planning time. This
would reduce a lot the planning time (you have to do it once for a type
of query), and make the actually chosen plan dependent on the parameters
too with little overhead.

>
> But of course, I know nothing about the optimizer so I'm not sure if
> this makes any sense at all.

Well, this applies to me too, with the same conclusion :-)

Cheers,
Csaba.