Missing indexes in Pubsub SQL tables

[edhelas]

Environment

• ejabberd version: 21.12
• OS: Linux (Debian Stable)
• Installed from: deb package

Bug description

The current Pubsub SQL tables are missing some indexes. ejabberd often query those tables using their nodeid. Those ids are foreign-keys but are not indexed which can cause some performances issues with a lot of data.

I manually created those indexes in my PostgreSQL DB using the following commands.

create index pubsub_node_option_nodeid_index on pubsub_node_option (nodeid);
create index pubsub_item_nodeid_index on pubsub_item (nodeid);

Indexes are maybe missing in other places in the schema.

[prefiks]

So that first index is already there, second one doesn't exist, we have index on (nodeid, itemid), which from db explain seems to be used for most queries, and as we also return itemid, it's probably not that much extra cost. What is hoever missing are indexes for (nodeid, modification) and (nodeid, creation) and some queries that we are using use those two later column for sorting, clearly something that adding those indexes will help.

So i think i will add add two indexes with timestamps, as per explain select it seems having or not index on just nodeid seems to show same cost of queries

[mremond]

@prefiks It that ticket still relevant or was it sorted out already ?