0

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.

1
  • 1
    Indexes don't have to be unique, but it doesn't mean any of them will be used. Please consider reading this advice
    – mustaccio
    Aug 9, 2023 at 16:59

1 Answer 1

0

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:

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:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.