Re: Force specific index disuse

Jeff Janes wrote:

> Best case, 'A while' means however long it takes the explain (possibly
> analyze) to run, and for you to then type 'rollback;'
>
> worse case, someone else is already holding an incompatible lock (i.e. any
> lock) on the table, and is going to hang on to it for a long while, so your
> drop index hangs forever waiting to acquire the lock and in the process
> brings all other desired activity (except the one already holding the lock)
> to a screeching halt because they are not allowed to jump the lock queue.
>
> worser case, you forget to enter 'rollback' at all and accidentally commit
> the index drop.

I guess you could write a program to do this for you instead of doing it
interactively. That way,

1. you never forget BEGIN
2. you never mistake ROLLBACK and type COMMIT instead (oops).
3. you can LOCK TABLE before the DROP, with NOWAIT, and if it fails,
just retry later; or you can specify a statement_timeout so that an
upper limit to impact on other queries is. (Reset statement_timeout
after LOCK TABLE is successful, so that the EXPLAIN can take longer if
necessary).

I guess you should use a test server, of course, and that would mostly
free you from concern (3) anyway.

Also: there is, or used to be, a concept of hypothetical indexes in the
planner which could be useful to tools attaching to some hook(s) already
in core. EDB had an "index advisor" tool way back when; I don't know if
it's still alive. I have never tried any of this. I probably wouldn't
run it on a production server anyway ...

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services