0

I have a scenario where a row in a table can be obtained by using either primary key or another column which is indexed:

SELECT * FROM table_name WHERE primary_key = ?

vs.

SELECT * FROM table_name WHERE another_column = ?

another_column has unique values and has index created on it.

I am using Postgres, and on doing explain analyze on both SELECTs, they resulted in almost same execution time.

So, my question is, generally, is the performance for both the queries same or is one query better than the other? And why?

2 Answers 2

0

In PostgreSQL there is no difference between a primary key index and other indexes, so that does not surprise me.

The only slight difference could be caused by the size of the indexed data (wider values cause deeper indexes, so more blocks have to be traversed) or the speed of the comparison function. In most real world cases, the difference will be hard to measure.

0

I would say that a UNIQUE index is comparable to a PK index. The difference between the two depends on how the dbms handles NULL values. PostgreSQL allows multiple NULL values in a unique index (https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-UNIQUE-CONSTRAINTS) while other dbms do not. In most dbms, a PK constraint is implemented with a NOT NULL constraint and UNIQUE index combination.

Non-unique indexes may exhibit different performance. For example, the cardinality of the data would be a major factor. Consider an index on a Boolean column. Assuming randomly distributed data, 50% of the rows would have the same value. An index on that column would be used differently by the query planner than a unique index. Function based indexes is another area where I would expect different performance.

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.