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