4 ms·

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 de

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...

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...