Re: Proposal: Pluggable Optimizer Interface

Julius Stroffek <Julius.Stroffek@Sun.COM> writes:
> I understood that if the user creates his own implementation of the
> planner which can be stored in some external library, he have to provide
> some C language function as a "hook activator" which will assign the
> desired value to the planner_hook variable. Both, the activator function
> and the new planner implementation have to be located in the same
> dynamic library which will be loaded when CREATE FUNCTION statement
> would be used on "hook activator" function.

You could do it that way if you wanted, but a minimalistic solution is
just to install the hook from the _PG_init function of a loadable
library, and then LOAD is sufficient for a user to execute the thing.
There's a small example at
http://archives.postgresql.org/pgsql-patches/2007-05/msg00421.php

Also, having the loadable module add a custom GUC variable would likely
be a preferable solution for control purposes than making specialized
functions.  I attach another small hack I made recently, which simply
scales all the planner's relation size estimates by a scale_factor GUC;
this is handy for investigating how a plan will change with relation
size, without having to actually create gigabytes of test data.

> There are more things in the proposal as a new pg_optimizer catalog and
> different way of configuring the hooks. However, this thinks are not
> mandatory for the functionality but are more user friendly.

Granted, but at this point we are talking about infrastructure for
planner-hackers to play with, not something that's intended to be a
long-term API for end users.  It may or may not happen that we ever
need a user API for this at all.  I think a planner that just "does the
right thing" is far preferable to one with a lot of knobs that users
have to know how to twiddle, so I see this more as scaffolding on which
someone can build and test the replacement for GEQO; which ultimately
would go in without any user-visible API additions.

            regards, tom lane

#include "postgres.h"

#include "fmgr.h"
#include "commands/explain.h"
#include "optimizer/plancat.h"
#include "optimizer/planner.h"
#include "utils/guc.h"


PG_MODULE_MAGIC;

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

static double scale_factor = 1.0;

static void my_get_relation_info(PlannerInfo *root, Oid relationObjectId,
                                 bool inhparent, RelOptInfo *rel);


/*
 * Get control during planner's get_relation_info() function, which sets up
 * a RelOptInfo struct based on the system catalog contents.  We can modify
 * the struct contents to cause the planner to work with a hypothetical
 * situation rather than what's actually in the catalogs.
 *
 * This simplistic example just scales all relation size estimates by a
 * user-settable factor.
 */
static void
my_get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent,
                     RelOptInfo *rel)
{
    ListCell   *ilist;

    /* Do nothing for an inheritance parent RelOptInfo */
    if (inhparent)
        return;

    rel->pages = (BlockNumber) ceil(rel->pages * scale_factor);
    rel->tuples = ceil(rel->tuples * scale_factor);

    foreach(ilist, rel->indexlist)
    {
        IndexOptInfo *ind = (IndexOptInfo *) lfirst(ilist);

        ind->pages = (BlockNumber) ceil(ind->pages * scale_factor);
        ind->tuples = ceil(ind->tuples * scale_factor);
    }
}


/*
 * _pg_init()            - library load-time initialization
 *
 * DO NOT make this static nor change its name!
 */
void
_PG_init(void)
{
    /* Get into the hooks we need to be in all the time */
    get_relation_info_hook = my_get_relation_info;
    /* Make scale_factor accessible through GUC */
    DefineCustomRealVariable("scale_factor",
                             "",
                             "",
                             &scale_factor,
                             0.0001,
                             1e9,
                             PGC_USERSET,
                             NULL,
                             NULL);
}


/*
 * _PG_fini()            - library unload-time finalization
 *
 * DO NOT make this static nor change its name!
 */
void
_PG_fini(void)
{
    /* Get out of all the hooks (just to be sure) */
    get_relation_info_hook = NULL;
}