Re: Why does the query planner use two full indexes, when a dedicated partial index exists? (solved?)

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

>
>