On Monday, December 24, 2012, John Rouillard wrote:
On Mon, Dec 24, 2012 at 06:37:11PM +0000, Richard Neill wrote:
> [...]
> So... problem solved for me: I just have to reindex every few hours.
> BUT, this suggests a few remaining things:
> [...]
> 2. Is there any way to force the planner to use (or ignore) a
> specific index, for testing purposes, short of actually dropping the
> index?
> This would be very useful for debugging, especially given that query
> plans can only really be fully tested on production systems, and
> that dropping indexes is rather a bad thing to do when live
> operation is simultaneously happening on that server!
I believe that:
BEGIN;
drop index ....
explain analyze ...
explain analyze ...
ROLLBACK;
There are two cautions here. One is that doing the drop index takes an access exclusive lock on the table, and so brings all other connections to a screeching halt. That is not much nicer to do on a production system than actually dropping the index, so don't dilly-dally around before doing the rollback. rollback first, then ruminate on the results of the explain.
Also, this will forcibly cancel any autovacuums occurring on the table. I think one of the reasons he needs to reindex so much is that he is already desperately short of vacuuming behavior.
Cheers,
Jeff