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.
To optimize:
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:
Do you really need to select the whole row (SELECT *), or would a selection of columns suffice?
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: