Is there a way to temporarily disable a index

From: Benedikt Grundmann <bgrundmann(at)janestreet(dot)com>
To: PostgreSQL-Dev <pgsql-hackers(at)postgresql(dot)org>
Subject: Is there a way to temporarily disable a index
Date: 2014-07-11 08:10:06
Message-ID: CADbMkNO_2xoy2eKTeKSyVi9A2Kh350=hp4JrwPyMOFw2-tYbDw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

That is it possible to tell the planner that index is off limits i.e. don't
ever generate a plan using it?

Rationale: Schema changes on big tables. I might have convinced myself /
strong beliefs that for all queries that I need to be fast the planner does
not need to use a given index (e.g. other possible plans are fast enough).
However if I just drop the index and it turns out I'm wrong I might be in a
world of pain because it might just take way to long to recreate the index.

I know that I can use pg_stat* to figure out if an index is used at all.
But in the presense of multiple indices and complex queries the planner
might prefer the index-to-be-dropped but the difference to the alternatives
available is immaterial.

The current best alternative we have is to test such changes on a testing
database that gets regularly restored from production. However at least in
our case we simply don't know all possible queries (and logging all of them
is not an option).

Cheers,

Bene

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Ali Akbar 2014-07-11 08:36:55 Re: [REVIEW] Re: Fix xpath() to return namespace definitions
Previous Message Simon Riggs 2014-07-11 07:27:41 Re: tweaking NTUP_PER_BUCKET