Does a query with a primary key and foreign keys run faster than a query with just primary keys?

Question

SELECT something FROM table WHERE primary_key = ?

vs.

SELECT something FROM table WHERE primary_key = ? AND other_key = ?

Say that this is a scenario where the inclusion of other_key does NOT change the resultset. Is the second query faster in practice? Or do databases just use a single best key if several are provided?

Answer 1

Query

SELECT something FROM table WHERE primary_key = ?

This the fastest possible form. Adding any other predicate can only make it slower. Theoretically.

Exotic exceptions apply, like when the PK index is bloated for some reason, or the PK column is relatively big, or a multi-column PK, resulting in a much larger index, while the index for the added predicate on other_key is smaller. Then Postgres may decide to use the index for the added predicate, access the heap and filter on primary_key = ?. Unlikely, but possible.

If the added predicate evaluates to anything but TRUE, you get no row - a different result, so not a fair comparison - but that's not your case as you asserted.

A FOREIGN KEY constraint has no direct impact on read performance. The referencing column does not even have to be indexed (as opposed to the referenced column).

Covering index for top read performance

With tables of non-trivial size and not too much write activity, consider adding a multicolumn index on (primary_key, something) to allow index-only scans. In Postgres 10 or older that results in at least two indexes (imposing additional write / maintenance / space costs):

1. the PK index on (primary_key), obviously.
2. a plain (or, redundantly, UNIQUE) index on (primary_key, something).

Postgres 11 added true covering indexes using the INCLUDE clause, which conveniently allows to piggyback the non-key column something on the PRIMARY KEY:

CREATE TABLE tbl (
   primary_key bigint GENERATED ALWAYS AS IDENTITY
 , other_key   integer NOT NULL REFERENCES other_tbl
 , something   text
 , PRIMARY KEY (primary_key) INCLUDE (something)  -- here's the magic
);

If primary_key happens to be a much wider column than other_key you mentioned (bigint vs. int like in the example would not qualify), you can also piggyback something onto an index on other_key:

CREATE INDEX other_idx ON tbl(other_key) INCLUDE (something);

While either solution can optimize read performance for the given query, other queries not retrieving something then have to work with a bigger index. So weigh benefits and costs (like always when creating indexes).

• The manual on CREATE INDEX.

Related blog entry with details from Michael Paquier:

• Postgres 11 highlight - Covering Indexes

Answer 2

First, keys are a logical concept that per se has little to do with performance. However, unique constraints (primary keys) is in every DBMS:s I know of, implemented with a UNIQUE indexes so it indirectly affects performance. Some DBMS (not sure about PostgreSQL) automatically creates an index for FOREIGN KEYS. Once again the FOREIGN KEY itself has a very small impact on a SELECT statement (it will affect insert/update/delete since it has to be verified). So FOREIGN key's may indirectly affect performance of a SELECT.

In your case, the foreign key index will normally[1] not affect performance. The row is already uniquely identified by the primary key, which is the best we can hope for.

[1] normally because if the statistics for the primary key index is inconsistent with reality a table scan may be chosen. Having a second index for the foreign key can help in this situation, but this is not something that you should take into consideration.

Answer 3

No, because primary keys are unique, so a PK lookup will find a single record. So only the primary key will be used to find the record. you should see the same access plan for both queries.

using two indexes is sub-optimal in most cases. but when the best index finds several records a bitmap scan can be done using a second index.