10 ms·

Subtleties of SQLite Indexes

137 points by emschwartz 1y ago

porridgeraisin 1y ago
> multiple indices Yes, sqlite uses only one index per table in FROM clause... Except for when it can use the OR optimization [1] > Left to right, no skipping, stops at the first range I don't know if we need a soundbite for this, nor is it sqlite specific. This is a direct consequence of the fact that indexes are basically just sorted lists you can binary search over. They are sorted left to right (you have to choose some order anyhow) meaning all the other orderings cannot avail of the speedup, and thus sqlite chooses to not use it..... Except if left column of index is measured to be low cardinality by ANALYZE [2]'s output in the sqlite_stats table, in which case it is ok to scan over it and then binary search right column. More at skip-scan optimization [3]. [1] https://www.sqlite.org/optoverview.html#evaluating_or_constraints_separately_and_taking_the_union_of_the_result https://www.sqlite.org/optoverview.html#evaluating_or_constr... [2] https://www.sqlite.org/lang_analyze.html https://www.sqlite.org/lang_analyze.html [3] https://www.sqlite.org/optoverview.html#the_skip_scan_optimization https://www.sqlite.org/optoverview.html#the_skip_scan_optimi...
nikeee 1y ago
I use the mental model of nested maps for "column order matters". For example, an index "published, low_quality_probability, lang" is just a Map<date, Map<number, Map<lang, rowId>>> in my mental model. These maps are ordered by the order the index possesses. That explains why column order matters and why one cannot skip columns and why it stops at range queries. Just imagine getting a final rowId from these nested maps and you'll see why the index works for some queries and doesn't for others.
- adzm 1y ago
  I use this same approach to explain indexes to database novices; it usually helps a lot, especially with how the leaf nodes / included indexes work as well (using that info instead of or in addition to rowid/primary key as the last value there)
- emschwartz 1y ago
  That is a helpful way of thinking about it. Thanks for sharing!
- immibis 1y ago
  It's actually a List<Tuple<date, number, rowid>>> in sorted order, and queries are more akin to binary search (they are not actually binary but use a wider fanout depending on many factors)
  - Sesse__ 1y ago
    It's _not_ just a list in sorted order; if it were, you could not insert efficiently into it. But the tuple is the right mental model, indeed.
  - nikeee 1y ago
    Yeah it might be closer to what's actually happening but it doesn't make it obvious why something doesn't work. The map model doesn't even cover non-unique indices. Or different type of indexes.
- masklinn 1y ago
  It’s way easier if you think of the indexes as tuple keys in a binary tree. Because they’re tuple keys in a b-tree. That also explains how ranges work efficiently.
  - jasonwatkinspdx 1y ago
    Yeah, if you're going to work with databases regularly I think it's worth learning how b-trees work. It'll make a lot of things much more intuitive. If you wanna get a very complete grounding in how the big rdbmses work, Andy Pavlo's lectures and class notes are fantastic.
    - runlaszlorun 1y ago
      I definitely second Andy Pavlo's lectures.
- deleted 1y ago
  [deleted]
ComputerGuru 1y ago
Not a great article; I clicked expecting something super technical about SQLite internals and found a mix of rdbms basics and some misconceptions. The limitations in the blog post aren't really specific to SQLite (for the most part), they're just how indexes (indices) and database engines work across the board. And some of the things phrased as "SQLite [can't] do this" is stuff that wouldn't make sense to do in the first place. If you understand what (multi-column) indexes are at the lowest level (i.e. what data structure they represent, how they are used by the database, what the code reading them would look like) then all of this makes immediate and natural sense. Indexes aren't magic. They're just a shortcut to help the the db engine find your data more effectively. This doesn't require super complex understanding of data structures, btw. The same limitations and advice would apply if you were trying to, for example, search a stack of resumes sorted by one or more attributes. If you have them sorted by position, years of experience, and applicant's last name; you wouldn't be able to quickly grab all resumes for the HR Manager position that have a last name starting with J - after all, you sorted them by years of experience first! It's physically not possible! You can only use the first part of that index, the position, to jump to HR Manager resumes; then you would need to manually go through them one-by-one to grab only the ones starting with J for each years-of-experience subgroup (if any).
- some_old_guy 1y ago
  The fact that it stops at the first range isn't intuitive to me at all, and I've been using sqlite for 20 years now. Given a covering index on (thing_date, thing_color) I would think a scan would not be needed for the query: select thing_date, thing_color where thing_date < date and thing_color = 'blue' I also can't think of a reason this is the case given the underlying data structures.
  - ComputerGuru 1y ago
    It's a flattened tree, and you've used the index to reach a point where you have multiple child nodes that meet the precondition thing_date < 17. Some, but not all, of these have thing_color = blue, but the important part is that they're non-consecutive (the index can only give you one point-of-entry, so the data has to be consecutive or you need to do some amount of scan). I asked an llm to give me an ascii representation so it'll be easier to see what I mean; consider the case where you want thing_date < 17 and thing_color = blue, the results that match are marked with an asterisk, but note that there's no way to get to them (ALL of them) directly: Root (internal) +---------------------+ | keys: 14 17 | +----+--------+--------+ | | | v v v +----------------+ +----------------+ +----------------+ | Leaf P0 | | Leaf P1 | | Leaf P2 | | (dates 10-13) | | (dates 14-16) | | (dates 17-20) | |----------------| |----------------| |----------------| | 10 | red |101 | | 14 | blue |141| | 17 | green |171| | 11 | blue |111 |*| 14 | red |142| | 18 | blue |181| | 12 | green |121 | | 15 | yellow|151| | 19 | red |191| | 13 | red |131 | | 16 | blue |161|*| 20 | green |201| +----------------+ +----------------+ +----------------+ Go back to my resume's example: you have the resumes sorted by application timestamp (chronologically) and position (alphabetically). You want to find all resumes received last Tuesday where position was "Senior Architect". You literally can't skip to the point where the next n consecutive results will be the results you seek, because you can't temporarily re-order them and within the subset of resumes where date > monday and date < wednesday, there may be multiple for "Senior Architect" but they didn't arrive one-after-the-other so they're interspersed with results you don't want. The correct to do here would be (if this is always the shape of the query you expect to get) to sort the resumes by (position, timestamp) instead, at which point you absolutely CAN jump to the position that guarantees the next results are (all the results) of the query ("Senior Architect", last Tuesday). One important thing to keep in mind is that a SCAN is not the end of the world. You know your data and you know when it's ok (you expect most of the results to match and a few to be filtered out, in which case you're really not saving much time anyway) and when it's not (the scan will cover a huge range that contains only a few, widely interspersed results and you have no way of terminating early). EDIT: In response to your exact question however, note that with a covering index you might not end up with a SCAN (i.e. you don't hit the table) even though it uses only the partial index (thing_date) and not (thing_date, thing_color)! It's still possible for it to avoid hitting the backing table and might return the results directly from the index - something it wouldn't have been able to do if the index was only (thing_date).
Ameo 1y ago
The main takeaway from this for me is that SQLite’s query planner seems to be pretty limited. It’s reliant on stuff like the order in which WHERE conditions are specified, isn’t able to use multiple indexes in queries in many cases, bails out to scans when a variety of different operations show up in queries, etc. It might be the case that SQLite has a simpler or less sophisticated query planner than other databases like Postgres or MariaDB, but in my experience those DBs struggle a lot with good querying planning as well. I’ve spent many hours in the past with issues like Postgres suddenly starting to ignore an index entirely because its computed table data distribution statistics got out of balance, or having to add manual annotations to MariaDB queries like STRAIGHT_JOIN in order to get a query to run faster. I’m guessing that this is a really hard problem since it doesn’t seem to be really “solved” by any major DB vendor I’ve seen. A lot of modern DB engines like Clickhouse tend to just work around this problem by being so fast at full table scans that they don’t even need any sophisticated indexing set up at all.
- emschwartz 1y ago
  Just to clarify one thing: the order of WHERE conditions in a query does not matter. The order of columns in an index does.
  - Ameo 1y ago
    It definitely does matter, especially in cases where the conditions are on non-indexed columns or there are CPU-intensive search operations like regex, string ops, etc. I just ran this test locally with a table I created that has 50 million rows: ``` » time sqlite3 test.db "select count() from test WHERE a != 'a' AND a != 'b' AND a != 'c' AND a != 'd' AND b != 'c' AND d != 'd' AND e != 'f' AND f = 'g'" sqlite3 test.db 5.50s user 0.72s system 99% cpu 6.225 total » time sqlite3 test.db "select count() from test WHERE f = 'g' AND a != 'a' AND a != 'b' AND a != 'c' AND a != 'd' AND b != 'c' AND d != 'd' AND e != 'f'" sqlite3 test.db 1.51s user 0.72s system 99% cpu 2.231 total ``` The only difference is swapping the `f = 'g'` condition from last to first. That condition never matches in this query, so it's able to fail fast and skip all of the work of checking the other conditions.
- SkiFire13 1y ago
kccqzy 1y ago
> It's worth being careful to only add indexes that will be used by real queries. This reminds me of a technique used by Google App Engine SDK a long time ago before it was called cloud. Basically in development mode, the SDK captures the kind of queries you make, and then automatically add any index that would speed up this query into a configuration file. You then later deploy with this configuration file, which tells the production data store to create such indexes. I thought this was a genius idea. Not sure if something similar exists for SQLite.
- deleted 1y ago
  [deleted]
- Waterluvian 1y ago
  This seems like the kind of tool that would be useful for all indexable databases. I imagine you can’t definitively know and therefore just make this automagical. But I bet the result is a pretty solid shortlist for consideration.
- emschwartz 1y ago
  The SQLite CLI has a `.expert` command that will give index recommendations when you run queries: https://sqlite.org/cli.html#index_recommendations_sqlite_expert_ https://sqlite.org/cli.html#index_recommendations_sqlite_exp... It's not quite the same as capturing all of the queries used in development (or production), but it seems somewhat useful. I'll also note that I had an LLM generate quite a useful script to identify unused indexes (it scanned the code base for SQL queries, ran `EXPLAIN QUERY PLAN` on each one to identify which indexes were being used, and cross-referenced that against the indexes in the database to find unused ones). It would probably be possible to do something similar (but definitely imperfect) where you find all of the queries, get the query plans, and use an LLM to make suggestions about what indexes would speed up those queries.
  - zeroq 1y ago
    wow! I used to bruteforce a bunch of indexes until EXPLAIN on queries gave satisfactory results! I actually looked for a tool where I could provide a schema and all queries to get optimal indexes but never found one that actually worked.
crazygringo 1y ago
I agree with other commenters that there's nothing new or surprising here [1] if you actually understand how indexes work. Which, if you're working in SQL, you should. But the main takeaway, I disagree with. The author explains: > When I first set up Scour's database, I put a bunch of indexes on the items table without really thinking about whether they would help. For example, I had separate indexes on the published date, the language, and the quality rating. Useless. It's more important to have one or a small handful of good composite indexes on multiple columns than to have separate indexes on each column. Yes, the biggest error is throwing indexes at a table without having the slightest idea if they're helpful. But the idea that a smaller number of composite indexes is not the answer either. The answer is to go through every single query and make sure you have an index that matches the query, adding/changing indexes (including composite indexes) or rewriting queries as required. Indexes don't exist in a vacuum. They are optimizations for specific queries. Proper database design requires knowing exactly what information you will need to look up using which parameters, and designing the tables and indexes to produce that performantly. When you need to look up data in new ways, you often need to add new indexes or rearchitect tables entirely. [1] Except that the partial index match condition treats 0.9 and .9 as different. That is unexpected to me, but it is kind of in the docs at "The terms in W and X must match exactly": https://www.sqlite.org/partialindex.html https://www.sqlite.org/partialindex.html
- DaiPlusPlus 1y ago
  > Yes, the biggest error is throwing indexes at a table without having the slightest idea if they're helpful Unless you have a small number of static queries, this task isn’t really possible without an observability-solution (reporting the raw query text, the actual execution plan, and runtime and IO stats, et cetera) - otherwise it’s guesswork. …and worse-still if your application has runtime-defined queries, such as having a custom filter builder in your UI. Actually I’ll admit I have no idea how platforms like Jira are able to do that with decent performance (can anyone let me know?) I heap praise on MSSQL’s Query Store feature - which is still a relatively recent addition. I have no idea how anyone could manage query performance without needing 10x the time and effort needed to attach a profiler to a prod DB. …and if these are “edge” databases running on a 100k IoT devices then have fun, I guess.
wodenokoto 1y ago
The article is a developers journey into indexes and not a bad journey or travelogue imho. Sure, if you are a database expert, it might be disappointing but I enjoyed reading it.
- emschwartz 1y ago
  Thanks for saying that! That’s exactly how it was intended and I’m glad to hear you enjoyed it
  - andersmurphy 1y ago
    Fun read. I've been tripped up by the where in partial indexes before. Same goes for expression indexes.
    - emschwartz 1y ago
      Thank you for saying that too! Hope this explanation helped explain why, at least a little bit.
themafia 1y ago
You may not need a course. SQLite publishes a very useful guide on the query planner which goes over most of what you need to know with respect to indexes: https://www.sqlite.org/optoverview.html https://www.sqlite.org/optoverview.html
garaetjjte 1y ago
One thing I'm missing in SQLite are multi-valued indexes. I would want to have an index on func_returning_list(some_column), and query doing WHERE x IN func_returning_list(some_column) should use that index.
rtpg 1y ago
Postgres is pretty good about using index data from multiple single column indices... if you don't order results! Otherwise the planner will get in the way and do nonsense. A bunch of people spend years studying computer science and trees etc, and then when we actually need to care about that stuff the databases absolutely do not want us to declare the plan. Very annoying. Think about how people would be guided to do the right thing on indices if they had to say "go along this index to get my data" in a "planned mode" when dealing with bad performance? So many data layout and index issues would become immediately obvious. You wouldn't magically get as many benefits from DB updates, granted.

137 points by emschwartz 1y ago

> multiple indices Yes, sqlite uses only one index per table in FROM clause... Except for when it can use the OR optimization [1] > Left to right, no skipping, stops at the first range I don't know if we need a soundbite for this, nor is it sqlite specific. This is a direct consequence of the fact that indexes are basically just sorted lists you can binary search over. They are sorted left to right (you have to choose some order anyhow) meaning all the other orderings cannot avail of the speedup, and thus sqlite chooses to not use it..... Except if left column of index is measured to be low cardinality by ANALYZE [2]'s output in the sqlite_stats table, in which case it is ok to scan over it and then binary search right column. More at skip-scan optimization [3]. [1] https://www.sqlite.org/optoverview.html#evaluating_or_constraints_separately_and_taking_the_union_of_the_result https://www.sqlite.org/optoverview.html#evaluating_or_constr... [2] https://www.sqlite.org/lang_analyze.html https://www.sqlite.org/lang_analyze.html [3] https://www.sqlite.org/optoverview.html#the_skip_scan_optimization https://www.sqlite.org/optoverview.html#the_skip_scan_optimi...

nikeee 1y ago

I use the mental model of nested maps for "column order matters". For example, an index "published, low_quality_probability, lang" is just a Map<date, Map<number, Map<lang, rowId>>> in my mental model. These maps are ordered by the order the index possesses. That explains why column order matters and why one cannot skip columns and why it stops at range queries. Just imagine getting a final rowId from these nested maps and you'll see why the index works for some queries and doesn't for others.

adzm 1y ago
I use this same approach to explain indexes to database novices; it usually helps a lot, especially with how the leaf nodes / included indexes work as well (using that info instead of or in addition to rowid/primary key as the last value there)
emschwartz 1y ago
That is a helpful way of thinking about it. Thanks for sharing!
immibis 1y ago
It's actually a List<Tuple<date, number, rowid>>> in sorted order, and queries are more akin to binary search (they are not actually binary but use a wider fanout depending on many factors)
- Sesse__ 1y ago
  It's _not_ just a list in sorted order; if it were, you could not insert efficiently into it. But the tuple is the right mental model, indeed.
- nikeee 1y ago
  Yeah it might be closer to what's actually happening but it doesn't make it obvious why something doesn't work. The map model doesn't even cover non-unique indices. Or different type of indexes.
masklinn 1y ago
It’s way easier if you think of the indexes as tuple keys in a binary tree. Because they’re tuple keys in a b-tree. That also explains how ranges work efficiently.
- jasonwatkinspdx 1y ago
  Yeah, if you're going to work with databases regularly I think it's worth learning how b-trees work. It'll make a lot of things much more intuitive. If you wanna get a very complete grounding in how the big rdbmses work, Andy Pavlo's lectures and class notes are fantastic.
  - runlaszlorun 1y ago
    I definitely second Andy Pavlo's lectures.
deleted 1y ago
[deleted]

ComputerGuru 1y ago

Not a great article; I clicked expecting something super technical about SQLite internals and found a mix of rdbms basics and some misconceptions. The limitations in the blog post aren't really specific to SQLite (for the most part), they're just how indexes (indices) and database engines work across the board. And some of the things phrased as "SQLite [can't] do this" is stuff that wouldn't make sense to do in the first place. If you understand what (multi-column) indexes are at the lowest level (i.e. what data structure they represent, how they are used by the database, what the code reading them would look like) then all of this makes immediate and natural sense. Indexes aren't magic. They're just a shortcut to help the the db engine find your data more effectively. This doesn't require super complex understanding of data structures, btw. The same limitations and advice would apply if you were trying to, for example, search a stack of resumes sorted by one or more attributes. If you have them sorted by position, years of experience, and applicant's last name; you wouldn't be able to quickly grab all resumes for the HR Manager position that have a last name starting with J - after all, you sorted them by years of experience first! It's physically not possible! You can only use the first part of that index, the position, to jump to HR Manager resumes; then you would need to manually go through them one-by-one to grab only the ones starting with J for each years-of-experience subgroup (if any).

some_old_guy 1y ago
The fact that it stops at the first range isn't intuitive to me at all, and I've been using sqlite for 20 years now. Given a covering index on (thing_date, thing_color) I would think a scan would not be needed for the query: select thing_date, thing_color where thing_date < date and thing_color = 'blue' I also can't think of a reason this is the case given the underlying data structures.
- ComputerGuru 1y ago
  It's a flattened tree, and you've used the index to reach a point where you have multiple child nodes that meet the precondition thing_date < 17. Some, but not all, of these have thing_color = blue, but the important part is that they're non-consecutive (the index can only give you one point-of-entry, so the data has to be consecutive or you need to do some amount of scan). I asked an llm to give me an ascii representation so it'll be easier to see what I mean; consider the case where you want thing_date < 17 and thing_color = blue, the results that match are marked with an asterisk, but note that there's no way to get to them (ALL of them) directly: Root (internal) +---------------------+ | keys: 14 17 | +----+--------+--------+ | | | v v v +----------------+ +----------------+ +----------------+ | Leaf P0 | | Leaf P1 | | Leaf P2 | | (dates 10-13) | | (dates 14-16) | | (dates 17-20) | |----------------| |----------------| |----------------| | 10 | red |101 | | 14 | blue |141| | 17 | green |171| | 11 | blue |111 |*| 14 | red |142| | 18 | blue |181| | 12 | green |121 | | 15 | yellow|151| | 19 | red |191| | 13 | red |131 | | 16 | blue |161|*| 20 | green |201| +----------------+ +----------------+ +----------------+ Go back to my resume's example: you have the resumes sorted by application timestamp (chronologically) and position (alphabetically). You want to find all resumes received last Tuesday where position was "Senior Architect". You literally can't skip to the point where the next n consecutive results will be the results you seek, because you can't temporarily re-order them and within the subset of resumes where date > monday and date < wednesday, there may be multiple for "Senior Architect" but they didn't arrive one-after-the-other so they're interspersed with results you don't want. The correct to do here would be (if this is always the shape of the query you expect to get) to sort the resumes by (position, timestamp) instead, at which point you absolutely CAN jump to the position that guarantees the next results are (all the results) of the query ("Senior Architect", last Tuesday). One important thing to keep in mind is that a SCAN is not the end of the world. You know your data and you know when it's ok (you expect most of the results to match and a few to be filtered out, in which case you're really not saving much time anyway) and when it's not (the scan will cover a huge range that contains only a few, widely interspersed results and you have no way of terminating early). EDIT: In response to your exact question however, note that with a covering index you might not end up with a SCAN (i.e. you don't hit the table) even though it uses only the partial index (thing_date) and not (thing_date, thing_color)! It's still possible for it to avoid hitting the backing table and might return the results directly from the index - something it wouldn't have been able to do if the index was only (thing_date).

Ameo 1y ago

The main takeaway from this for me is that SQLite’s query planner seems to be pretty limited. It’s reliant on stuff like the order in which WHERE conditions are specified, isn’t able to use multiple indexes in queries in many cases, bails out to scans when a variety of different operations show up in queries, etc. It might be the case that SQLite has a simpler or less sophisticated query planner than other databases like Postgres or MariaDB, but in my experience those DBs struggle a lot with good querying planning as well. I’ve spent many hours in the past with issues like Postgres suddenly starting to ignore an index entirely because its computed table data distribution statistics got out of balance, or having to add manual annotations to MariaDB queries like STRAIGHT_JOIN in order to get a query to run faster. I’m guessing that this is a really hard problem since it doesn’t seem to be really “solved” by any major DB vendor I’ve seen. A lot of modern DB engines like Clickhouse tend to just work around this problem by being so fast at full table scans that they don’t even need any sophisticated indexing set up at all.

emschwartz 1y ago
Just to clarify one thing: the order of WHERE conditions in a query does not matter. The order of columns in an index does.
- Ameo 1y ago
  It definitely does matter, especially in cases where the conditions are on non-indexed columns or there are CPU-intensive search operations like regex, string ops, etc. I just ran this test locally with a table I created that has 50 million rows: ``` » time sqlite3 test.db "select count() from test WHERE a != 'a' AND a != 'b' AND a != 'c' AND a != 'd' AND b != 'c' AND d != 'd' AND e != 'f' AND f = 'g'" sqlite3 test.db 5.50s user 0.72s system 99% cpu 6.225 total » time sqlite3 test.db "select count() from test WHERE f = 'g' AND a != 'a' AND a != 'b' AND a != 'c' AND a != 'd' AND b != 'c' AND d != 'd' AND e != 'f'" sqlite3 test.db 1.51s user 0.72s system 99% cpu 2.231 total ``` The only difference is swapping the `f = 'g'` condition from last to first. That condition never matches in this query, so it's able to fail fast and skip all of the work of checking the other conditions.
SkiFire13 1y ago

kccqzy 1y ago

> It's worth being careful to only add indexes that will be used by real queries. This reminds me of a technique used by Google App Engine SDK a long time ago before it was called cloud. Basically in development mode, the SDK captures the kind of queries you make, and then automatically add any index that would speed up this query into a configuration file. You then later deploy with this configuration file, which tells the production data store to create such indexes. I thought this was a genius idea. Not sure if something similar exists for SQLite.

deleted 1y ago
[deleted]
Waterluvian 1y ago
This seems like the kind of tool that would be useful for all indexable databases. I imagine you can’t definitively know and therefore just make this automagical. But I bet the result is a pretty solid shortlist for consideration.
emschwartz 1y ago
The SQLite CLI has a `.expert` command that will give index recommendations when you run queries: https://sqlite.org/cli.html#index_recommendations_sqlite_expert_ https://sqlite.org/cli.html#index_recommendations_sqlite_exp... It's not quite the same as capturing all of the queries used in development (or production), but it seems somewhat useful. I'll also note that I had an LLM generate quite a useful script to identify unused indexes (it scanned the code base for SQL queries, ran `EXPLAIN QUERY PLAN` on each one to identify which indexes were being used, and cross-referenced that against the indexes in the database to find unused ones). It would probably be possible to do something similar (but definitely imperfect) where you find all of the queries, get the query plans, and use an LLM to make suggestions about what indexes would speed up those queries.
- zeroq 1y ago
  wow! I used to bruteforce a bunch of indexes until EXPLAIN on queries gave satisfactory results! I actually looked for a tool where I could provide a schema and all queries to get optimal indexes but never found one that actually worked.

crazygringo 1y ago

I agree with other commenters that there's nothing new or surprising here [1] if you actually understand how indexes work. Which, if you're working in SQL, you should. But the main takeaway, I disagree with. The author explains: > When I first set up Scour's database, I put a bunch of indexes on the items table without really thinking about whether they would help. For example, I had separate indexes on the published date, the language, and the quality rating. Useless. It's more important to have one or a small handful of good composite indexes on multiple columns than to have separate indexes on each column. Yes, the biggest error is throwing indexes at a table without having the slightest idea if they're helpful. But the idea that a smaller number of composite indexes is not the answer either. The answer is to go through every single query and make sure you have an index that matches the query, adding/changing indexes (including composite indexes) or rewriting queries as required. Indexes don't exist in a vacuum. They are optimizations for specific queries. Proper database design requires knowing exactly what information you will need to look up using which parameters, and designing the tables and indexes to produce that performantly. When you need to look up data in new ways, you often need to add new indexes or rearchitect tables entirely. [1] Except that the partial index match condition treats 0.9 and .9 as different. That is unexpected to me, but it is kind of in the docs at "The terms in W and X must match exactly": https://www.sqlite.org/partialindex.html https://www.sqlite.org/partialindex.html

DaiPlusPlus 1y ago
> Yes, the biggest error is throwing indexes at a table without having the slightest idea if they're helpful Unless you have a small number of static queries, this task isn’t really possible without an observability-solution (reporting the raw query text, the actual execution plan, and runtime and IO stats, et cetera) - otherwise it’s guesswork. …and worse-still if your application has runtime-defined queries, such as having a custom filter builder in your UI. Actually I’ll admit I have no idea how platforms like Jira are able to do that with decent performance (can anyone let me know?) I heap praise on MSSQL’s Query Store feature - which is still a relatively recent addition. I have no idea how anyone could manage query performance without needing 10x the time and effort needed to attach a profiler to a prod DB. …and if these are “edge” databases running on a 100k IoT devices then have fun, I guess.

wodenokoto 1y ago

The article is a developers journey into indexes and not a bad journey or travelogue imho. Sure, if you are a database expert, it might be disappointing but I enjoyed reading it.

emschwartz 1y ago
Thanks for saying that! That’s exactly how it was intended and I’m glad to hear you enjoyed it
- andersmurphy 1y ago
  Fun read. I've been tripped up by the where in partial indexes before. Same goes for expression indexes.
  - emschwartz 1y ago
    Thank you for saying that too! Hope this explanation helped explain why, at least a little bit.

themafia 1y ago

You may not need a course. SQLite publishes a very useful guide on the query planner which goes over most of what you need to know with respect to indexes: https://www.sqlite.org/optoverview.html https://www.sqlite.org/optoverview.html

garaetjjte 1y ago

One thing I'm missing in SQLite are multi-valued indexes. I would want to have an index on func_returning_list(some_column), and query doing WHERE x IN func_returning_list(some_column) should use that index.

rtpg 1y ago

Postgres is pretty good about using index data from multiple single column indices... if you don't order results! Otherwise the planner will get in the way and do nonsense. A bunch of people spend years studying computer science and trees etc, and then when we actually need to care about that stuff the databases absolutely do not want us to declare the plan. Very annoying. Think about how people would be guided to do the right thing on indices if they had to say "go along this index to get my data" in a "planned mode" when dealing with bad performance? So many data layout and index issues would become immediately obvious. You wouldn't magically get as many benefits from DB updates, granted.