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 > >
