Disabling an index temporarily

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.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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
/messages/by-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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Oleg and Teodor announced some time ago an extension for this exact use
case, see
/messages/by-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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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 = false
WHERE indexrelid = 'indexname'::regclass;

--
Jaime Casanova www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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.

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

On Sun, Dec 13, 2015 at 11:03 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

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

Sorry, I misread Tatsu's initial post. I thought the disabling was for the
purpose of reducing overhead on large DML operations, not plan
experimentation.

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 then re-enabling them is
tedious and error prone, both in the coding of it and the error handling.
Leaving the index definitions in the data dictionary is one way to avoid
all that.

On Sun, Dec 13, 2015 at 10:23 PM, Bill Moran <wmoran@potentialtech.com>
wrote:

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

I'd be fine swapping NONUNIQUE for ALL and defaulting to non-unique, or
flatly enforcing a rule that it won't disable the index required by an
enabled constraint.

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers