Does the index column order matter on row insert in Postgresql?

user_16631966 0
Viewed 204

I have a table similar to this one:

create table request_journal
(
    id                  bigint,
    request_body        text,
    request_date        timestamp,
    user_id             bigint,
);

It is used for request logging purposes, so frequent inserts in it are expected (2k+ rps). I want to create composite index on columns request_date and user_id to speed up execution of select queries like this:

select * 
from request_journal 
where request_date between '2021-07-08 10:00:00' and '2021-07-08 16:00:00'
  and user_id = 123
order by request_date desc;

I tested select queries with (request_date desc, user_id) btree index and (user_id, request_date desc) btree index. With request_date leading column index select queries are executed about 10% faster, but in general performance of any of this indexes is acceptable.

So my question is does the index column order affect insertion time? I have not spotted any differences using EXPLAIN/EXPLAIN ANALYZE on insert query. Which index will be more build time efficient under "high load"?

postgresql-11 postgresql
1 Answers

It is hard to believe your test were done on any vaguely realistic data size.

At the rate you indicate, a 6 hour range would include over 43 million records. If the user_ids are spread evenly over 1e6 different values, I get the index leading with user_id to be a thousand times faster for that query than the one leading with request_date.

But anyway, for loading new data, assuming the new data is all from recent times, then the one with request_date should be faster as the part of the index needing maintenance while loading will be more concentrated in part of the index, and so better cached. But this would depend on how much RAM you have, what your disk system is like, and how many distinct user_ids you are loading data for.

user_17212390
Related