Re: Optimizer hook - Mailing list pgsql-patches

Well, It seems that I probably do not understand something quite well or
my explanation
is not clear. There is some example code of my idea in the attachment.

There is a function jos_search which has nearly the same code as
make_rel_from_joinlist.
The example tries geqo first and then the regular algorithm. At the
first sight it seems that
it is possible to do the same with the hook placed to the current
decision point. However,
the execution flow is slightly different.

1.) example in dummy.c
---
It first uses geqo only to find the cheapest path also for all the recursive
calls in jos_search. Afterwards, the same is executed using the regular
algorithm
also for all the recursive calls in jos_search. The whole result path
for the query
will be produced only by either geqo or regular algorithm.

2.) placing the hook to the current decision point and trying both
geqo®ular
at that place.
---
Parts of the result path might be found by geqo and parts of it by
regular algorithm.

The problem here is that regular algorithm will find the best plan every
time it finishes. However, the above is supposed to be used for the
algorithms
that none of them is supposed to find the best solution.

> That would be an entirely different problem, I think.  If you want to
> replace the *entire* planner, there's already the planner_hook to do that.
>
Replacing the whole planner would need a much more code to be reused without
any change than in this case.

> you can't just willy-nilly replace the code for
> single-relation path selection, at least not without a whole lot of
> changes elsewhere in the planner infrastructure.
>
Would the code in dummy.c work in a way that I expect and explained above?
If there is no way of how to make the code work then it makes no sense
to put the hook to the place I am proposing. It works for me, but I have
not tested that very well yet. If I would swap calls to geqo
and make_one_rel_by_joins it will not work. Therefore there might be
an issue I do not know about yet.
> My thought was that the reason for this hook to exist was simply to
> provide a convenient way to replace only the join search order
> algorithm.
Yes, thats true. I do not have a plan to do something more for now.

Thank you

Regards

Julius Stroffek

#include "postgres.h"

#include "fmgr.h"
#include "optimizer/geqo.h"
#include "optimizer/paths.h"
#include "optimizer/pathnode.h"


PG_MODULE_MAGIC;

void    _PG_init(void);
void    _PG_fini(void);

typedef RelOptInfo * (*jos_function_type) (PlannerInfo *root, int levels_needed, List* initial_rels);

static
RelOptInfo *
jos_search(PlannerInfo *root, List *joinlist, jos_function_type jos_function)
{
     int         levels_needed;
     List       *initial_rels;
     ListCell   *jl;

     /*
      * Count the number of child joinlist nodes.  This is the depth of the
      * dynamic-programming algorithm we must employ to consider all ways of
      * joining the child nodes.
      */
     levels_needed = list_length(joinlist);

     if (levels_needed <= 0)
         return NULL;            /* nothing to do? */

     /*
      * Construct a list of rels corresponding to the child joinlist nodes.
      * This may contain both base rels and rels constructed according to
      * sub-joinlists.
      */
     initial_rels = NIL;
     foreach(jl, joinlist)
     {
         Node       *jlnode = (Node *) lfirst(jl);
         RelOptInfo *thisrel;

         if (IsA(jlnode, RangeTblRef))
         {
             int         varno = ((RangeTblRef *) jlnode)->rtindex;

             thisrel = find_base_rel(root, varno);
         }
         else if (IsA(jlnode, List))
         {
             /* Recurse to handle subproblem */
             thisrel = jos_search(root, (List *) jlnode, jos_function);
         }
         else
         {
             elog(ERROR, "unrecognized joinlist node type: %d",
                  (int) nodeTag(jlnode));
             thisrel = NULL;     /* keep compiler quiet */
         }

         initial_rels = lappend(initial_rels, thisrel);
     }

     if (levels_needed == 1)
     {
         /*
          * Single joinlist node, so we're done.
          */
         return (RelOptInfo *) linitial(initial_rels);
     }
     else
     {
         jos_function(root, levels_needed, initial_rels);
     }
}


static
RelOptInfo *
jos_dummy(PlannerInfo *root, List *joinlist)
{
    RelOptInfo *dynamic_result, *geqo_result;
    List *copy;
    Cost dynamic_cost, geqo_cost;

    copy = list_copy(joinlist);
    elog(LOG, "Starting a join order search \"geqo\"...");
    geqo_result = jos_search(root, copy, geqo);
//    geqo_result = jos_search(root, copy, make_one_rel_by_joins);
    geqo_cost = geqo_result->cheapest_total_path->total_cost;

    copy = list_copy(joinlist);
    elog(LOG, "Starting a join order search \"dynamic\"...");
    dynamic_result = jos_search(root, copy, make_one_rel_by_joins);
//    dynamic_result = jos_search(root, copy, geqo);
    dynamic_cost = dynamic_result->cheapest_total_path->total_cost;


    printf("GEQO cost: %f\n", geqo_cost);
    printf("Dynamic programming cost: %f\n", dynamic_cost);

    if (geqo_cost < dynamic_cost)
        return geqo_result;
    else
        return dynamic_result;
}


void
_PG_init(void)
{
    join_order_search_hook = jos_dummy;
}

void
_PG_fini(void)
{
    join_order_search_hook = 0;
}