Re: pgsql vs mysql

Guido Neitzer wrote:
> On 11.07.2006, at 19:36 Uhr, Merlin Moncure wrote:
>
> >As to preparing
> >statements, I agree in principle although I don't know if that is a
> >good argument not to make the non-paramaterized interface more
> >powerful.
>
> It is not, as prepared statements have the problem that they are only
> optimized once and very generically and without actual knowledge of
> the parameter content, this is just useless.
>
> I had the problem a few months ago, where my app server plugin and
> the jdbc driver used prepared statements for selecting stuff from the
> database. Most of the time, indexes weren't used at all, so
> PostgreSQL performance was the worst I've ever seen in this environment.

I'm pretty excited about this idea of yours on how to fix this problem.
Does it involve the histogram at all?

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.

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

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.