How to speed up SELECT from not-unique column?

Question

I have a simple query in a Postgres 15 database. No joins or other fancy stuff:

select * from my_table where col='val'

The goal is to speed up the query. I'd say I need an index, but the values of the col are not unique. Is there any way to specify something like non-unique index, or any other way to speed it up using table structure only?

I assume all mem/cache/hardware tuning was done already.

Answer 1

To optimize:

Specifics

The column col is not UNIQUE. But as long as cardinality in the column isn't extremely low (meaning your filter is selective enough), a basic B-tree index helps (a lot):

CREATE INDEX my_table_col_idx ON my_table (col);

If performance of that SELECT is your paramount objective, CLUSTER the table using said index. See:

• Impact of CLUSTER on performance
• Alternative to CLUSTER without table lock

Do you really need to select the whole row (SELECT *), or would a selection of columns suffice?

Basics

• Storage: faster is obviously better.

• RAM: more is obviously better - until you have enough so that the whole table can reside in cache all the time. Then storage loses its relevance for your SELECT query.

• CPU: faster is obviously better - but mostly only when your system is near capacity, as CPU is typically not the bottleneck for a simple SELECT query.

• Concurrent load: less on the same table / database / server is obviously better. (Writers don't block readers in Postgres. Still, write activity can impede read performance in various ways.)

• Server configuration: RAM has to be allocated properly, cost settings and column statistics must about valid.

• Table definition: choose the right data types in optimized sequence. See:

  • Calculating and saving space in PostgreSQL