Thread: Reallife szenario for GEQO

I am currently searching some resources, why and where the PostgreSQL
GEQO-Engine improves large join queries. The theoretical background is
clear, but i need some real life szenarios, where the GEQO-Engine beats
other solutions. Have anyone made such a comparison, or have experiences
which szenarios, where the GEQO has improved the query plan generation
in contrast to "traditional" solutions? This will be very helpful (and
interesting) ....

Thanks in advance,

   Bernd

On Tuesday 13 January 2004 09:50, Bernd Helmle wrote:
> Have anyone made such a comparison, or have experiences
> which szenarios, where the GEQO has improved the query plan generation
> in contrast to "traditional" solutions? This will be very helpful (and
> interesting) ....

I don't think it's that it provides a better plan, just that it comes up with
a quicker solution. Searching all possible paths is not practical for a large
number of tables, so you need another approach.

Google for "travelling salesman problem" for discussion of the sort of
thinking behind it.

--
  Richard Huxton
  Archonet Ltd

Richard Huxton wrote:
> On Tuesday 13 January 2004 09:50, Bernd Helmle wrote:
>
>
>
> I don't think it's that it provides a better plan, just that it comes up with
> a quicker solution. Searching all possible paths is not practical for a large
> number of tables, so you need another approach.
>

Aggreed. That was my opinion, too. Sorry for my unclear statement.

> Google for "travelling salesman problem" for discussion of the sort of
> thinking behind it.
>

That was the first thing i've done. I understand the theoretical
background, but i need a practical scenario, to show:

QUERY A with GEQO
QUERY A without GEQO

And then compare the specific query plan generation efficency.

In this case I have to think about a practical database scenario, to
execute such queries. If i have enough tables i can join against, i
should see an improvement, in theory.

But what means "enough tables"?

So i wonder, if anyone had done such a comparison already.

Thanks for your reply,

   Bernd

Bernd Helmle <mailings@oopsware.de> writes:
> But what means "enough tables"?

GEQO_THRESHOLD or more.  You can set that anywhere you like (I think the
default is 11 or 12, which is about where the standard exhaustive-search
planner becomes painfully slow).

            regards, tom lane

Tom Lane wrote:
> Bernd Helmle <mailings@oopsware.de> writes:
>
>>But what means "enough tables"?
>
>
> GEQO_THRESHOLD or more.  You can set that anywhere you like (I think the
> default is 11 or 12, which is about where the standard exhaustive-search
> planner becomes painfully slow).
>
>             regards, tom lane
>

Thank you Tom, that's helpful.

   Bernd