Check indices of ALA tables

[jirik]

After 1 mil records admin started to be slow

• https://docs.djangoproject.com/en/1.11/topics/performance/

[Darkless012]

Interesting issue. I've also found this article: https://medium.com/@hakibenita/things-you-must-know-about-django-admin-as-your-app-gets-bigger-6be0b0ee9614

[jirik]

At least part of the problem is related to slow "order by" over multiple columns.

Possible actions

• Use appropriate multicolumn index or indices

  `CREATE INDEX ala_observation_multi_idx ON ala_observation USING btree (phenomenon_time_range desc, feature_of_interest_id asc, procedure_id asc, observed_property_id asc, id desc);`

  It should be combined with __id postfix for ordering of joined models:

      ordering = ['-phenomenon_time_range', 'feature_of_interest__id', 'procedure__id',
                  'observed_property__id']

• Use also custom query expressions for ordering [django 2.0]

Performance would be probably similar as the previous option.

`CREATE INDEX ala_observation_multi_idx ON ala_observation USING btree (lower(phenomenon_time_range) desc, feature_of_interest_id asc, procedure_id asc, observed_property_id asc, id desc);`

# Django 2.0 only https://docs.djangoproject.com/en/2.0/ref/models/options/#ordering
from django.db.models import Func, F
...
        ordering = [Func(F('phenomenon_time_range'), function='LOWER'), 'feature_of_interest__id', 'procedure__id',
                    'observed_property__id']

Similarly, we can setup duration function:

CREATE OR REPLACE FUNCTION duration(TSTZRANGE) RETURNS INTERVAL AS
$func$
    SELECT UPPER($1) - LOWER($1);
$func$ LANGUAGE SQL STABLE;

And use it also for ordering.

Some sources:

• https://stackoverflow.com/questions/22108477/perform-this-hours-of-operation-query-in-postgresql/22111524#22111524
• https://gist.github.com/peter/397009
• https://stackoverflow.com/questions/24844842/poor-performance-on-contained-by-query-for-tstzrange
• https://stackoverflow.com/questions/26625614/select-query-with-offset-limit-is-too-much-slow