Open edhelas opened 2 years ago
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
@prefiks It that ticket still relevant or was it sorted out already ?
@edhelas: What is the current situation?
Environment
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.
Indexes are maybe missing in other places in the schema.