Additional indices on materialized views

[ktosiek]

Is there any way to have custom indices on materialized views, except for the one used for concurrent update?

If not, would it be possible to at least get a signal after the view is created (but still in the same transaction)? Then we'd be able to build additional indices in the same transaction.

[scott-w]

Hi @ktosiek would you be able to provide an example of the kind of thing you'd be wanting to do?

[ktosiek]

I want to have additional indices, other than the UNIQUE one used for concurrent updates, to optimize some queries. For example, if I had a view that flattens a hierarchy of groups to show all "user in group" relations, I'd like to have indices for both user_id and group_id, as I'd query both "is user in group" and "which users are in this group".

For Django 1.11 it would be possible to use the new Class-based Index API, no idea how it would look for older versions.

I'll make a PR with the post-creation signal, that'd make app-specific customisations possible.

[scott-w]

I think the post_sync signals are a great start.

If anything in particular is needed to support the Class-based Index API, we should probably look to bump the version too as it likely breaks compatibility with Django <1.11.

[meric]

I built a django postgresview library for my own use a couple of months ago, but to my surprise I discovered the same thing here... Anyway, in my library, the unique index declared for refresh concurrently is declared as Meta.unique_together.

from django.db.backends.postgresql import schema

# in the metaclass...
    sql_create_unique_index = (
        "CREATE UNIQUE INDEX %(name)s on %(table)s (%(columns)s);")

    def _create_unique_sql(self, columns):
        editor = schema.DatabaseSchemaEditor(connection)
        return self.sql_create_unique_index % {
            "table": editor.quote_name(self._meta.db_table),
            "name": editor.quote_name(
                editor._create_index_name(self, columns, suffix="_uniq")),
            "columns": ", ".join(editor.quote_name(column)
                for column in columns),
        }

and then after the view is created:

        if self._meta.unique_together:
            for unique_together in self._meta.unique_together:
                columns = [self._meta.get_field(field).column
                    for field in unique_together]
                cursor.execute(self._create_unique_sql(columns))

In case you want to write something like this in your post_sync signal.

EDIT: I uploaded here https://github.com/meric/django-postgresviews.

[reb197]

I did this to create indexes defined in the Meta of the class in the usual Django way:

@receiver(view_synced)
def on_view_synced(sender, update, force, **kwargs):
    if force:
        for index in sender._meta.indexes:
            connection.schema_editor().add_index(sender, index)