[GENERAL] GIN: any ordering guarantees for the hits returned?

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)

Do you need help?

In addition to our free technical library, Pantek provides professional IT services to companies who utilize Open Source software: Learn more about Pantek's Technical Support offerings Place an order for 24/7/365 Technical Support here Learn more about Pantek, who we are, and what we do Chat with a Live Operator Related postgresql.org Links pgsql-admin pgsql-advocacy pgsql-announce pgsql-bugs pgsql-cygwin pgsql-docs pgsql-general pgsql-interfaces pgsql-jdbc pgsql-jobs pgsql-novice pgsql-odbc pgsql-performance pgsql-php pgsql-ports pgsql-sql Request more information about Pantek

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.


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               
http://archives.postgresql.org/