Re: Extremely irregular query performance

On Thu, Jan 12, 2006 at 03:23:14PM -0500, Jean-Philippe Cote wrote:
>
>
> Can I actully know whether a given plan is excuted with GEQO on ?
> In other words, if I launch 'explain <query>', I'll get a given plan, but if I re-launch
> the <query> (withtout the 'explain' keyword), could I get a different
> plan given that GEQO induces some randomness ?
>
> >Is it the plan that is different in the fastest case with GEQO or is it
> >the time needed to plan that is causing the GEQO to beat the exhaustive
> >search?
>
GEQO will be used if the number of joins is over the GEQO limit in
the configuration file. The GEQO process is an iterative random
process to find an query plan. The EXPLAIN results are the plan for that
query, but not neccessarily for subsequent runs. GEQO's advantage is a
much faster plan time than the exhaustive search method normally used.
If the resulting plan time is less than the exhaustive search plan time,
for short queries you can have the GECO run more quickly than the
exhaustive search result. Of course, if you PREPARE the query the plan
time drops out.

Ken