Re: [HACKERS] Disabling an index temporarily

On Mon, Dec 14, 2015 at 10:27 AM, Tom Lane wrote: > Corey Huinker 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,

Re: [HACKERS] Disabling an index temporarily

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

Re: [HACKERS] Disabling an index temporarily

On 12/13/15 9:27 PM, Tom Lane wrote: Corey Huinker 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

Re: [HACKERS] Disabling an index temporarily

On Tue, Dec 15, 2015 at 7:56 PM, Jim Nasby wrote: > On 12/13/15 9:27 PM, Tom Lane wrote: >> >> Corey Huinker writes: >>> >>> >So, I'd propose we following syntax: >>> >ALTER INDEX foo SET DISABLED >>> >-- does the SET indisvalid = false shown

Re: [HACKERS] Disabling an index temporarily

On Sun, Dec 13, 2015 at 11:03 PM, Tom Lane wrote: > Jeff Janes 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

Re: [HACKERS] Disabling an index temporarily

On Sun, Dec 13, 2015 at 10:23 PM, Bill Moran wrote: > On Sun, 13 Dec 2015 22:15:31 -0500 > Corey Huinker wrote: > > > ALTER TABLE foo DISABLE [NONUNIQUE] INDEXES > > -- same, but joining to pg_class and possibly filtering on indisunique > > I

Re: [HACKERS] Disabling an index temporarily

On Sun, Dec 13, 2015 at 1:33 AM, Oleg Bartunov wrote: > > On Sun, Dec 13, 2015 at 1:16 AM, Jaime Casanova < > jaime.casan...@2ndquadrant.com> wrote: > >> indexrelid = 'indexname'::regclass; > > > This works, but might bloat system catalog. > > +1 for the functionality. +1

Re: [HACKERS] Disabling an index temporarily

>> On Sun, Dec 13, 2015 at 1:16 AM, Jaime Casanova < >> jaime.casan...@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

Re: [HACKERS] Disabling an index temporarily

On Sun, 13 Dec 2015 22:15:31 -0500 Corey Huinker 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

Re: [HACKERS] Disabling an index temporarily

On Sun, Dec 13, 2015 at 7:27 PM, Tom Lane wrote: > Corey Huinker 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,

Re: [HACKERS] Disabling an index temporarily

Corey Huinker 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

Re: [HACKERS] Disabling an index temporarily

Bill Moran 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

Re: [HACKERS] Disabling an index temporarily

On Mon, Dec 14, 2015 at 12:27 PM, Tom Lane 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

Re: [HACKERS] Disabling an index temporarily

Jeff Janes 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,

Re: [HACKERS] Disabling an index temporarily

2015-12-14 5:34 GMT+02:00 Tom Lane : > 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

Re: [HACKERS] Disabling an index temporarily

On Sun, Dec 13, 2015 at 1:16 AM, Jaime Casanova < jaime.casan...@2ndquadrant.com> wrote: > indexrelid = 'indexname'::regclass; This works, but might bloat system catalog.

Re: [HACKERS] Disabling an index temporarily

> 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

Re: [HACKERS] Disabling an index temporarily

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

Re: [HACKERS] Disabling an index temporarily

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

Re: [HACKERS] Disabling an index temporarily

> 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.6...@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.

Re: [HACKERS] Disabling an index temporarily

On 11 December 2015 at 22:03, Joshua D. Drake wrote: > On 12/11/2015 06:25 PM, Tatsuo Ishii wrote: > >> What about inventing a new SET command something like: >> >> SET disabled_index to >> >> This adds to "disabled index list". The disabled index >> list let the planner

[HACKERS] 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

Re: [HACKERS] 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 This adds to "disabled index list". The disabled index list let the planner to disregard the indexes in the list. SET enabled_index to This removes from the disabled

Re: [HACKERS] 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 >> >> This adds to "disabled index list". The disabled index >> list let the planner to disregard the indexes in the list. >> >> SET enabled_index to >> >> This