Mastering PostgreSQL 12 - Third Edition

Using more than one index at a time

Up until now, you have seen that one index at a time has been used. However, in many real-world situations, this is nowhere near sufficient. There are cases demanding more logic in the database.

PostgreSQL allows the use of multiple indexes in a single query. Of course, this makes sense if many columns are queried at the same time. However, that's not always the case. It can also happen that a single index is used multiple times to process the very same column.

Here is an example:

test=# explain SELECT * FROM t_test WHERE id = 30 OR id = 50;                           QUERY PLAN -----------------------------------------------------------  Bitmap Heap Scan on t_test (cost=8.88..16.85 rows=2 width=9)  Recheck Cond: ((id = 30) OR ...