Information about Indexing

Anvil Q&A
1

Hi, I believe I have a “business plan” with “extra server resources” (SS attached). I don’t seem to find option to add indexes to a DB column. Where can I find it?
image

2

Hello @rehan

Does the following help?

3

In the above link it says:

Users on the Business Plan with Extra Server Resources or Enterprise Plan above have the option to add further indexes on particular Data Table columns to speed up more advanced queries using operators in the anvil.tables.query namespace.

But I can’t find any option in UI or code to add indexes.
Where can I find that option?

4

It’s in the last 3 items here:
image

4 Likes
5

If I apply above indexing, will it lock the database?
If yes, for how much time will it be locked?

6

I don’t know if or for how long it will lock the database.

I remember I was scared too when I used it because I didn’t want other apps with large tables with thousands of rows to be blocked because a new app with an empty table is indexing it. I think that under the hood, all tables are one postgres table, so indexing one may touch them all.

I don’t know if postgres is smart enough to only lock some views/app tables (the table you are indexing) of that big table, or only the tables of one app, or all the tables of all the apps in the account.

I know that the few times I used it, I kept an eye on the locking, and I didn’t notice any locking at all. (All my apps share the same Users and a few other tables.)

2 Likes
7

If I try to add Pattern matching indexing I am getting the following error.
Is there any pre-indexing step that I am missing? because i couldn’t find anything in docs related to this.
error

8

I can’t read any text message in your snapshot, but there is nothing you need to do. Clicking on that checkbox it’s all you need to do to apply or remove an index (… and, perhaps, to lock all the tables of all the apps on your account?? That’s scary, I would like some feedback from the UI or better documentation in this regard!!)

I remember that those checkbox used to work in the old IDE, but they were broken when the new IDE was released. I reported the problem to the support and they quickly did the indexing for me for the app I was working on, and later fixed the UI.

I haven’t played with that indexing in ages, so I don’t know if there are UI problems now.

2 Likes
9

Hi @rehan,

Sorry about this - it was happening because your dedicated database was created long before we had support for custom indexes, and needed some manual tweaking to support them. You should be able to add Pattern Matching indexes now!

1 Like