Cannot get query to use btree-gin index when ORDER BY - Mailing list pgsql-performance

The simplified schema is like this:

 

CREATE TABLE message(

id serial PRIMARY KEY,

subject varchar NOT NULL,

folder_id integer NOT NULL REFERENCES folder(id),

received timestamp not null,

);

 

 

On my dataset it uses an index I have on (folder_id, received DESC), then filters the result, which is not optimal when searching in > 1million messages and the result is large and I'm only after the first (newest) 10.

 

What I'd like is to have an index like this:

 

create index message_fts_all_folder_idx ON message using gin (fts_all, folder_id, received DESC);

 

but GIN doesn't allow ASC/DESC modifiers.

 

Any hints on how to optimize this?

 

Thanks.