Re: Is there a way to temporarily disable a index

Benedikt Grundmann wrote
> 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

Worth double-checking in test but...

BEGIN;
DROP INDEX ...;
EXPLAIN ANALYZE SELECT ...
ROLLBACK;

Index dropping is transactional so your temporary action lasts until you
abort said transaction.

Though given your knowledge limitations this really isn't an improvement...

Catalog hacking could work but not recommended (nor do I know the proper
commands and limitations). Do you need the database/table to accept writes
during the testing period?

You can avoid all indexes, but not a named subset, using a configuration
parameter.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Is-there-a-way-to-temporarily-disable-a-index-tp5811249p5811290.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.