Support CREATE INDEX and DROP INDEX for system views

[frankmcsherry]

System views, such as those installed around logging, do not allow users to drop them (I believe as parts of the catalog presume they will exist). However, it would be helpful for users to be able to create and drop indexes for these views, not changing their definitions but changing whether they are materialized or not, as this affects performance of the system and the materialization of the views may not be helpful for some users.

Sources are a different issue, in that the materialization of the source is its only definition. They should not be dematerialized without a careful think about what that means.

[benesch]

Just want to jot down that while this is definitely a nice-to-have, the implementation of this will probably be painful at the moment, since the coordinator/catalog aren't equipped at the moment to have user-space indexes on system views.

After #3752, there's a pretty reasonable workaround, I think, whereby you can essentially create an index on a logging view via CREATE MATERIALIZED VIEW log_idx AS SELECT * FROM log_view. You'll have to remember to use log_idx in your queries instead of log_view, which is a bit unfortunate, but it's probably good enough for any internal use cases that need support for this urgently.

[frankmcsherry]

This is now also relevant for system tables, system materialized views (e.g. introspection), and any other system things that involve persist by default but a user might want to have go faster.

[frankmcsherry]

This came up recently, in that @rjnn noticed the non-uniformity of response times to SHOW commands as a function of the complexity of the queried object. For example, SHOW OBJECTS; takes quite a while as it pulls in ten different sources, but would be rather prompt as an index, or (slower) materialized view. Specifically, it was taking about a second, whereas pulling from an indexed form of this would probably be ~10ms.

[ggnall]

Idea Surfaces team discussed: In order to make SHOWS always fast we could have default indexes to serve from memory and not persist.

Users can't create their own indexes in these cases but they can't see the SHOW queries anyway.

[benesch]

How would that work with clusters, though? Would the indexes be force-created in every cluster? Only in the default cluster?

[uce]

I've removed the triage label, because it's reserved for bugs (?).

[jkosh44]

Now that we have the mz_introspection cluster, we could automatically create an index for all SHOW commands on the mz_introspection cluster. Then automatically run all SHOW commands in the mz_introspection cluster regardless of what the current cluster is.

[jkosh44]

Now that we have the mz_introspection cluster, we could automatically create an index for all SHOW commands on the mz_introspection cluster. Then automatically run all SHOW commands in the mz_introspection cluster regardless of what the current cluster is.

I'm going to open a new issue for this a prioritize it above this issue.