Обсуждение: Disabling an index temporarily
Sometimes I need to repeat creating and dropping indexes while doing an SQL tuning work. As you might know, creating a large index takes long time. So dropping the index and re-creating it is pain and counter productive. What about inventing a new SET command something like: SET disabled_index to <index-name> This adds <index-name> to "disabled index list". The disabled index list let the planner to disregard the indexes in the list. SET enabled_index to <index-name> This removes <index-name> from the disabled index list. SHOW disabled_index This shows the content of the disabled index list. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp
On 12/11/2015 06:25 PM, Tatsuo Ishii wrote: > What about inventing a new SET command something like: > > SET disabled_index to <index-name> > > This adds <index-name> to "disabled index list". The disabled index > list let the planner to disregard the indexes in the list. > > SET enabled_index to <index-name> > > This removes <index-name> from the disabled index list. > > SHOW disabled_index > > This shows the content of the disabled index list. Wouldn't something like: ALTER INDEX foo SET DISABLED; See more in line with our grammar? I assume the index is only disabled as far as the planner is concerned and all updates/inserts/deletes will still actually update the index appropriately? JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Centered full stack support, consulting and development. Announcing "I'm offended" is basically telling the world you can't control your own emotions, so everyone else should do it for you.
> On 12/11/2015 06:25 PM, Tatsuo Ishii wrote: > >> What about inventing a new SET command something like: >> >> SET disabled_index to <index-name> >> >> This adds <index-name> to "disabled index list". The disabled index >> list let the planner to disregard the indexes in the list. >> >> SET enabled_index to <index-name> >> >> This removes <index-name> from the disabled index list. >> >> SHOW disabled_index >> >> This shows the content of the disabled index list. > > Wouldn't something like: > > ALTER INDEX foo SET DISABLED; > > See more in line with our grammar? But this will affect other sessions, no? > I assume the index is only disabled as far as the planner is concerned > and all updates/inserts/deletes will still actually update the index > appropriately? Yes. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp
Tatsuo Ishii wrote: >> Wouldn't something like: >> >> ALTER INDEX foo SET DISABLED; >> >> See more in line with our grammar? > > But this will affect other sessions, no? Not if it is used in a transaction that ends with a ROLLBACK, but then you might as well use DROP INDEX, except that DROP INDEX takes an access exclusive lock. Yours, Laurenz Albe
On 12/12/2015 11:42, Albe Laurenz wrote: > Tatsuo Ishii wrote: >>> Wouldn't something like: >>> >>> ALTER INDEX foo SET DISABLED; >>> >>> See more in line with our grammar? >> >> But this will affect other sessions, no? > > Not if it is used in a transaction that ends with a ROLLBACK, > but then you might as well use DROP INDEX, except > that DROP INDEX takes an access exclusive lock. > > Yours, > Laurenz Albe > Oleg and Teodor announced some time ago an extension for this exact use case, see http://www.postgresql.org/message-id/Pine.LNX.4.64.0910062354510.6801@sn.sai.msu.ru This also has the advantage of not needing an exclusive lock on the index. -- Julien Rouhaud http://dalibo.com - http://dalibo.org
> Tatsuo Ishii wrote: >>> Wouldn't something like: >>> >>> ALTER INDEX foo SET DISABLED; >>> >>> See more in line with our grammar? >> >> But this will affect other sessions, no? > > Not if it is used in a transaction that ends with a ROLLBACK, > but then you might as well use DROP INDEX, except > that DROP INDEX takes an access exclusive lock. I thought about this. Problem with the transaction rollback technique is, I would not be able to test with an application which runs multiple transactions. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp
> Oleg and Teodor announced some time ago an extension for this exact use > case, see > http://www.postgresql.org/message-id/Pine.LNX.4.64.0910062354510.6801@sn.sai.msu.ru > > This also has the advantage of not needing an exclusive lock on the index. Thanks for the info. I will try out them. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp
On 11 December 2015 at 22:03, Joshua D. Drake <jd@commandprompt.com> wrote: > On 12/11/2015 06:25 PM, Tatsuo Ishii wrote: > >> What about inventing a new SET command something like: >> >> SET disabled_index to <index-name> >> >> This adds <index-name> to "disabled index list". The disabled index >> list let the planner to disregard the indexes in the list. >> >> SET enabled_index to <index-name> >> >> This removes <index-name> from the disabled index list. >> >> SHOW disabled_index >> >> This shows the content of the disabled index list. > > > Wouldn't something like: > > ALTER INDEX foo SET DISABLED; > > See more in line with our grammar? > > I assume the index is only disabled as far as the planner is concerned and > all updates/inserts/deletes will still actually update the index > appropriately? > BTW, you can do that today with UPDATE pg_index SET indisvalid = falseWHERE indexrelid = 'indexname'::regclass; -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación
<div dir="ltr"><div class="gmail_extra"><br /><div class="gmail_quote">On Sun, Dec 13, 2015 at 1:16 AM, Jaime Casanova <spandir="ltr"><<a href="mailto:jaime.casanova@2ndquadrant.com" target="_blank">jaime.casanova@2ndquadrant.com</a>></span>wrote:<br /><blockquote class="gmail_quote" style="margin:00 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">indexrelid = 'indexname'::regclass;</blockquote></div><br/></div><div class="gmail_extra">This works, but might bloat system catalog.<br/></div><div class="gmail_extra"><br /></div></div>
On Sun, Dec 13, 2015 at 1:33 AM, Oleg Bartunov <obartunov@gmail.com> wrote:
On Sun, Dec 13, 2015 at 1:16 AM, Jaime Casanova <jaime.casanova@2ndquadrant.com> wrote:indexrelid = 'indexname'::regclass;This works, but might bloat system catalog.
+1 for the functionality.
+1 for ALTER INDEX foo SET DISABLED
I mentioned the need for this functionality to PeterG as PgConfUS back in March when he asked what I missed most about Oracle, where it came into play when doing partitions swaps and similar bulk Data Warehouse operations. He didn't seem to think it would be too hard to implement.
But the real win would be the ability to disable all indexes on a table without specifying names. Even Oracle has to do this with an anonymous pl/sql block querying dba_indexes or all_indexes, a pity for such a common pattern.
So, I'd propose we following syntax:
ALTER INDEX foo SET DISABLED
-- does the SET indisvalid = false shown earlier.
ALTER TABLE foo DISABLE [NONUNIQUE] INDEXES
-- same, but joining to pg_class and possibly filtering on indisunique
REINDEX [DISABLED [INDEXES ON]] TABLE table_name [PARALLEL [degree]]
or
REINDEX [INVALID [INDEXES ON]] TABLE table_name [PARALLEL [degree]]
In this last case, REINDEX would walk the catalog as it does now, but potentially filtering the table indexes on indisvalid = false. I'd ask that we make a parallel spec part of the command even if it is not initially honored.
This would be another feather in Postgres's cap of letting the user write clear code and hiding implementation specific complexity.
On Sun, 13 Dec 2015 22:15:31 -0500 Corey Huinker <corey.huinker@gmail.com> wrote: > ALTER TABLE foo DISABLE [NONUNIQUE] INDEXES > -- same, but joining to pg_class and possibly filtering on indisunique I would think that NONUNIQUE should be the default, and you should have to specify something special to also disable unique indexes. Arguably, unique indexes are actually an implementation detail of unique constraints. Disabling a performance-based index doesn't cause data corruption, whereas disabling an index created as part of unique constraint can allow invalid data into the table. Just my $.02 ... -- Bill Moran
>> On Sun, Dec 13, 2015 at 1:16 AM, Jaime Casanova < >> jaime.casanova@2ndquadrant.com> wrote: >> >>> indexrelid = 'indexname'::regclass; >> >> >> This works, but might bloat system catalog. >> >> > +1 for the functionality. > +1 for ALTER INDEX foo SET DISABLED -1 for the reason I mentioned in the up thread. Also I dislike thisbecause this does not work with standby servers. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp
Corey Huinker <corey.huinker@gmail.com> writes:
> So, I'd propose we following syntax:
> ALTER INDEX foo SET DISABLED
> -- does the SET indisvalid = false shown earlier.
This is exactly *not* what Tatsuo-san was after, though; he was asking
for a session-local disable, which I would think would be by far the more
common use-case. It's hard for me to see much of a reason to disable an
index globally while still paying all the cost to maintain it. Seems to
me the typical work flow would be more like "disable index in a test
session, try all your queries and see how well they work, if you conclude
you don't need the index then drop it". Or perhaps you could imagine that
you want the index selected for use only in certain specific sessions ...
but the above doesn't cater for that use-case either.
Certainly, there's opportunities to improve the flexibility of the
index-disable specifications in the plug-in Oleg and Teodor did. But
I think that that is the right basic approach: some sort of SET command,
not anything that alters the catalogs. We already have lots of
infrastructure that could handle desires like having specific values
active in only some sessions.
regards, tom lane
Bill Moran <wmoran@potentialtech.com> writes:
> I would think that NONUNIQUE should be the default, and you should have
> to specify something special to also disable unique indexes. Arguably,
> unique indexes are actually an implementation detail of unique
> constraints. Disabling a performance-based index doesn't cause data
> corruption, whereas disabling an index created as part of unique
> constraint can allow invalid data into the table.
Maybe I misunderstood, but I thought what was being discussed here is
preventing the planner from selecting an index for use in queries, while
still requiring all table updates to maintain validity of the index.
regards, tom lane
On Mon, Dec 14, 2015 at 12:27 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Certainly, there's opportunities to improve the flexibility of the > index-disable specifications in the plug-in Oleg and Teodor did. But > I think that that is the right basic approach: some sort of SET command, > not anything that alters the catalogs. We already have lots of > infrastructure that could handle desires like having specific values > active in only some sessions. ISTM that an intuitive answer is something like enable_indexscan_list = 'index1, index2' and not worry about any disable switch, that's more in line with the equivalent planner-level GUC. -- Michael
On Sun, Dec 13, 2015 at 7:27 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Corey Huinker <corey.huinker@gmail.com> writes: >> So, I'd propose we following syntax: >> ALTER INDEX foo SET DISABLED >> -- does the SET indisvalid = false shown earlier. > > This is exactly *not* what Tatsuo-san was after, though; he was asking > for a session-local disable, which I would think would be by far the more > common use-case. It's hard for me to see much of a reason to disable an > index globally while still paying all the cost to maintain it. Not to hijack the thread even further in the wrong direction, but I think what Corey really wants here is to stop maintaining the index at retail while preserving the existing definition and existing index data, and then to do a wholesale fix-up, like what is done in the 2nd half of a create index concurrently, upon re-enabling it. Cheers, Jeff
Jeff Janes <jeff.janes@gmail.com> writes:
> Not to hijack the thread even further in the wrong direction, but I
> think what Corey really wants here is to stop maintaining the index at
> retail while preserving the existing definition and existing index
> data, and then to do a wholesale fix-up, like what is done in the 2nd
> half of a create index concurrently, upon re-enabling it.
Meh. Why not just drop the index? I mean, yeah, you might save a few
keystrokes when and if you ever re-enable it, but this sure seems like
a feature in search of a use-case.
regards, tom lane
2015-12-14 5:34 GMT+02:00 Tom Lane <tgl@sss.pgh.pa.us>:
Maybe I misunderstood, but I thought what was being discussed here is
preventing the planner from selecting an index for use in queries, while
still requiring all table updates to maintain validity of the index.
The O-ther big DBMS has `ALTER INDEX ... INVISIBLE` feature, that does exactly this.
I was thinking of a function, similar to `set_config()`, for it has `is_local` parameter, making it possible to adjust just current session or a global behavior.
`set_index(name, is_visible, is_local` perhaps?
--
Victor Y. Yegorov
<div dir="ltr"><div class="gmail_extra"><div class="gmail_quote">On Sun, Dec 13, 2015 at 11:03 PM, Tom Lane <span dir="ltr"><<ahref="mailto:tgl@sss.pgh.pa.us" target="_blank">tgl@sss.pgh.pa.us</a>></span> wrote:<br /><blockquoteclass="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><span class="">JeffJanes <<a href="mailto:jeff.janes@gmail.com">jeff.janes@gmail.com</a>> writes:<br /> > Not to hijackthe thread even further in the wrong direction, but I<br /> > think what Corey really wants here is to stop maintainingthe index at<br /> > retail while preserving the existing definition and existing index<br /> > data, andthen to do a wholesale fix-up, like what is done in the 2nd<br /> > half of a create index concurrently, upon re-enablingit.<br /><br /></span>Meh. Why not just drop the index? I mean, yeah, you might save a few<br /> keystrokeswhen and if you ever re-enable it, but this sure seems like<br /> a feature in search of a use-case.<br /><br /> regards, tom lane<br /></blockquote></div><br /></div><div class="gmail_extra">Sorry, I misreadTatsu's initial post. I thought the disabling was for the purpose of reducing overhead on large DML operations, notplan experimentation.</div><div class="gmail_extra"><br /></div><div class="gmail_extra">Jeff's suggestion is one use-case.The work of discovering what indexes exist on a table (because it may have changed since you last wrote that code),saving those names and definitions to an intermediate table, disabling them, doing the big DML operation, and thenre-enabling them is tedious and error prone, both in the coding of it and the error handling. Leaving the index definitionsin the data dictionary is one way to avoid all that.</div><div class="gmail_extra"><br /></div><div class="gmail_extra"><br/></div></div>
<div dir="ltr"><div class="gmail_extra"><div class="gmail_quote">On Sun, Dec 13, 2015 at 10:23 PM, Bill Moran <span dir="ltr"><<ahref="mailto:wmoran@potentialtech.com" target="_blank">wmoran@potentialtech.com</a>></span> wrote:<br/><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><span class="">OnSun, 13 Dec 2015 22:15:31 -0500<br /> Corey Huinker <<a href="mailto:corey.huinker@gmail.com">corey.huinker@gmail.com</a>>wrote:<br /><br /> > ALTER TABLE foo DISABLE [NONUNIQUE]INDEXES<br /> > -- same, but joining to pg_class and possibly filtering on indisunique<br /><br /></span>Iwould think that NONUNIQUE should be the default, and you should have<br /> to specify something special to alsodisable unique indexes. Arguably,<br /> unique indexes are actually an implementation detail of unique<br /> constraints.Disabling a performance-based index doesn't cause data<br /> corruption, whereas disabling an index created aspart of unique<br /> constraint can allow invalid data into the table.<br /><br /> Just my $.02 ...<br /><span class="HOEnZb"><fontcolor="#888888"><br /> --<br /> Bill Moran<br /></font></span></blockquote></div><br /></div><div class="gmail_extra">I'dbe fine swapping NONUNIQUE for ALL and defaulting to non-unique, or flatly enforcing a rule that itwon't disable the index required by an enabled constraint.</div><div class="gmail_extra"><br /></div><div class="gmail_extra"><br/></div></div>
On 12/13/15 9:27 PM, Tom Lane wrote: > Corey Huinker<corey.huinker@gmail.com> writes: >> >So, I'd propose we following syntax: >> >ALTER INDEX foo SET DISABLED >> >-- does the SET indisvalid = false shown earlier. > This is exactly*not* what Tatsuo-san was after, though; he was asking > for a session-local disable, which I would think would be by far the more > common use-case. It's hard for me to see much of a reason to disable an > index globally while still paying all the cost to maintain it. Seems to > me the typical work flow would be more like "disable index in a test > session, try all your queries and see how well they work, if you conclude > you don't need the index then drop it". Both have value. Sometimes the only realistic way to test this is to disable the index server-wide and see if anything blows up. Actually, in my experience, that's far more common than having some set of queries you can test against and call it good. FWIW, I also don't see the use case for disabling maintenance on an index. Just drop it and if you know you'll want to recreate it squirrel away pg_get_indexdef() before you do. -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com
On Tue, Dec 15, 2015 at 7:56 PM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote: > On 12/13/15 9:27 PM, Tom Lane wrote: >> >> Corey Huinker<corey.huinker@gmail.com> writes: >>> >>> >So, I'd propose we following syntax: >>> >ALTER INDEX foo SET DISABLED >>> >-- does the SET indisvalid = false shown earlier. >> >> This is exactly*not* what Tatsuo-san was after, though; he was asking >> for a session-local disable, which I would think would be by far the more >> common use-case. It's hard for me to see much of a reason to disable an >> index globally while still paying all the cost to maintain it. Seems to >> me the typical work flow would be more like "disable index in a test >> session, try all your queries and see how well they work, if you conclude >> you don't need the index then drop it". > > > Both have value. > > Sometimes the only realistic way to test this is to disable the index > server-wide and see if anything blows up. Actually, in my experience, that's > far more common than having some set of queries you can test against and > call it good. > > FWIW, I also don't see the use case for disabling maintenance on an index. > Just drop it and if you know you'll want to recreate it squirrel away > pg_get_indexdef() before you do. If someone wants to make "squirreling away the pg_get_indexdef" easier, particularly for an entire table or an entire schema or an entire database, I certainly wouldn't object. I am not a masochist. But also, while loading 1.5 million records into a table with 250 million records is horribly, rebuilding all the indexes on a 251.5 million record table from scratch is even more horrible. I don't know if suspending maintenance (either globally or just for one session) and then doing a bulk fix-up would be less horrible, but would be willing to give it a test run. Cheers, Jeff
On 12/16/15 12:15 AM, Jeff Janes wrote: > But also, while loading 1.5 million records into a table with 250 > million records is horribly, rebuilding all the indexes on a 251.5 > million record table from scratch is even more horrible. I don't know > if suspending maintenance (either globally or just for one session) > and then doing a bulk fix-up would be less horrible, but would be > willing to give it a test run. I would think that's something completely different though, no? If you're doing that wouldn't you want other inserting/updating backends to still maintain the index, and only do something special in the backend that's doing the bulk load? Otherwise the bulk load would have to wait for all running backends to finish to ensure that no one was using the index. That's ugly enough for CIC; I can't fathom it working in any normal batch processing. (Doing a single bulk insert to the index at the end of an INSERT should be safe though because none of those tuples are visible yet, though I'd have to make sure your backend didn't try to use the index for anything while the command was running... like as part of a trigger.) -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com
On Mon, Dec 14, 2015 at 10:27 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Corey Huinker <corey.huinker@gmail.com> writes: >> So, I'd propose we following syntax: >> ALTER INDEX foo SET DISABLED >> -- does the SET indisvalid = false shown earlier. > > This is exactly *not* what Tatsuo-san was after, though; he was asking > for a session-local disable, which I would think would be by far the more > common use-case. It's hard for me to see much of a reason to disable an > index globally while still paying all the cost to maintain it. Seems to > me the typical work flow would be more like "disable index in a test > session, try all your queries and see how well they work, if you conclude > you don't need the index then drop it". Or perhaps you could imagine that > you want the index selected for use only in certain specific sessions ... > but the above doesn't cater for that use-case either. > > Certainly, there's opportunities to improve the flexibility of the > index-disable specifications in the plug-in Oleg and Teodor did. But > I think that that is the right basic approach: some sort of SET command, > not anything that alters the catalogs. We already have lots of > infrastructure that could handle desires like having specific values > active in only some sessions. I searched for "indisvalid" and this thread came up. I need this exact same thing as Tatsuo-san; a way to session-local disable index(es), so that plpgsql functions can avoid certain indexes when they are created/planned. How would one go about to implement such a SET command, without altering the catalog? I noticed the RelationReloadIndexInfo() which appears to be doing a light-weight update of index changes, including "relation->rd_index->indisvalid = index->indisvalid". Or maybe one could call index_set_state_flags(indexId, INDEX_DROP_CLEAR_VALID) before the function is compiled/planned, and then reset it using index_set_state_flags(indexId, INDEX_CREATE_SET_VALID) after it has been compiled/planned? If someone could give me guidance on where to start I would be grateful. Even if I don't succeed implementing this, it's at least fun and interesting to dig into the postgres source code to learn things. Thanks Joel