Re: Invalid indexes should not consume update overhead

On Sunday, July 17, 2016, Peter Geoghegan <pg(at)heroku(dot)com> wrote:

> On Sun, Jul 17, 2016 at 4:41 AM, Tomasz Ostrowski
> <tometzky+pg(at)ato(dot)waw(dot)pl <javascript:;>> wrote:
> > That wouldn't solve my problem, which is that I need a way to disable
> > indexes before large update. I believe (but I'm not sure) that Oracle has
> > this concept:
> > ALTER INDEX [INDEX_NAME] UNUSABLE;
>
> I think that this must make the index unusable to the optimizer. The
> idea being that you can see the impact of dropping the index without
> actually doing so, reserving the ability to back out (mark the index
> usable once more rather than actually dropping it) if it turns out
> that the index is of some use.
>
> If it simply made the index unusable while removing any ongoing
> obligation for writes to maintain the index, then what's the point in
> supporting this at all? You need to be able to mark it usable again.
>
> --
> Peter Geoghegan

For example, in SQL Server you can "alter index disable". If you are about
to do a lot of bulk operations. But there is no "re-enable"; instead you
have to "alter index rebuild" because as has been said on this thread you
don't know what has changed since the disable.

Basically this is very similar to dropping and recreating indexes around
bulk loads/updates.

>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs(at)postgresql(dot)org <javascript:;>
> )
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>

--
--
David Rader
davidr(at)openscg(dot)com