Re: Invalid indexes should not consume update overhead

On Sunday, July 17, 2016, Jan Wieck <jan(at)wi3ck(dot)info> wrote:

>
>
> On Sun, Jul 17, 2016 at 4:42 PM, Rader, David <davidr(at)openscg(dot)com
> <javascript:_e(%7B%7D,'cvml','davidr(at)openscg(dot)com');>> wrote:
>
>>
>>
>> On Sunday, July 17, 2016, Peter Geoghegan <pg(at)heroku(dot)com
>> <javascript:_e(%7B%7D,'cvml','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> 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
>
>
> Yes parallel multi index build would provide actual benefit. Otherwise
the disable/rebuild is just syntactic sugar that makes scripting bulk
operations simpler.

>
>
>
>>
>>
>>
>>
>>>
>>> --
>>> Sent via pgsql-bugs mailing list (pgsql-bugs(at)postgresql(dot)org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-bugs
>>>
>>
>>
>> --
>> --
>> David Rader
>> davidr(at)openscg(dot)com <javascript:_e(%7B%7D,'cvml','davidr(at)openscg(dot)com');>
>>
>>
>
>
> --
> Jan Wieck
> Senior Postgres Architect
> http://pgblog.wi3ck.info
>

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