Re: Why does the query planner use two full indexes, when a dedicated partial index exists? (solved?)
| От | Jeff Janes |
|---|---|
| Тема | Re: Why does the query planner use two full indexes, when a dedicated partial index exists? (solved?) |
| Дата | |
| Msg-id | CAMkU=1wYPPgB9uiH3AXwP-MSAUyObfC_-BoCgdvqFjZLfg+-Ow@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Why does the query planner use two full indexes, when a dedicated partial index exists? (solved?) (John Rouillard <rouilj@renesys.com>) |
| Список | pgsql-performance |
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
В списке pgsql-performance по дате отправления: