4 ms·

What is the rule that you use on which fields to index? I've been following the general when adding fields: if the field is mentioned in a sql WHERE, ORDER BY,

What is the rule that you use on which fields to index? I've been following the general when adding fields: if the field is mentioned in a sql WHERE, ORDER BY, or GROUP BY EDIT: JOINs

AnotherGoodName 3y ago
Don't overthink that part. Just rely on EXPLAIN. It will tell you if the query had an index to use or if it scanned. You'd be surprised when a DB does/doesn't use an index if you try to figure it out yourself. A run book for any org when writing queries should be 1) Write the query 2) Before shipping the query run the query through EXPLAIN. Did it scan or did it use an index? 3) If it scanned can you re-write it to use an existing index (the usual answer honestly). 4) Create the index.
ksherlock 3y ago
You should also consider how unique that field is. If half your widgets are blue and half your widgets are red, indexing the color is probably not helpful (ie, the query planner will ignore the widget_color index and do a full table scan). A rule of thumb number I vaguely recall is 10%
- wiredfool 3y ago
  My recollection in postgres anyway is that low cardinality indexes aren't useful, because it doesn't take into account which side of the 1/99% you're on when determining to use the index. What is useful is to do a partial index on values where foo=small % ,value because then it will use that index when it matches the query, but not when it's in the majority case.
  - anarazel 3y ago
    > My recollection in postgres anyway is that low cardinality indexes aren't useful, because it doesn't take into account which side of the 1/99% you're on when determining to use the index. It does take that into account. Demo: =# CREATE TABLE low_cardinality AS SELECT generate_series(-10, 1000000) < 0 AS col; =# CREATE INDEX ON low_cardinality (col); =# ANALYZE low_cardinality; =# EXPLAIN SELECT * FROM low_cardinality WHERE col = false; ┌─────────────────────────────────────────────────────────────────────────┐ │ QUERY PLAN │ ├─────────────────────────────────────────────────────────────────────────┤ │ Seq Scan on low_cardinality (cost=0.00..14425.11 rows=1000011 width=1) │ │ Filter: (NOT col) │ └─────────────────────────────────────────────────────────────────────────┘ (2 rows) =# EXPLAIN SELECT * FROM low_cardinality WHERE col = true; ┌────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ QUERY PLAN │ ├────────────────────────────────────────────────────────────────────────────────────────────────────┤ │ Index Only Scan using low_cardinality_col_idx on low_cardinality (cost=0.42..4.44 rows=1 width=1) │ │ Index Cond: (col = true) │ └────────────────────────────────────────────────────────────────────────────────────────────────────┘ (2 rows)
    - anarazel 3y ago
      Forgot to say: Obviously a partial index is going to be considerably better, performance and size wise.
    - wiredfool 3y ago
      Guess they've improved -- That used to be a thing. Looks like it was probably in the PG13 changes to the statistics and btree indexes that did that, though it's hard to tell exactly.
      - anarazel 3y ago
        It worked like this for much longer. The oldest version I have around is 9.2 and it behaves the same. You probably are thinking of a somewhat more complex scenario...
jdmichal 3y ago
The answer will heavily depend on what you're doing. CRUD patterns where you're pulling out a small group of rows from several tables, you probably want indexes on `JOIN` clauses so that you can quickly find those links using nested loop index scans. And maybe a few indexes on common ways to find records. Analytics patterns where you're aggregating a large number of rows, indexing `JOIN`s won't help you, because you really want hash or merge joins anyway. Instead, tune your indexes for `WHERE` clauses, because you want a filtered index scans instead of table scans on the reads. You also want to learn about cardinality and selectivity. Ideally your indexes are organized by selectivity.

What is the rule that you use on which fields to index? I've been following the general when adding fields: if the field is mentioned in a sql WHERE, ORDER BY, or GROUP BY EDIT: JOINs

AnotherGoodName 3y ago

Don't overthink that part. Just rely on EXPLAIN. It will tell you if the query had an index to use or if it scanned. You'd be surprised when a DB does/doesn't use an index if you try to figure it out yourself. A run book for any org when writing queries should be 1) Write the query 2) Before shipping the query run the query through EXPLAIN. Did it scan or did it use an index? 3) If it scanned can you re-write it to use an existing index (the usual answer honestly). 4) Create the index.

ksherlock 3y ago

You should also consider how unique that field is. If half your widgets are blue and half your widgets are red, indexing the color is probably not helpful (ie, the query planner will ignore the widget_color index and do a full table scan). A rule of thumb number I vaguely recall is 10%

wiredfool 3y ago
My recollection in postgres anyway is that low cardinality indexes aren't useful, because it doesn't take into account which side of the 1/99% you're on when determining to use the index. What is useful is to do a partial index on values where foo=small % ,value because then it will use that index when it matches the query, but not when it's in the majority case.
- anarazel 3y ago
  > My recollection in postgres anyway is that low cardinality indexes aren't useful, because it doesn't take into account which side of the 1/99% you're on when determining to use the index. It does take that into account. Demo: =# CREATE TABLE low_cardinality AS SELECT generate_series(-10, 1000000) < 0 AS col; =# CREATE INDEX ON low_cardinality (col); =# ANALYZE low_cardinality; =# EXPLAIN SELECT * FROM low_cardinality WHERE col = false; ┌─────────────────────────────────────────────────────────────────────────┐ │ QUERY PLAN │ ├─────────────────────────────────────────────────────────────────────────┤ │ Seq Scan on low_cardinality (cost=0.00..14425.11 rows=1000011 width=1) │ │ Filter: (NOT col) │ └─────────────────────────────────────────────────────────────────────────┘ (2 rows) =# EXPLAIN SELECT * FROM low_cardinality WHERE col = true; ┌────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ QUERY PLAN │ ├────────────────────────────────────────────────────────────────────────────────────────────────────┤ │ Index Only Scan using low_cardinality_col_idx on low_cardinality (cost=0.42..4.44 rows=1 width=1) │ │ Index Cond: (col = true) │ └────────────────────────────────────────────────────────────────────────────────────────────────────┘ (2 rows)
  - anarazel 3y ago
    Forgot to say: Obviously a partial index is going to be considerably better, performance and size wise.
  - wiredfool 3y ago
    Guess they've improved -- That used to be a thing. Looks like it was probably in the PG13 changes to the statistics and btree indexes that did that, though it's hard to tell exactly.
    - anarazel 3y ago
      It worked like this for much longer. The oldest version I have around is 9.2 and it behaves the same. You probably are thinking of a somewhat more complex scenario...

jdmichal 3y ago

The answer will heavily depend on what you're doing. CRUD patterns where you're pulling out a small group of rows from several tables, you probably want indexes on `JOIN` clauses so that you can quickly find those links using nested loop index scans. And maybe a few indexes on common ways to find records. Analytics patterns where you're aggregating a large number of rows, indexing `JOIN`s won't help you, because you really want hash or merge joins anyway. Instead, tune your indexes for `WHERE` clauses, because you want a filtered index scans instead of table scans on the reads. You also want to learn about cardinality and selectivity. Ideally your indexes are organized by selectivity.