Is there any way to achieve sorting with inverted indexes? (I mean sorting with the index itself.)
I got a simple table:
create table messages( id int primary key, accountId int, created timestamp, message string, labels jsonb, index account_created_idx (accountId, created desc), inverted index labels_idx(labels) );
I want to order the results by “created” field.
Of course I can do this:
select * from messages@account_created_idx where accountId = 5 and labels @> '' limit 100;
This will use the account_created_idx index and it will be sorted the way I want, but it won’t use the labels_idx inverted index so the query time will grow as the message count will increase for accountId = 5.
The second option is:
select * from messages@labels_idx where labels @> '' order by created desc limit 100;
This is much more slower of course because of the sort.
The first option’s speed is acceptable but I’m curious if there is a way to achieve this sorting with inverted indexes.