Which column to index in multicolumn indexing?

Question

UserID	First	Middle	Last	Type	CreatedAt
123	John	Henry	Doe	Mage	03-28-2025

Let's say I have the following table above. I would like to create index to help speed up my queries.

All queries would like along the lines of this:

Select *
from users
where Type = 'SomeType'
  and First = 'SomeName1'
Order by CreatedAt DESC;

Select *
from users
where Type = 'SomeType'
  and Middle = 'SomeName2'
Order by CreatedAt DESC;

Select *
from users
where Type = 'SomeType'
  and Last = 'SomeName3'
Order by CreatedAt DESC;

How would I index the columns to make the queries efficient? Would CreatedAt be the first of the indexed column?

I'm thinking

CREATE INDEX idx_users on users(CreatedAt, Type, First, Middle, Last)

CreatedAt and type would always be used, whereas first, middle, and last varies.

Answer 1

The index you proposed might be unhelpful:

CREATE INDEX idx_users on users(CreatedAt, Type, First, Middle, Last)

The fact that you're selecting and ordering by CreatedAt doesn't mean it's needed as an index key. In my test on 400k samples, none of the three queries you showed ended up using it. They all ended up running a sequential scan that took above 150ms.

If you simply remove createdat from the index, they're happy to use it for a Bitmap Index Scan taking below 5ms. You can add more tweaks depending on how much input latency you're ready to tolerate and how much space you're willing to spend on accommodating the indexes.

Here's a comparison of storage space they take up on 200k rows, with query times in ms averaged on 45 calls, plus the index size and how much more space you need for it, in % compared to the base table size:
_{demo at db<>fiddle}

variant	query1	query2	query3	indexes size	%size
(Type,First,Middle,Last) include(createdat,userid);	0.159	0.148	0.148	35 MB	104
(Type,First,CreatedAt) INCLUDE(Middle,Last,UserID); (Type,Middle,CreatedAt) INCLUDE(First,Last,UserID); (Type,Last,CreatedAt) INCLUDE (First,Middle,UserID); charlieface	0.172	0.165	0.161	108 MB	325
(Type,First,Middle,Last);	0.197	0.187	0.182	33 MB	98
(Type,first); (Type,middle); (Type,last);	0.196	0.191	0.179	4912 kB	14
(first,type,createdat desc); (middle,type,createdat desc); (last,type,createdat desc); Thorsten Kettner	0.214	0.210	0.202	53 MB	161
(Type) include(First,Middle,Last, UserID,CreatedAt);	2.712	2.689	2.399	34 MB	103
(Type);	5.098	5.024	4.627	1456 kB	4
(CreatedAt,Type,First,Middle,Last);	77.073	76.153	76.333	32 MB	97