On Tue, May 20, 2014 at 3:20 PM, Jeff Janes [via PostgreSQL] < ml-node+s1045698n580459...@n5.nabble.com> wrote:
> On Tue, May 20, 2014 at 11:48 AM, Steve Crawford <[hidden > email]<http://user/SendEmail.jtp?type=node&node=5804596&i=0> > > wrote: > >> On 05/20/2014 10:44 AM, Alvaro Herrera wrote: >> >>> Steve Crawford wrote: >>> >>>> Is there a way to force a specific index to be removed from >>>> consideration in planning a single query? >>>> >>>> Specifically, on a 60-million-row table I have an index that is a >>>> candidate for removal. I have identified the sets of nightly queries >>>> that use the index but before dropping it I would like to run >>>> EXPLAIN and do timing tests on the queries to see the impact of not >>>> having that index available and rewrite the query to efficiently use >>>> other indexes if necessary. >>>> >>> If you can afford to lock the table for a while, the easiest is >>> >>> BEGIN; >>> DROP INDEX bothersome_idx; >>> EXPLAIN your_query; >>> ROLLBACK; >>> >>> Interesting. But what do you mean by "a while?" Does the above keep the >> index intact (brief lock) or does it have to rebuild it on rollback? >> > > Best case, 'A while' means however long it takes the explain (possibly > analyze) to run, and for you to then type 'rollback;' > > worse case, someone else is already holding an incompatible lock (i.e. any > lock) on the table, and is going to hang on to it for a long while, so your > drop index hangs forever waiting to acquire the lock and in the process > brings all other desired activity (except the one already holding the lock) > to a screeching halt because they are not allowed to jump the lock queue. > > worser case, you forget to enter 'rollback' at all and accidentally commit > the index drop. > > I use the begin...drop...rollback on test servers a lot, but rarely on > productions servers. If I don't want to lock for that long, you can often > alter the query to make the index useless, for example: > > explain select * from pgbench_accounts where aid=87; > > Becomes: > > explain select * from pgbench_accounts where aid+0=87; > > Although for complex queries it can be hard to find the antimagic bullet. > > Cheers, > > Jeff > > This whole line of thought is a use-case for Jaime Casanova's recent proposal: http://postgresql.1045698.n5.nabble.com/WIP-showing-index-maintenance-on-EXPLAIN-td5803106.html Note that disabling the index as shown, via clobbering a WHERE clause, doesn't help when the concern is how much time is being spent updating indexes during INSERT/UPDATE. The risk of forgetting to issue the "BEGIN;" (not so much ROLLBACK given typical default behavior if BEGIN is indeed issued) is probably the one that would concern me the most if working on a production server. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Force-specific-index-disuse-tp5804564p5804601.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
