Skip site navigation (1) Skip section navigation (2)

Re: Full text search with ORDER BY performance issue

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: "Krade" <krade(at)krade(dot)com>,<pgsql-performance(at)postgresql(dot)org>
Subject: Re: Full text search with ORDER BY performance issue
Date: 2009-07-20 21:42:31
Message-ID: 4A649E7702000025000289E3@gw.wicourts.gov (view raw or flat)
Thread:
Lists: pgsql-performance
Krade <krade(at)krade(dot)com> wrote:
> SELECT * FROM a WHERE comment_tsv @@ plainto_tsquery('love')
> ORDER BY timestamp DESC LIMIT 24 OFFSET 0;
 
Have you considered keeping rows "narrow" until you've identified your
24 rows?  Something like:
 
SELECT * FROM a 
  WHERE id in
    (
      SELECT id FROM a
        WHERE comment_tsv @@ plainto_tsquery('love')
        ORDER BY timestamp DESC
        LIMIT 24 OFFSET 0
    )
  ORDER BY timestamp DESC
;
 
-Kevin

In response to

Responses

pgsql-performance by date

Next:From: Raji Sridar (raji)Date: 2009-07-20 22:33:04
Subject: Help needed for reading postgres log : RE: Concurrency issue under very heay loads
Previous:From: Merlin MoncureDate: 2009-07-20 21:24:57
Subject: Re: Fastest char datatype

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group