GIN: any ordering guarantees for the hits returned?

From: adrobj <adrobj(at)yahoo(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: GIN: any ordering guarantees for the hits returned?
Date: 2007-11-17 03:56:45
Message-ID: 13805836.post@talk.nabble.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


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
--
View this message in context: http://www.nabble.com/GIN%3A-any-ordering-guarantees-for-the-hits-returned--tf4825287.html#a13805836
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ted Byers 2007-11-17 04:09:14 Need help with complicated SQL statement
Previous Message Shane Ambler 2007-11-17 02:52:36 Re: convert access sql to postgresql