Re: Invisible Indexes

On 5 July 2018 at 13:31, Peter Geoghegan <pg@bowt.ie> wrote:
> On Wed, Jul 4, 2018 at 6:26 PM, David Rowley
> <david.rowley@2ndquadrant.com> wrote:
>> Or would it be insanely weird to just not allow setting or unsetting
>> this invisible flag if indcheckxmin is true?  I can't imagine there
>> will be many people adding an index and not wanting to use it while
>> it's still being created.  I think the use case here is mostly people
>> wanting to test dropping indexes before they go and remove that 1TB
>> index that will take days to build again if they're wrong.
>
> I'm surprised that that use case wasn't the first one that everyone
> thought of. I actually assumed that that's what Andrew had in mind
> when reading his original message. I only realized later that it
> wasn't.

hmm. Maybe I missed any other use case.  The mention of hypothetical
indexes seems a bit lost on this thread. Andrew's proposal mentions
that an invisible index will just not be considered by the planner.
I'd very much assume here that the index must exist on disk, and
there's not much hypothetical about that.

It seems to me that there would be exactly 1 place in the backend that
the new bool flag would need to be checked, and that's in
get_relation_info() to skip any indexes that are "invisible".  pg_dump
would, of course, need to know about this flag too.

Like Andrew, I'm not much of a fan of the GUC idea.  Testing a plan
without an index could just be a BEGIN; ALTER INDEX; EXPLAIN;
ROLLBACK; operation. It seems much neater not to spread the properties
of an index all over the place when we have a perfectly good table to
store index properties in.  Unsure why Tom thinks that's ugly.

FWIW I have also seen customers asking if they can test drop an index
by setting indisready to false. Naturally, people are often a bit
scared to confirm messing around with catalogue tables on a busy
production server is fine.

Also, FWIW, I'd not bother with a CREATE INDEX syntax for this and
leave it to ALTER INDEX.  I also think that ENABLE/DISABLE is nicer
than VISIBLE/NOT VISIBLE.  Those are already unreserved words too.
Although, perhaps pg_dump would prefer us to have syntax for this in
CREATE INDEX since it could describe the new object in a single
statement.

-- 
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services