From: Tom Lane <tgl@sss.pgh.pa.us>
To: Julius Stroffek <Julius.Stroffek@Sun.COM>
Cc: Stefan Kaltenbrunner <stefan@kaltenbrunner.cc>
Cc: pgsql-performance@postgresql.org
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Proposal: Pluggable Optimizer Interface
Date: Mon, 13 Aug 2007 18:24:27 -0400
Message-ID: <23305.1187043867@sss.pgh.pa.us> (raw)
In-Reply-To: <46C0CCDC.7070906@sun.com>
References: <46C0B5E4.4020305@sun.com>
<46C0BD13.107@kaltenbrunner.cc>
<46C0CCDC.7070906@sun.com>
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;
}
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, Julius.Stroffek@Sun.COM, stefan@kaltenbrunner.cc, pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Proposal: Pluggable Optimizer Interface
In-Reply-To: <23305.1187043867@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox