Search Postgresql Archives

Re: GIN: any ordering guarantees for the hits returned?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.

> 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 :)

Cheers,
David.
-- 
David Fetter <david@xxxxxxxxxx> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@xxxxxxxxx

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux