Thread: Is there a way to temporarily disable a index

Benedikt Grundmann wrote
> That is it possible to tell the planner that index is off limits i.e.
> don't
> ever generate a plan using it?
> 
> Rationale:  Schema changes on big tables.  I might have convinced myself /
> strong beliefs that for all queries that I need to be fast the planner
> does
> not need to use a given index (e.g. other possible plans are fast enough).
> However if I just drop the index and it turns out I'm wrong I might be in
> a
> world of pain because it might just take way to long to recreate the
> index.
> 
> I know that I can use pg_stat* to figure out if an index is used at all.
> But in the presense of multiple indices and complex queries the planner
> might prefer the index-to-be-dropped but the difference to the
> alternatives
> available is immaterial.
> 
> The current best alternative we have is to test such changes on a testing
> database that gets regularly restored from production.  However at least
> in
> our case we simply don't know all possible queries (and logging all of
> them
> is not an option).
> 
> Cheers,
> 
> Bene

Worth double-checking in test but...

BEGIN;
DROP INDEX ...;
EXPLAIN ANALYZE SELECT ...
ROLLBACK;

Index dropping is transactional so your temporary action lasts until you
abort said transaction.

Though given your knowledge limitations this really isn't an improvement...

Catalog hacking could work but not recommended (nor do I know the proper
commands and limitations).  Do you need the database/table to accept writes
during the testing period?

You can avoid all indexes, but not a named subset, using a configuration
parameter.

David J.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Is-there-a-way-to-temporarily-disable-a-index-tp5811249p5811290.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

David G Johnston <david.g.johnston@gmail.com> writes:
> Benedikt Grundmann wrote
>> That is it possible to tell the planner that index is off limits i.e.
>> don't ever generate a plan using it?

> Catalog hacking could work but not recommended (nor do I know the proper
> commands and limitations).  Do you need the database/table to accept writes
> during the testing period?

Hacking pg_index.indisvalid could work, given a reasonably recent PG.
I would not try it in production until I'd tested it ;-)
        regards, tom lane

On 2014-07-11 11:07:21 -0400, Tom Lane wrote:
> David G Johnston <david.g.johnston@gmail.com> writes:
> > Benedikt Grundmann wrote
> >> That is it possible to tell the planner that index is off limits i.e.
> >> don't ever generate a plan using it?
> 
> > Catalog hacking could work but not recommended (nor do I know the proper
> > commands and limitations).  Do you need the database/table to accept writes
> > during the testing period?
> 
> Hacking pg_index.indisvalid could work, given a reasonably recent PG.
> I would not try it in production until I'd tested it ;-)

Works, but IIRC can cause problems at least < 9.4 because concurrent
cache builds might miss the pg_index row...

Greetings,

Andres Freund

-- Andres Freund                       http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training &
Services

Andres Freund <andres@2ndquadrant.com> writes:
> On 2014-07-11 11:07:21 -0400, Tom Lane wrote:
>> Hacking pg_index.indisvalid could work, given a reasonably recent PG.
>> I would not try it in production until I'd tested it ;-)

> Works, but IIRC can cause problems at least < 9.4 because concurrent
> cache builds might miss the pg_index row...

If you're talking about SnapshotNow hazards, I think the risk would be
minimal, and probably no worse than cases that the system will cause
by itself.
        regards, tom lane

On 2014-07-11 11:20:08 -0400, Tom Lane wrote:
> Andres Freund <andres@2ndquadrant.com> writes:
> > On 2014-07-11 11:07:21 -0400, Tom Lane wrote:
> >> Hacking pg_index.indisvalid could work, given a reasonably recent PG.
> >> I would not try it in production until I'd tested it ;-)
> 
> > Works, but IIRC can cause problems at least < 9.4 because concurrent
> > cache builds might miss the pg_index row...
> 
> If you're talking about SnapshotNow hazards, I think the risk would be
> minimal, and probably no worse than cases that the system will cause
> by itself.

Yes, SnapshotNow. I could reproduce it causing 'spurious' HOT updates
and missing index inserts a while back. And I don't think it's
comparable with normal modifications. Those either have a modification
blocking lock or use heap_inplace...

Greetings,

Andres Freund

-- Andres Freund                       http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training &
Services

Andres Freund <andres@2ndquadrant.com> writes:
> On 2014-07-11 11:20:08 -0400, Tom Lane wrote:
>> If you're talking about SnapshotNow hazards, I think the risk would be
>> minimal, and probably no worse than cases that the system will cause
>> by itself.

> Yes, SnapshotNow. I could reproduce it causing 'spurious' HOT updates
> and missing index inserts a while back. And I don't think it's
> comparable with normal modifications. Those either have a modification
> blocking lock or use heap_inplace...

I still think the risk is minimal, but if the OP was worried about this
he could take out an AccessExclusive lock on the parent table for long
enough to commit the pg_index change.
        regards, tom lane

On Fri, Jul 11, 2014 at 11:07:21AM -0400, Tom Lane wrote:
> David G Johnston <david.g.johnston@gmail.com> writes:
> > Benedikt Grundmann wrote
> >> That is it possible to tell the planner that index is off limits
> >> i.e.
> >> don't ever generate a plan using it?
> 
> > Catalog hacking could work but not recommended (nor do I know the
> > proper
> > commands and limitations).  Do you need the database/table to accept
> > writes
> > during the testing period?
> 
> Hacking pg_index.indisvalid could work, given a reasonably recent PG.
> I would not try it in production until I'd tested it ;-)

I wonder whether this should be exposed at the SQL level?  Hacking
pg_index is left to superusers, but the creator of an index (or the
owner of the schema) might want to experiment with disabling indices
while debugging query plans as well.

Turns out this is already in the TODO, Steve Singer has requested this
(in particular, "ALTER TABLE ...  ENABLE|DISABLE INDEX ...") in
http://www.postgresql.org/message-id/87hbegz5ir.fsf@cbbrowne.afilias-int.info
(as linked to from the TODO wiki page), but the neighboring discussion
was mostly about FK constraints.

Thoughts?


Michael

<div dir="ltr"><div class="gmail_default" style="font-family:arial,helvetica,sans-serif"><span
style="font-family:arial">OnFri, Jul 11, 2014 at 12:12 PM, Michael Banck </span><span dir="ltr"
style="font-family:arial"><<ahref="mailto:mbanck@gmx.net" target="_blank">mbanck@gmx.net</a>></span><span
style="font-family:arial">wrote:</span><br /></div><div class="gmail_extra"><div class="gmail_quote"><blockquote
class="gmail_quote"style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div class="HOEnZb"><div
class="h5">OnFri, Jul 11, 2014 at 11:07:21AM -0400, Tom Lane wrote:<br /> > David G Johnston <<a
href="mailto:david.g.johnston@gmail.com">david.g.johnston@gmail.com</a>>writes:<br /> > > Benedikt Grundmann
wrote<br/> > >> That is it possible to tell the planner that index is off limits<br /> > >> i.e.<br
/>> >> don't ever generate a plan using it?<br /> ><br /> > > Catalog hacking could work but not
recommended(nor do I know the<br /> > > proper<br /> > > commands and limitations).  Do you need the
database/tableto accept<br /> > > writes<br /> > > during the testing period?<br /> ><br /> > Hacking
pg_index.indisvalidcould work, given a reasonably recent PG.<br /> > I would not try it in production until I'd
testedit ;-)<br /><br /></div></div>I wonder whether this should be exposed at the SQL level?  Hacking<br /> pg_index
isleft to superusers, but the creator of an index (or the<br /> owner of the schema) might want to experiment with
disablingindices<br /> while debugging query plans as well.<br /><br /> Turns out this is already in the TODO, Steve
Singerhas requested this<br /> (in particular, "ALTER TABLE ...  ENABLE|DISABLE INDEX ...") in<br /><a
href="http://www.postgresql.org/message-id/87hbegz5ir.fsf@cbbrowne.afilias-int.info"
target="_blank">http://www.postgresql.org/message-id/87hbegz5ir.fsf@cbbrowne.afilias-int.info</a><br/> (as linked to
fromthe TODO wiki page), but the neighboring discussion<br /> was mostly about FK constraints.<br /><br /> Thoughts?<br
/><spanclass="HOEnZb"><font color="#888888"><br /><br /> Michael<br /></font></span></blockquote></div><br /></div><div
class="gmail_extra"><divclass="gmail_default" style="font-family:arial,helvetica,sans-serif">Apparently work is ongoing
onto allow EXPLAIN to calculate the impact a particular index has on table writes.  What is needed is a mechanism to
temporarilyfacilitate the remove impact of specific indexes on reads without ​having to disable the index for writing.
 Ideallyon a per-query basis so altering the catalog doesn't make sense.  I know we do not want traditional planner
hintsbut in the spirit of the existing enable_indexscan GUC there should be a "
disable_readofindex='table1.index1,table1.index2,table2.index1'" GUC capability that would allow for session, user, or
system-levelcontrol of which indexes are to be used during table reads.</div><div class="gmail_default"
style="font-family:arial,helvetica,sans-serif"><br/></div><div class="gmail_default"
style="font-family:arial,helvetica,sans-serif">DavidJ.</div><div class="gmail_default"
style="font-family:arial,helvetica,sans-serif"><br/></div><br /></div><div class="gmail_extra"><br /></div></div>