I have created the following multi column index
CREATE INDEX ON message_log (website, in_use, insert_timestamp);
However when I perform the following query, it does not seem to be using the query as it's taking a very long time to complete.
select id from message_log where website = 'forgezilla' AND in_use = false ORDER BY insert_timestamp NULLS FIRST;
Can the multi column index be used for order by? if so did I create the ordering wrong when creating the index?
Best Answer
No, your given index cannot be used for the ORDER BY. You can see this by doing
set enable_sort=off
and then explaining the query, you get a sort anyway because that is the only way PostgreSQL can get the job done.But if you create the correct index:
then it will be used for the ORDER BY.