How to create indices for Anvil data tables on custom DB?

Anvil Q&A
1

We are running the open source anvil-app-server from GitHub and host our Anvil application on our own server. Anvil ships with a built-in PostgreSQL database, which we also use for this application. There are some data tables that have a single-row reference to another table. These tables have have hundreds of thousands rows, and we often search() with just the reference to that other table. These selects can become quite slow.

Thus I wanted to create an index for these large tables on the reference/link field. However, as it turned out this a bit tricky, as Anvil has created for example this view for one of these large application data tables:

postgres=# SELECT definition
postgres-# FROM pg_views
postgres-# WHERE schemaname = 'data_tables' AND viewname = 'table_5';
                                                           definition                                                           
--------------------------------------------------------------------------------------------------------------------------------
  SELECT app_storage_data.id AS _id,                                                                                           +
     (((btrim(((app_storage_data.data -> 'kChGi1Zhutg='::text) ->> 'id'::text), '"'::text))::jsonb ->> 1))::integer AS dataset,+
     (((btrim(((app_storage_data.data -> 'd_OfvOBL8OE='::text) ->> 'id'::text), '"'::text))::jsonb ->> 1))::integer AS image,  +
     (((btrim(((app_storage_data.data -> 'eAhRvxghvLs='::text) ->> 'id'::text), '"'::text))::jsonb ->> 1))::integer AS label,  +
     ((app_storage_data.data ->> 'zgIQkdHm+VE='::text))::double precision AS x_center,                                         +
     ((app_storage_data.data ->> 'OGKtQPCb_J4='::text))::double precision AS y_center,                                         +
     ((app_storage_data.data ->> '6hm1_+NXVKg='::text))::double precision AS width,                                            +
     ((app_storage_data.data ->> 'xVPZrQEnaHg='::text))::double precision AS height,                                           +
     (app_storage_data.data ->> '1sK5XWneodM='::text) AS segmentation                                                          +
    FROM app_storage_data                                                                                                      +
   WHERE (app_storage_data.table_id = 5);

The field dataset is the one linked to another table and thus where I want to create the DB index for. But this seems to be tricky, I don’t know how exactly how to proceed in this case.

What is the “official” approach to create such an index for an Anvil data table?

1 Like
2

Hi @mfb,

The App Server doesn’t have built-in support for indices like this, but the description you’ve pasted here gives you a starting point – you can add an index to the underlying table on the expression for that column (in this case app_storage_data.data -> 'kChGi1Zhutg='::text – ideally as a partial index with the constraint app_storage_data.table_id = 5 so you don’t index all the other rows from other tables). This is analagous to what happens on a Dedicated plan when you configure an index.

Of course, at this point you’re messing with your Postgres database by hand, which means that automatic migrations might have trouble (eg if you take a migration that deletes that table, you’ll probably need to delete the index first). I recommend the Postgres docs – there’s a lot, but there’s usually enough information for whatever you’re trying to do!

3 Likes
3

Thank you very much!