Investigate custom indexes

[DeD1rk]

Our database currently has only the default indexes suggested by Django (on pk's and foreignkeys mainly). Perhaps adding some more indexes could help increase performance.

Particularly, the most obvious case where improving performance would be great is event registrations. This is just one example, and there are more cases where indexes may be helpful.

We currently have the following:

concrexit=# \d events_eventregistration
                                       Table "public.events_eventregistration"
      Column      |           Type           | Collation | Nullable |                     Default                     
------------------+--------------------------+-----------+----------+-------------------------------------------------
 id               | integer                  |           | not null | nextval('events_registration_id_seq'::regclass)
 name             | character varying(50)    |           |          | 
 date             | timestamp with time zone |           | not null | 
 date_cancelled   | timestamp with time zone |           |          | 
 present          | boolean                  |           | not null | 
 event_id         | integer                  |           | not null | 
 member_id        | integer                  |           |          | 
 payment_id       | uuid                     |           |          | 
 remarks          | text                     |           |          | 
 special_price    | numeric(8,2)             |           |          | 
 alt_email        | character varying(254)   |           |          | 
 alt_phone_number | character varying(20)    |           |          | 
Indexes:
    "events_registration_pkey" PRIMARY KEY, btree (id)
    "events_registration_event_id_75b6f198" btree (event_id)
    "events_registration_member_id_4e016547" btree (member_id)
    "events_registration_member_id_event_id_483dbac6_uniq" UNIQUE CONSTRAINT, btree (member_id, event_id)
    "events_registration_payment_id_key" UNIQUE CONSTRAINT, btree (payment_id)
Foreign-key constraints:
...

When determining the queue position of a registration, we make extensive use of the date and date_cancelled columns, so perhaps adding an index on those would cause a nice speedup.

[ColonelPhantom]

Can we analyze some data from Postgres itself, for example with pg_stat_element? Might be better than just doing guesswork.

[DeD1rk]

Sure, you can just open a shell into the postgres docker om staging. But it's not realy practical to try stuff out there. Easiest is probably to run pg locally and try out that way, of course with a big lot of fixtures