{ "@context":[ "https://www.w3.org/ns/activitystreams", {"Hashtag":"as:Hashtag"} ], "published":"2024-01-21T19:49:04.300Z", "attributedTo":"https://epiktistes.com/actors/toddsundsted", "replies":"https://epiktistes.com/objects/h4jE5pNpqt0/replies", "to":["https://www.w3.org/ns/activitystreams#Public"], "cc":["https://epiktistes.com/actors/toddsundsted/followers"], "content":"

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:

  1. You can have too many indexes. At best, this makes the database larger. In at least one case, however, this caused the query planner to pick a less effective index, which resulted in worse performance.
  2. Unless you understand the data well, it is hard to know what indexes you are going to need up front. For example, on the relationships table, it's clear in retrospect that an index on the to_iri column has better selectivity than an index on the from_iri column—and, in fact, no index is even necessary on the from_iri column. For reasons of symmetry, I created both when I created the table. I'll go so far as to say, don't even create indexes until/unless you can analyze actual data. (Aside: the SQLite3 function likelihood is an excellent way to hint about that data to the query planner.)
  3. Ordering results using the automatically assigned, monotonically increasing id primary key behaves identically to ordering by something like created_at, so order by id and save yourself an index on created_at.

#ktistec #sqlite #optimization

* The original five:

CREATE INDEX idx_relationships_type_from_iri_created_at\r\n    ON relationships (type ASC, from_iri ASC, created_at DESC);\r\nCREATE INDEX idx_relationships_from_iri_created_at_type\r\n    ON relationships (from_iri ASC, created_at DESC, type ASC);\r\nCREATE INDEX idx_relationships_type_to_iri\r\n    ON relationships (type ASC, to_iri ASC);\r\nCREATE INDEX idx_relationships_to_iri_type\r\n    ON relationships (to_iri ASC, type ASC);\r\nCREATE INDEX idx_relationships_type_id\r\n    ON relationships (type ASC, id ASC);


* The final two:

CREATE INDEX idx_relationships_type\r\n    ON relationships (type ASC);\r\nCREATE INDEX idx_relationships_to_iri\r\n    ON relationships (to_iri ASC);
", "mediaType":"text/html", "attachment":[], "tag":[ {"type":"Hashtag","name":"#ktistec","href":"https://epiktistes.com/tags/ktistec"}, {"type":"Hashtag","name":"#sqlite","href":"https://epiktistes.com/tags/sqlite"}, {"type":"Hashtag","name":"#optimization","href":"https://epiktistes.com/tags/optimization"} ], "type":"Note", "id":"https://epiktistes.com/objects/mLbDJyZKkJQ" }