Improving GEQO

boix <boix@uclv.cu> writes:

Hello, my partner and me are working with the goal of improve the GEQO's
performance, we tried with Ant Colony Optimization, but it does not
improve, actually we are trying with a new variant of Genetic Algorithm,
specifically Micro-GA. This algorithm finds a better solution than GEQO
in less time, however the total query execution time is higher. The
fitness is calculated by geqo_eval function. Does anybody know why this
happens?

Well, for one thing, you can't just do this:

+ aux = aux1;

without totally confusing all your subsequent steps.

You'd want to copy the pointed-to data, likely, not the pointer.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

We follow your advice, our goal is improve the quality of the solution
and we made it,however the total query execution time is higher.
Regards.

On Wed, May 27, 2015 at 3:06 PM, boix <boix@uclv.cu> wrote:

Hello, my partner and me are working with the goal of improve the GEQO's
performance, we tried with Ant Colony Optimization, but it does not improve,
actually we are trying with a new variant of Genetic Algorithm, specifically
Micro-GA. This algorithm finds a better solution than GEQO in less time,
however the total query execution time is higher. The fitness is calculated
by geqo_eval function. Does anybody know why this happens?

We attach the patch made with the changes in postgresql-9.2.0.

can you submit more details? for example 'explain analyze' (perhaps
here: http://explain.depesz.com/) of the plans generated GEQO vs GA vs
stock? It sounds like you might be facing an estimation miss which is
not really an issue a better planner could solve.

That said, assuming you're getting 'better' plans in less time suggest
you might be on to something.

merlin

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Fri, May 29, 2015 at 12:59 AM, Merlin Moncure <mmoncure@gmail.com> wrote:

On Wed, May 27, 2015 at 3:06 PM, boix <boix@uclv.cu> wrote:

Hello, my partner and me are working with the goal of improve the GEQO's
performance, we tried with Ant Colony Optimization, but it does not

improve,

actually we are trying with a new variant of Genetic Algorithm,

specifically

Micro-GA. This algorithm finds a better solution than GEQO in less time,
however the total query execution time is higher. The fitness is

calculated

by geqo_eval function. Does anybody know why this happens?

We attach the patch made with the changes in postgresql-9.2.0.

can you submit more details? for example 'explain analyze' (perhaps
here: http://explain.depesz.com/) of the plans generated GEQO vs GA vs
stock? It sounds like you might be facing an estimation miss which is
not really an issue a better planner could solve.

That said, assuming you're getting 'better' plans in less time suggest
you might be on to something.

merlin

What sort of tests are you running? I suspect that anything which is not
too well thought out and tested might end up performing well only on small
subset of tests.

Also, what is the consistency of the plans generated? If you are only
targeting planning time, I feel it might be of lesser value. However, if
you can get large order joins to be executed in a near optimal (brute
force) solution, you might be on to something.

Something I would like to see done is remove the dead code that is present
in existing GEQO. This might alone lead to lesser compilation times.

--
Regards,

Atri
*l'apprenant*