Thread: Disable an index temporarily
Hi, 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? Thanks, Torsten
On Apr 19, 2014 1:53 PM, "Torsten Förtsch" <torsten.foertsch@gmx.net> wrote:
>
> Hi,
>
> 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;
>
> Thanks,
> Torsten
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
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
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
Torsten Förtsch wrote on 20.04.2014 10:09: > 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?). What about monitoring pg_stat_all_indexes to see if an index is used: http://www.postgresql.org/docs/current/static/monitoring-stats.html#PG-STAT-ALL-INDEXES-VIEW Btw: in the above example the second one is definitely not needed. Any query that uses the second one (single column) can also use the first one.
On 20/04/14 12:08, Thomas Kellerer wrote: >> Example: >> >> btree (fmb_id, action_type) >> btree (fmb_id) >> [...] > > Btw: in the above example the second one is definitely not needed. > Any query that uses the second one (single column) can also use the > first one. I know. But the single column index is 3534 MB the 2-column one 4963 MB. The number of rows per distinct fmb_id is very small, usually 1 or 2. So, if a query looks for "fmb_id=A and action_type=B", it has to filter out only one row if the first index is not available. Hence, I thought maybe the lower size of the index and the higher compactness per page could outweigh the more direct access provided by the 2-column index. I am quite sure there is no query that qualifies for an index-only scan on the 2-column index. Torsten
On Apr 20, 2014 4:21 AM, "Torsten Förtsch" <torsten.foertsch@gmx.net> wrote:
>
> On 20/04/14 12:08, Thomas Kellerer wrote:
> >> Example:
> >>
> >> btree (fmb_id, action_type)
> >> btree (fmb_id)
> >>
> [...]
> >
> > Btw: in the above example the second one is definitely not needed.
> > Any query that uses the second one (single column) can also use the
> > first one.
>
> I know.
>
> But the single column index is 3534 MB the 2-column one 4963 MB. The
> number of rows per distinct fmb_id is very small, usually 1 or 2. So, if
> a query looks for "fmb_id=A and action_type=B", it has to filter out
> only one row if the first index is not available. Hence, I thought maybe
> the lower size of the index and the higher compactness per page could
> outweigh the more direct access provided by the 2-column index.
I don't think there is a supported way to do that. And since the quasi dropped index still has to be maintained (and so probably kept in cache), the benefits of dropping it may not be observable anyway by your proposed experiment, unless the table is read only.
Cheers,
Jeff
On 20 April 2014 02:21, Torsten Wrote:
> Hi,
>
> 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?
I don't think there is any way to do so.
But If your intension is just to avoid index scan for some time, then you can use following command, which disable
indexscan
set enable_indexscan to off;
Once you are done with experimentation, you can execute command, which enable index scan.
set enable_indexscan to on;
Thanks and Regards,
Kumar Rajeev Rastogi