Searching GIN-index (FTS) and sort by timestamp-column - Mailing list pgsql-performance

create extension if not exists btree_gin;

drop table if EXISTS delivery;
create table delivery(    id BIGSERIAL primary key,    fts_all TSVECTOR not null,    folder_id BIGINT NOT NULL,    received_timestamp TIMESTAMP not null,    message varchar not null
);
create index fts_idx ON delivery using gin(fts_all, folder_id);

CREATE OR REPLACE FUNCTION update_delivery_tsvector_tf() RETURNS TRIGGER AS $$
BEGIN
    NEW.fts_all = to_tsvector('simple', NEW.message);
    return NEW;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER update_delivery_tsvector_t BEFORE INSERT OR UPDATE ON delivery
FOR EACH ROW EXECUTE PROCEDURE update_delivery_tsvector_tf();

insert into delivery(folder_id, received_timestamp, message)
values (1, '2015-01-01', 'Yes hit four')    , (1, '2014-01-01', 'Hi man')    , (2, '2013-01-01', 'Hi man')    , (2, '2013-01-01', 'fish')
;

analyze delivery;

set ENABLE_SEQSCAN to off;

explain analyze SELECT del.id    , del.received_timestamp
FROM delivery del
WHERE 1 = 1
      AND del.fts_all @@ to_tsquery('simple', 'hi:*')      AND del.folder_id = 1
ORDER BY  del.received_timestamp DESC LIMIT 101 OFFSET 0;