Re: Is there a way to temporarily disable a index - Mailing list pgsql-hackers

<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>