David Fetter <[EMAIL PROTECTED]> wrote: On Fri, Nov 16, 2007 at 07:56:45PM -0800, adrobj wrote: > > 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?
Ordering is never guaranteed without an ORDER BY, except in the time between a CLUSTER and the first write operation after it. Which sound like my case - there are no writes to the table! Do I really need to CLUSTER - or just doing INSERTs in the right order would be sufficient? > If not, are there any other ideas around? Rather than assuming you know where problems will arise, do some profiling and find out where they actually do :) I agree with this maybe 98% - but not 100%. :-) Unfortunately performance can change rather unpredictably when the DB stops fitting in memory - say, 3-4 months after a production roll-out, too late for profiling experiments. :-( Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate --------------------------------- Never miss a thing. Make Yahoo your homepage.
