Re: [HACKERS] 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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Is there a way to temporarily disable a index
David G Johnston david.g.johns...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Is there a way to temporarily disable a index
On 2014-07-11 11:07:21 -0400, Tom Lane wrote: David G Johnston david.g.johns...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Is there a way to temporarily disable a index
Andres Freund and...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Is there a way to temporarily disable a index
On 2014-07-11 11:20:08 -0400, Tom Lane wrote: Andres Freund and...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Is there a way to temporarily disable a index
Andres Freund and...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Is there a way to temporarily disable a index
On Fri, Jul 11, 2014 at 11:07:21AM -0400, Tom Lane wrote: David G Johnston david.g.johns...@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@cbbrowne.afilias-int.info (as linked to from the TODO wiki page), but the neighboring discussion was mostly about FK constraints. Thoughts? Michael -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Is there a way to temporarily disable a index
On Fri, Jul 11, 2014 at 12:12 PM, Michael Banck mba...@gmx.net wrote: On Fri, Jul 11, 2014 at 11:07:21AM -0400, Tom Lane wrote: David G Johnston david.g.johns...@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@cbbrowne.afilias-int.info (as linked to from the TODO wiki page), but the neighboring discussion was mostly about FK constraints. Thoughts? Michael Apparently work is ongoing on to allow EXPLAIN to calculate the impact a particular index has on table writes. What is needed is a mechanism to temporarily facilitate the remove impact of specific indexes on reads without ​having to disable the index for writing. Ideally on a per-query basis so altering the catalog doesn't make sense. I know we do not want traditional planner hints but 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-level control of which indexes are to be used during table reads. David J.
