Re: Force specific index disuse

On Tue, May 20, 2014 at 3:20 PM, Jeff Janes [via PostgreSQL] <
ml-node+s1045698n5804596h0(at)n5(dot)nabble(dot)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.