« Return to Thread: GIN: any ordering guarantees for the hits returned?

GIN: any ordering guarantees for the hits returned?

by adrobj :: Rate this Message:

Reply to Author | View in Thread

Hello,

I have a moderately large (~10-20GB) table:

CREATE TABLE msgs (
  msg varchar(2048),
  msg_tsv tsvector,
  posted timestamp
);

CREATE INDEX msgs_i ON msgs USING gin(msg_tsv);

The table never gets updated (more specifically, it gets re-created once a day with no updates in between).

I want to run queries of the following form:

SELECT msg, posted FROM msgs WHERE 'blah blah'::tsquery @@ msg_tsv ORDERED BY posted DESC;
(with various LIMIT/OFFSET)

Which obviously may get too expensive, for it will cause reading and sorting of all rows meeting the condition, i.e. too many disk reads.

On the other hand, (as far as I understand) GIN always produces hits already sorted in the insertion order.

So - what if I just populate my table in the order of decreasing 'posted', remove the "ORDERED BY" clause and just hope for the best? Will the correct ordering be guaranteed?

If not, are there any other ideas around?

Thanks,
- adrobj

 « Return to Thread: GIN: any ordering guarantees for the hits returned?