Thread: Cannot get query to use btree-gin index when ORDER BY

Cannot get query to use btree-gin index when ORDER BY

From
Andreas Joseph Krogh
Date:
Hi all.
 
Using PG-9.4
 
I have a query which goes something like this
 
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,
fts_all tsvector
);
 
create index message_fts_all_folder_idx ON message using gin (fts_all, folder_id);
 
SELECT m.id, m.subject
  FROM message m
WHERE m.folder_id = 1 AND m.fts_all @@ to_tsquery('simple', 'foo')
ORDER BY received DESC LIMIT 10;
 
...
 
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.
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
Attachment