What we found is that the search endpoint runs two queries:
- findAll -> paginated list (ORDER BY createTime DESC LIMIT 50)
- countAll -> exact total matching rows for pagination
1) Missing indexes on plugin_devicelog_log
Out of the box the table only has a primary key. With no index on createTime, the list query did a full sequential scan + sort over the entire table (~14.6 s in EXPLAIN ANALYZE).
After adding:
CREATE INDEX CONCURRENTLY idx_devicelog_log_createtime
ON plugin_devicelog_log (createTime DESC);
the same list query dropped to ~3.983 ms (index scan, stop after 50 rows). Roughly half (or more) of the page load was fixed by this alone.
We’d suggest shipping indexes with the plugin schema, especially (createTime DESC)
2) Remaining bottleneck: exact countAll
Even with indexes in place, countAll still takes ~11.5 s because it must count all matching rows (~18M). Together with the (now fast) list query, the API remains ~10 s.
So: indexes help the list a lot; the exact total count dominates for large tables.
Suggestions to improve performance without changing behaviour for small deployments, options that would help large ones:
- Defer the count
Return the first page immediately; load the total asynchronously so the UI feels fast.
- Ship the indexes above in the official plugin changelog so all installations benefit.