Re: temporarily deactivate an index - Mailing list pgsql-general

Viktor Rosenfeld <rosenfel@informatik.hu-berlin.de> writes:
> That works, but I'm still looking for another way to deactivate the
> index.  The reason being, that my query load is randomly generated by
> a Java program and I don't want to go and change the SQL compiler.

Well, you're going to have to change *something* on the client side,
assuming you want this behavioral change to affect only some queries.

Otherwise, in versions that have CREATE INDEX CONCURRENTLY you could
mark the index not ready to use yet (see pg_index.indisvalid or
pg_index.indisready depending on version).

Another line of attack, which I think only works in 8.3 and up,
is to make a planner plugin that disables specific indexes from
being considered.  In fact I think I'd made a toy one of those
last year [ rummages... ]  Yeah, here it is.  Code attached.
No documentation, but basically you'd build it using pgxs and
then do
    LOAD '$libdir/planignoreindex';
    SET ignore_index = name-of-index;
Again this would be difficult to do without any client changes.

            regards, tom lane

#include "postgres.h"

#include "fmgr.h"
#include "catalog/namespace.h"
#include "optimizer/plancat.h"
#include "optimizer/planner.h"
#include "utils/builtins.h"
#include "utils/guc.h"


PG_MODULE_MAGIC;

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

static char *ignore_index = NULL;

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 removes any index that matches ignore_index
 * by name.
 */
static void
my_get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent,
                     RelOptInfo *rel)
{
    RangeVar   *relrv;
    Oid            indexoid;
    ListCell   *ilist;
    ListCell   *prev;

    /* Do nothing unless ignore_index has been set */
    if (ignore_index == NULL || *ignore_index == '\0')
        return;

    /*
     * Look up the index.  No complaint if not found.
     *
     * Note: doing this lookup again each time is a bit inefficient, but
     * for the contexts in which this plugin would be used, it probably
     * isn't worth making this faster.
     */
    relrv = makeRangeVarFromNameList(stringToQualifiedNameList(ignore_index));
    indexoid = RangeVarGetRelid(relrv, true);
    if (!OidIsValid(indexoid))
        return;

    /* If it's in the rel's list of indexes, delete it */
    prev = NULL;
    foreach(ilist, rel->indexlist)
    {
        IndexOptInfo *ind = (IndexOptInfo *) lfirst(ilist);

        if (ind->indexoid == indexoid)
        {
            rel->indexlist = list_delete_cell(rel->indexlist, ilist, prev);
            break;                /* can't be more than 1 match */
        }
        prev = ilist;
    }
}


/*
 * _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 ignore_index accessible through GUC */
    DefineCustomStringVariable("ignore_index",
                               "Name of an index to ignore",
                               "",
                               &ignore_index,
                               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;
}