Обсуждение: Is there a way to temporarily disable a index
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.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
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>