This action will delete this post on this instance and on all federated instances, and it cannot be undone. Are you certain you want to delete this post?
This action will delete this post on this instance and on all federated instances, and it cannot be undone. Are you certain you want to delete this post?
This action will block this actor and hide all of their past and future posts. Are you certain you want to block this actor?
This action will block this object. Are you certain you want to block this object?
I replaced five indexes* on the relationships table with two**, improved query performance in at least one case, and cut the size of the database down by 11.4% (98MB).
Lessons (finally) learned:
#ktistec #sqlite #optimization
* The original five:
CREATE INDEX idx_relationships_type_from_iri_created_at ON relationships (type ASC, from_iri ASC, created_at DESC); CREATE INDEX idx_relationships_from_iri_created_at_type ON relationships (from_iri ASC, created_at DESC, type ASC); CREATE INDEX idx_relationships_type_to_iri ON relationships (type ASC, to_iri ASC); CREATE INDEX idx_relationships_to_iri_type ON relationships (to_iri ASC, type ASC); CREATE INDEX idx_relationships_type_id ON relationships (type ASC, id ASC);
* The final two:
CREATE INDEX idx_relationships_type ON relationships (type ASC); CREATE INDEX idx_relationships_to_iri ON relationships (to_iri ASC);
@toddsundsted Too many indexes kills insert performance as well.
@relistan yes 💯 i forgot to mention that one!