Additional database indices

If you have a large pretix database, some features such as search for orders or events might turn pretty slow. For PostgreSQL, we have compiled a list of additional database indexes that you can add to speed things up. Just like any index, they in turn make write operations insignificantly slower and cause the database to use more disk space.

The indexes aren’t automatically created by pretix since Django does not allow us to do so only on PostgreSQL (and they won’t work on other databases). Also, they’re really not necessary if you’re not having tens of thousands of records in your database.

However, this also means they won’t automatically adapt if some of the referred fields change in future updates of pretix and you might need to re-check this page and change them manually.

Here is the currently recommended set of commands:

CREATE EXTENSION pg_trgm;

CREATE INDEX CONCURRENTLY pretix_addidx_event_slug
    ON pretixbase_event
    USING gin (upper("slug") gin_trgm_ops);
CREATE INDEX CONCURRENTLY pretix_addidx_event_name
    ON pretixbase_event
    USING gin (upper("name") gin_trgm_ops);
CREATE INDEX CONCURRENTLY pretix_addidx_order_code
    ON pretixbase_order
    USING gin (upper("code") gin_trgm_ops);
CREATE INDEX CONCURRENTLY pretix_addidx_voucher_code
    ON pretixbase_voucher
    USING gin (upper("code") gin_trgm_ops);
CREATE INDEX CONCURRENTLY pretix_addidx_invoice_nu1
    ON "pretixbase_invoice" (UPPER("invoice_no"));
CREATE INDEX CONCURRENTLY pretix_addidx_invoice_nu2
    ON "pretixbase_invoice" (UPPER("full_invoice_no"));
CREATE INDEX CONCURRENTLY pretix_addidx_organizer_name
    ON pretixbase_organizer
    USING gin (upper("name") gin_trgm_ops);
CREATE INDEX CONCURRENTLY pretix_addidx_organizer_slug
    ON pretixbase_organizer
    USING gin (upper("slug") gin_trgm_ops);
CREATE INDEX CONCURRENTLY pretix_addidx_order_email
    ON pretixbase_order
    USING gin (upper("email") gin_trgm_ops);
CREATE INDEX CONCURRENTLY pretix_addidx_order_comment
    ON pretixbase_order
    USING gin (upper("comment") gin_trgm_ops);
CREATE INDEX CONCURRENTLY pretix_addidx_order_event_date_id
    ON public.pretixbase_order (event_id, datetime, id);
CREATE INDEX CONCURRENTLY pretix_addidx_orderpos_name
    ON pretixbase_orderposition
    USING gin (upper("attendee_name_cached") gin_trgm_ops);
CREATE INDEX CONCURRENTLY pretix_addidx_orderpos_secret
    ON pretixbase_orderposition
    USING gin (upper("secret") gin_trgm_ops);
CREATE INDEX CONCURRENTLY pretix_addidx_orderpos_email
    ON pretixbase_orderposition
    USING gin (upper("attendee_email") gin_trgm_ops);
CREATE INDEX CONCURRENTLY pretix_addidx_ia_name
    ON pretixbase_invoiceaddress
    USING gin (upper("name_cached") gin_trgm_ops);
CREATE INDEX CONCURRENTLY pretix_addidx_ia_company
    ON pretixbase_invoiceaddress
    USING gin (upper("company") gin_trgm_ops);
CREATE INDEX CONCURRENTLY pretix_addidx_orderpos_email_upper
    ON public.pretixbase_orderposition (upper((attendee_email)::text));
CREATE INDEX CONCURRENTLY pretix_addidx_voucher_code_upper
    ON public.pretixbase_voucher (upper((code)::text));
CREATE INDEX CONCURRENTLY pretix_addidx_logentry_event_date_id
    ON public.pretixbase_logentry (event_id, datetime, id);
CREATE INDEX CONCURRENTLY pretix_addidx_logentry_event_cid_date_id
    ON public.pretixbase_logentry (event_id, content_type_id, datetime, id);

Also, if you use our pretix-shipping plugin:

CREATE INDEX CONCURRENTLY pretix_addidx_sa_name
    ON pretix_shipping_shippingaddress
    USING gin (upper("name") gin_trgm_ops);
CREATE INDEX CONCURRENTLY pretix_addidx_sa_company
    ON pretix_shipping_shippingaddress
    USING gin (upper("company") gin_trgm_ops);