pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedORDER BY using index, tsearch2
6+ messages / 3 participants
[nested] [flat]
* ORDER BY using index, tsearch2
@ 2013-12-11 22:29 Janek Sendrowski <janek12@web.de>
0 siblings, 2 replies; 6+ messages in thread
From: Janek Sendrowski @ 2013-12-11 22:29 UTC (permalink / raw)
To: pgsql-performance
<html><head></head><body><div style="font-family: Verdana;font-size: 12.0px;"><div>Hi,</div>
<div> </div>
<div>How can I use this ORDER BY using index feature 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>It still 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>Thanks for your help!</div>
<div>Janek Sendrowski</div></div></body></html>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ORDER BY using index, tsearch2
@ 2013-12-11 23:38 Craig James <cjames@emolecules.com>
parent: Janek Sendrowski <janek12@web.de>
1 sibling, 1 reply; 6+ messages in thread
From: Craig James @ 2013-12-11 23:38 UTC (permalink / raw)
To: Janek Sendrowski <janek12@web.de>; +Cc: pgsql-performance
On Wed, Dec 11, 2013 at 2:29 PM, Janek Sendrowski <janek12@web.de> wrote:
> 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?
>
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.
Craig
> Thanks for your help!
> Janek Sendrowski
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ORDER BY using index, tsearch2
@ 2013-12-11 23:55 Tom Lane <tgl@sss.pgh.pa.us>
parent: Craig James <cjames@emolecules.com>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2013-12-11 23:55 UTC (permalink / raw)
To: Craig James <cjames@emolecules.com>; +Cc: Janek Sendrowski <janek12@web.de>; pgsql-performance
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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ORDER BY using index, tsearch2
@ 2013-12-12 01:00 Janek Sendrowski <janek12@web.de>
parent: Janek Sendrowski <janek12@web.de>
1 sibling, 1 reply; 6+ messages in thread
From: Janek Sendrowski @ 2013-12-12 01:00 UTC (permalink / raw)
To: pgsql-performance
Sorry, I still wanted to add following link: http://www.sai.msu.su/~megera/postgres/talks/Full-text%20search%20in%20PostgreSQL%20in%20millisecond...
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, that this name is obsolete.
Janek Sendrowski
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ORDER BY using index, tsearch2
@ 2013-12-12 03:57 Tom Lane <tgl@sss.pgh.pa.us>
parent: Janek Sendrowski <janek12@web.de>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2013-12-12 03:57 UTC (permalink / raw)
To: Janek Sendrowski <janek12@web.de>; +Cc: pgsql-performance
"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%20millisecond...
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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ORDER BY using index, tsearch2
@ 2013-12-12 13:16 Janek Sendrowski <janek12@web.de>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Janek Sendrowski @ 2013-12-12 13:16 UTC (permalink / raw)
To: pgsql-performance
Okay thanks.
That's what I wanted to know.
Janek Sendrowski
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2013-12-12 13:16 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-12-11 22:29 ORDER BY using index, tsearch2 Janek Sendrowski <janek12@web.de>
2013-12-11 23:38 ` Craig James <cjames@emolecules.com>
2013-12-11 23:55 ` Tom Lane <tgl@sss.pgh.pa.us>
2013-12-12 01:00 ` Janek Sendrowski <janek12@web.de>
2013-12-12 03:57 ` Tom Lane <tgl@sss.pgh.pa.us>
2013-12-12 13:16 ` Janek Sendrowski <janek12@web.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox