4

I have a (large) table with a composite primary key, composed of 5 columns (a, b, c, d, e).

I'd like to efficiently select all rows having two of those columns (a + e) to a given value.

In PostgreSQL, do I need an index for this? Or will the database use the primary key (even partly?)

I've seen the following post, which specify that MySQL can use the left-most part(s) of a multiple-column index for efficiently querying rows. But I did not find anything for PostgreSQL on composite primary keys.

Laurent Grégoire
  • 4,006
  • 29
  • 52
  • 2
    Well, `(a,e)` is not the "left-most" part of the primary key. So Postgres won't be able to use an index on `(a,b,c,d,e)` efficiently. Details are [in the manual](https://www.postgresql.org/docs/current/indexes-multicolumn.html). I highly doubt that MySQL will use an index on `(a,b,c,d,e)` if the where clause specifies `(a,e)` - if it does, it probably only uses `a` (which Postgres can do just as well) –  Jan 10 '22 at 10:02
  • Well my remark on MySQL is not clear: it can indeed use the left-most part of the index, not "any" left-most part. For example it can use (a), (a,b), (a,b,c) and (a,b,c,d) if there is composite index on (a,b,c,d,e); bot none other. I'll remove "any" in my comment. – Laurent Grégoire Jan 10 '22 at 10:18
  • 1
    Postgres can use the same left-most parts. In addition it _can_ use the index for a condition on `(a,e)` (or even `c,d`) - but not as efficiently. Check the [execution plan](https://www.postgresql.org/docs/current/using-explain.html) and you'll see –  Jan 10 '22 at 10:28

1 Answers1

4

For best performance, you would need an additional index on (a, e).

If that is an option, you can drop the primary key and replace it with one on (a, e, b, c, d). The primary key index would then support your query, and it is just as good as far as uniqueness is concerned.

You can also experiment with a single-column index on (e). PostgreSQL can combine scans on several indexes. That won't be as fast as a multi-column index, but the resulting index would be smaller.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263