# Generated by Django 2.2.12 on 2020-04-30 00:35

from django.db import migrations


class Migration(migrations.Migration):
    atomic = False

    dependencies = [
        ('zerver', '0278_remove_userprofile_alert_words'),
    ]

    operations = [
        migrations.RunSQL("""
        CREATE INDEX CONCURRENTLY IF NOT EXISTS zerver_message_recipient_upper_subject ON zerver_message (recipient_id, upper(subject::text), id DESC NULLS LAST);
        """),
        migrations.RunSQL("""
        CREATE INDEX CONCURRENTLY IF NOT EXISTS zerver_message_recipient_subject ON zerver_message (recipient_id, subject, id DESC NULLS LAST);
        """),
    ]

Add Message indexes on (recipient_id, subject).
migrations: Add Message indexes on (recipient_id, subject).

Our previous set of indexes for the Message table did not contain
anything to optimize queries for all the messages in a topic in an
organization where the same topic name might appear in 10,000s of
messages in many streams.

We add two indexes here to support common queries
* A `(recipient_id, upper(subject), id)` index to support
  "Fetch all messages from a topic" queries.
* A `(recipient_id, subject, id)` index to support
  "Fetch all messages by topic"

We use the `DESC NULLS last` on both indexes because we almost always
want to query from the "Latest N messages" on a topic, not the
"Earliest N messages".

These indexes dramatically improve the performance of fetching topic
history (which remains not good enough in my opinion; we'll likely
need caching to make it nice), and more importantly make it possible
to check quickly which users have sent messages to a topic for the
"Topics I follow" feature.

Fixes part of #13726.
