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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux