Thread: Cannot get query to use btree-gin index when ORDER BY
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