Hello,
I'm having a bit of an issue with full text search (using tsvectors) on
PostgreSQL 8.4. I have a rather large table (around 12M rows) and want
to use full text search in it (just for one of the columns). Just doing
a plainto_tsquery works reasonably fast (I have a GIN index on the
colu
Hello, thanks for your replies.
On 7/20/2009 13:12, Oleg Bartunov wrote:
Hmm, everything is already written in explain :) In the first query
253635 rows should be readed from disk and sorted, while in the
second query only 24 (random) rows readed from disk, so there is 4
magnitudes
difference
Hello,
On 7/20/2009 22:42, Kevin Grittner wrote:
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
On 7/21/2009 2:13, Devin Ben-Hur wrote:
Have you tried make the full-text condition in a subselect with
"offset 0" to stop the plan reordering?
eg:
select *
from (
select * from a where comment_tsv @@ plainto_tsquery('love')
offset 0
) xx
order by timestamp DESC
limit 24
offset 0;
See ht
On 7/21/2009 11:32, valgog wrote:
Hi,
There is a problem with GIN and GIST indexes, that they cannot be used
by the ORDER BY. Maybe it will be a nice idea to ask Oleg to make it
possible to use the b-tree columns in GIST or GIN to make the sort
easier, but I have no idea how difficult it will be
