Re: Disable an index temporarily - Mailing list pgsql-general

On 20/04/14 20:09, Torsten Förtsch wrote:
> On 20/04/14 03:02, Sergey Konoplev wrote:
>
> Thanks for you reply.
>
>>> an index can be INVALID (pg_index.indisvalid=false).
>>>
>>> I want to temporarily disable an index so that it won't be used to
>>> access data but will still be updated.
>>>
>>> Can I simply set pg_index.indisvalid=false and later turn it true again?
>> It works on a quick test, but I'm not sure how safe it is.
>>
>> If you need to test a query without the index use a transaction:
>>
>> Begin;
>> Drop index ...;
>> Explain ... select ...;
>> Rollback;
> I know about that.
>
> The problem is I have a number of indexes in a large system that are
> very similar. And I suspect some of them are superfluous.
>
> Example:
>
>      btree (fmb_id, action_type)
>      btree (fmb_id)
>
> Action_type in this case is one out of a handful of values (should
> perhaps be an ENUM but is TEXT) and for most of the table the
> combination of (fmb_id, action_type) is unique. The table itself has
> ~2E8 rows. So it takes a while to build these indexes from scratch.
>
> Now, we have several performance counters in place. I want to disable
> these indexes one by one and see what happens. I am probably not able to
> find all of the queries that use them. But I believe that nothing much
> happens if I drop one of them (preferably the former?).
>
> Torsten
>
>
You might be best off dropping all indexes, then initially adding them
back individually, then in pairs etc.  As each index used, will have to
be read in from disk and consumes RAM. The more RAM that is used to hold
indexes, the less RAM there is that can be used to hold table data and
other stuff related to your queries.

Also PostgreSQL can use multiple indexes on columns.  For example if you
have an index on column A and another on column B - then a query with
restrictions on columns A, B, and C might (if the values searched for in
A & B each hit very a low fraction of pages) result in the indexes for A
& B being used to select which pages to be read in to check on the
values for C.

So sometimes an index on A & B plus an index on C & D, could be used for
a query that has restrictions on A, B, C, & E - as the 2 indexes can be
used to search on the values of A, B, & C, so restricting the pages
needing to be read to check on E.  So possibly, depending on your data
and your query mix, an index on A, B, C, & E may not be needed in
addition to the other 2 indexes - or just the latter index would be
useful - or possibly all 3 indexes.

So in your example, you might be better off with the two indexes: btree
(fmb_id) & btree (action_type) - or just the one index: btree (fmb_id,
action_type) - or all 3!

Depends on trade-offs between the cost of testing and the time saved,
how much effort you should put in!

Can you get any insights from looking at the existing queries?


Cheers,
Gavin