On Sun, Jul 17, 2016 at 4:42 PM, Rader, David <davidr@openscg.com> wrote:
>
>
> On Sunday, July 17, 2016, Peter Geoghegan <pg@heroku.com> wrote:
>
>> On Sun, Jul 17, 2016 at 4:41 AM, Tomasz Ostrowski
>> <tometzky+pg@ato.waw.pl> 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.
>
I would say that materially there is no difference. What would make a
difference would be
if it were possible to ALTER TABLE DISABLE INDEXES and then REINDEX to
build them.
That is, it would be different if rebuilding multiple indexes at once had a
substantial advantage,
like let's say it would scan the heap only once, building all the sort sets
in parallel.
Regards, Jan
>
>
>
>
>>
>> --
>> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-bugs
>>
>
>
> --
> --
> David Rader
> davidr@openscg.com
>
>
--
Jan Wieck
Senior Postgres Architect
http://pgblog.wi3ck.info