Обсуждение: temporarily deactivate an index
Hi, I'm experimenting with different indexes to speed up my queries and I was wondering if it is possible to temporarily deactivate an index, so it won't be considered in the evaluation of query plans. The reason is that dropping and then rebuilding an index takes up time which I would rather not wait. I couldn't find a command to do that, but I'm guessing that it should be possible by manipulating a structure in the catalog? Thanks, Viktor
On Sat, Jun 7, 2008 at 8:05 AM, Viktor Rosenfeld <rosenfel@informatik.hu-berlin.de> wrote: > Hi, > > I'm experimenting with different indexes to speed up my queries and I was > wondering if it is possible to temporarily deactivate an index, so it won't > be considered in the evaluation of query plans. The reason is that dropping > and then rebuilding an index takes up time which I would rather not wait. > > I couldn't find a command to do that, but I'm guessing that it should be > possible by manipulating a structure in the catalog? Try this: begin; drop indexname; explain analyze select ...; rollback;
Hi Scott, Am 07.06.2008 um 16:53 schrieb Scott Marlowe: >> I'm experimenting with different indexes to speed up my queries and >> I was >> wondering if it is possible to temporarily deactivate an index, so >> it won't >> be considered in the evaluation of query plans. The reason is that >> dropping >> and then rebuilding an index takes up time which I would rather not >> wait. >> >> I couldn't find a command to do that, but I'm guessing that it >> should be >> possible by manipulating a structure in the catalog? > > Try this: > > begin; > drop indexname; > explain analyze select ...; > rollback; 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. Cheers, Viktor
On Sat, Jun 7, 2008 at 5:16 PM, Viktor Rosenfeld <rosenfel@informatik.hu-berlin.de> wrote: > Hi Scott, > > Am 07.06.2008 um 16:53 schrieb Scott Marlowe: >>> >>> I'm experimenting with different indexes to speed up my queries and I was >>> wondering if it is possible to temporarily deactivate an index, so it >>> won't >>> be considered in the evaluation of query plans. The reason is that >>> dropping >>> and then rebuilding an index takes up time which I would rather not wait. >>> >>> I couldn't find a command to do that, but I'm guessing that it should be >>> possible by manipulating a structure in the catalog? >> >> Try this: >> >> begin; >> drop indexname; >> explain analyze select ...; >> rollback; > > 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. Sorry, I'm out of ideas. I mean, you can turn off all indexes with set enable_indexscan=off but there's no other way to do it cheaply and in such a fine grained way.
On Sun, Jun 8, 2008 at 1:34 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote: > On Sat, Jun 7, 2008 at 5:16 PM, Viktor Rosenfeld > <rosenfel@informatik.hu-berlin.de> wrote: >>> >>> Try this: >>> >>> begin; >>> drop indexname; >>> explain analyze select ...; >>> rollback; >> >> 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. > > Sorry, I'm out of ideas. I mean, you can turn off all indexes with > set enable_indexscan=off but there's no other way to do it cheaply and > in such a fine grained way. > with "enable_indexscan=off" you're not turning indexes off but putting a high cost in using them... the effect, most of the time, is that indexes will not be used but you can't be sure... the better solution was the first one: dropping the index inside a transaction, execute the explain analyze of the query and rollback the transaction -- regards, Jaime Casanova Soporte y capacitación de PostgreSQL Guayaquil - Ecuador Cel. (593) 87171157
On Sun, Jun 8, 2008 at 7:55 AM, Jaime Casanova <systemguards@gmail.com> wrote: > On Sun, Jun 8, 2008 at 1:34 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote: >> On Sat, Jun 7, 2008 at 5:16 PM, Viktor Rosenfeld >> <rosenfel@informatik.hu-berlin.de> wrote: >>>> >>>> Try this: >>>> >>>> begin; >>>> drop indexname; >>>> explain analyze select ...; >>>> rollback; >>> >>> 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. >> >> Sorry, I'm out of ideas. I mean, you can turn off all indexes with >> set enable_indexscan=off but there's no other way to do it cheaply and >> in such a fine grained way. >> > > with "enable_indexscan=off" you're not turning indexes off but putting > a high cost in using them... the effect, most of the time, is that > indexes will not be used but you can't be sure... > > the better solution was the first one: dropping the index inside a > transaction, execute the explain analyze of the query and rollback the > transaction True. Also there's been some talk lately about how to add query planner controls to postgresql to control the planner. This being an open source project, I think it's pretty reasonable to put the onus of that kind of thing on the user community to take a first whack at before someone spends a lot of time instrumenting it in full in the core.
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;
}
On Sun, Jun 8, 2008 at 11:45 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > 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). > if i mark the index not ready (using pg_index.indisvalid or pg_index.indisready depending on version), will the index be updated if in another transaction i make an insert? -- regards, Jaime Casanova Soporte y capacitación de PostgreSQL Guayaquil - Ecuador Cel. (593) 87171157
Jaime Casanova escribió: > if i mark the index not ready (using pg_index.indisvalid or > pg_index.indisready depending on version), will the index be updated > if in another transaction i make an insert? Yes (in fact that's the whole point of having an index marked "not valid/ready"). -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support