Does a query with a primary key and foreign keys run faster than a query with just primary keys?
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):
- the PK index on
(primary_key)
, obviously. - 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
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.
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.