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.
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
- a plain (or, redundantly,
UNIQUE) index on
Postgres 11 (currently beta) introduces true covering indexes with the added
INCLUDE clause, which conveniently allows to piggyback the non-key column
something on the
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
primary_key happens to be a much wider column than
other_key you mentioned (
int like in the example would not qualify), you can also piggyback
something on an index on that column:
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).
Related blog entry with details from Michael Paquier: