Thread: ORDER BY using index, tsearch2

<div style="font-family: Verdana;font-size: 12.0px;"><div>Hi,</div><div> </div><div>How can I use this ORDER BY using
indexfeature presented in this implementation.</div><div>It doesn't seem to be in use, when I have a look in my query
plan.</div><div>Itstill does an cost intensive Bitmap Heap Scan and a Bitmap Index scan.</div><div>I also can't find
the"><" operator in any introduction of the tsearch2 extension.</div><div>Is it just an
idea?</div><div> </div><div>Thanksfor your help!</div><div>Janek Sendrowski</div></div> 

[Sorry, this previous mail was HTML-foramted]

Hi,
 
How can I use this ORDER BY using index feature presented in this implementation.
It doesn't seem to be in use, when I have a look in my query plan.
It still does an cost intensive Bitmap Heap Scan and a Bitmap Index scan.
I also can't find the "><" operator in any introduction of the tsearch2 extension.
Is it just an idea?
 
Thanks for your help!
Janek Sendrowski

"Janek Sendrowski" <janek12@web.de> writes:
> How can I use this ORDER BY using index feature presented in this implementation.
> It doesn't seem to be in use, when I have a look in my query plan.
> It still does an cost intensive Bitmap Heap Scan and a Bitmap Index scan.
> I also can't find the "><" operator in any introduction of the tsearch2 extension.
> Is it just an idea?

We're not in the habit of documenting nonexistent features, if that's what
you mean.  However, you've not provided nearly enough information for
anyone to help you; at minimum, the index definitions you have, the query
you gave, the plan you got, and the exact PG version would be critical
information.  More information about asking answerable questions can be
found here:
https://wiki.postgresql.org/wiki/Slow_Query_Questions

Also, I'm a bit troubled by your reference to tsearch2, because that
contrib module is obsolete, and has been since well before any PG version
that has a feature like what I think you're asking about.  So I wonder if
you are reading documentation not applicable to the version you're working
with.

            regards, tom lane

Craig James <cjames@emolecules.com> writes:
> A GIST is a tree, but there's no notion of ">" or "<", only yes/no at each
> tree branch.  In this regard a GIST index is more like a hash table.  You
> can't use a hash table to sort.  It doesn't make sense.

Recent versions of PG do allow GIST indexes to be used to satisfy
K-nearest-neighbor queries, if the operator class supports that.
(This requires that the tree partitioning be done on some notion of
distance, and even then there'll be some traversal of irrelevant index
entries; but it way beats a full-table scan, or even full-index scan.)

But I'm not entirely sure if that's what the OP is asking about.

            regards, tom lane

Sorry, I still wanted to add following link:
http://www.sai.msu.su/~megera/postgres/talks/Full-text%20search%20in%20PostgreSQL%20in%20milliseconds-extended-version.pdf
On page 6 you can see the first example:

"postgres=# explain analyze
SELECT docid, ts_rank(text_vector, to_tsquery('english', 'title')) AS rank
FROM ti2
WHERE text_vector @@ to_tsquery('english', 'title')
ORDER BY text_vector>< plainto_tsquery('english','title')
LIMIT 3;"

"Limit (cost=20.00..21.65 rows=3 width=282) (actual time=18.376..18.427 rows=3 loops=-> Index Scan using ti2_index on
ti2(cost=20.00..26256.30 rows=47692 width=282) 
(actual time=18.375..18.425 rows=3 loops=1)
Index Cond: (text_vector @@ '''titl'''::tsquery)
Order By: (text_vector >< '''titl'''::tsquery)"

My PG-version is 9.3.
I was wondering about this feature, bacause I haven't seen it yet and it a huge speed up.

Sorry, I thought the name is still tsearch, because the functionnames are roughly the same, but now know I noticed,
thatthis name is obsolete. 

Janek Sendrowski

"Janek Sendrowski" <janek12@web.de> writes:
> Sorry, I still wanted to add following link:
http://www.sai.msu.su/~megera/postgres/talks/Full-text%20search%20in%20PostgreSQL%20in%20milliseconds-extended-version.pdf

Oh ... well, that's not Postgres documentation; that's Oleg and Alexander
giving a paper about some research work that they're doing.  Which is
still unfinished as far as I know; it certainly hasn't been committed
to community source code.  (I'm not sure if the GIN improvements being
worked on in the current release cycle are the same thing described in
this paper, but in any case they're not committed yet.)

            regards, tom lane

Okay thanks.
That's what I wanted to know.

Janek Sendrowski