This is the second article in the series of explaining indexing in PostgreSQL. If you missed the first article you can visit this link.

Making use of indexes in Postgres

One step forwards towards becoming a database ninja. Learn how to use indexes in postgresql

medium.com

---

Cardinality

Cardinality is an indicator that refers to the uniqueness of all values in a column.

Low cardinality means a lot of duplicated values in the column. For example, a column that stores gender values have low cardinality. In contrast, high cardinality means that there are many unique values in that column. For example the primary key column.

CARDINALITY

When we make a query on the low cardinality column it is likely that we will receive a large portion of the table. It will be more efficient to use a sequential scan rather than an index scan because we will be fetching a lot of columns anyway.

Until now in our database, we have stored only two values in the name field Alice and bob. Now we want to make a query on the name field but before that, we will need to index the name column.

test_indexing=## create index idx_name on test_indexing(name);
CREATE INDEX

Now we run explain analyze command on the select query to check what strategy Postgres is using behind the scenes.

test_indexing=## explain analyze select * from test_indexing where name = 'alice';

Seq Scan on test_indexing  (cost=0.00..871.00 rows=25202 width=9) (actual time=6.398..15.959 rows=25000 loops=1)
   Filter: (name = 'alice'::text)
   Rows Removed by Filter: 25000
 Planning time: 4.347 ms
 Execution time: 18.752 ms
(5 rows)

As we can see here that Postgres is using a sequential scan here although the column is indexed. Here Postgres analyzed that it has to read most of the table anyway so it is better to just scan the table instead of scanning both the index and the table. This brings up the important question: How to choose what columns should be indexed?

Postgres will not use index because there is one. It will only use indexes when it makes sense.

#database #indexing #postgresql #postgres #backend