Home / Browse / OpenFTS / Mail / Archive

OpenFTS

Email Archive: openfts-general (read-only)

RE: [OpenFTS-general] GIST index and ORDER BY

From: Stas Filippov <stas@st...> - 2005-11-23 19:27

Actually my output for EXPLAIN ANALYZE is similar, but because of poor
performance it makes me wonder if the second half of the index is being
used.  Can you tell if the second half is used by looking at EXPLAIN
ANALYZE?

In general, can the second part of a two-column index be used for a
query such as:

	SELECT * FROM table WHERE col1='blabla' ORDER BY col2;

where there is an index ON table (col1,col2). In my case instead of
col1='blabla', it's vectors @@ to_tsquery('entry'), I don't know if that
makes a difference.

Not to deviate too far from the original problem -- I am trying to make
the following query perform under half a second:

SELECT textfield FROM sample WHERE vectors @@ to_tsquery('term') ORDER
BY sortfield LIMIT 10;

-Stas


-----Original Message-----
From: Oleg Bartunov [mailto:oleg@...] 
Sent: Wednesday, November 23, 2005 12:35 AM
To: Stas Filippov
Cc: openfts-general@...
Subject: Re: [OpenFTS-general] GIST index and ORDER BY

Stas,

I don't have 8.1 available for playing but 8.2dev works as expected

test=# explain analyze select tid, title from h_pages where fts @@
'printer' order by tid limit 10;
                                                          QUERY PLAN 
------------------------------------------------------------------------
----------------------------------------------------
  Limit  (cost=16.62..16.63 rows=4 width=36) (actual time=10.301..10.316
rows=5 loops=1)
    ->  Sort  (cost=16.62..16.63 rows=4 width=36) (actual
time=10.296..10.301 rows=5 loops=1)
          Sort Key: tid
          ->  Bitmap Heap Scan on h_pages  (cost=2.01..16.58 rows=4
width=36) (actual time=9.603..9.690 rows=5 loops=1)
                Filter: (fts @@ '''printer'''::tsquery)
                ->  Bitmap Index Scan on fts_idx  (cost=0.00..2.01
rows=4 width=0) (actual time=0.277..0.277 rows=5 loops=1)
                      Index Cond: (fts @@ '''printer'''::tsquery)
  Total runtime: 21.790 ms
(8 rows)

Time: 78.033 ms

test=# \d h_pages
          Table "public.h_pages"
  Column |       Type        | Modifiers 
--------+-------------------+-----------
  tid    | integer           | not null
  path   | character varying | not null
  body   | character varying |
  title  | character varying |
  fts    | tsvector          | 
Indexes:
     "h_pages_pkey" PRIMARY KEY, btree (tid)
     "h_pages_path_key" UNIQUE, btree (path)
     "fts_idx" gist (fts, tid)

btw, your explain output looks like you disable bitmapscan. Did you ?
Check postgresql.conf


 	Oleg

On Tue, 22 Nov 2005, Stas Filippov wrote:

> Hello,
>
> I am unable to use GIST index in ORDER BY clause.  I am trying to
> utilize tsearch2 for searching a textfield in a table and the sort the
> result set by another column.  E.g:
>
> CREATE TABLE sample (
>            :
>            textfield            text,
>            sortfield            int,
>            :.
>            vectors             tsvector
> );
>
> UPDATE sample SET vectors = to_tsvector( textfield );
> CREATE INDEX sample_vectors_sortfield_idx ON sample USING gist(
vectors,
> sortfield );
>
> SET enable_seqscan=off;
>
> EXPLAIN SELECT textfield FROM sample WHERE vectors @@
to_tsquery('term')
> ORDER BY sortfield LIMIT 10;
>
>                                               QUERY PLAN
>
>
------------------------------------------------------------------------
> --------------------------------
> Limit  (cost=3.69..3.70 rows=1 width=36)
>   ->  Sort  (cost=3.69..3.70 rows=1 width=36)
>         Sort Key: sortfield
>         ->  Index Scan using sample_vectors_sortfield_idx on sample
> (cost=0.00..3.68 rows=1 width=36)
>               Index Cond: (vectors @@ '''term'''::tsquery)
>               Filter: (vectors @@ '''term'''::tsquery)
> (6 rows)
>
> As you can see, only the first part of the index is used - for
searching
> the term; it then sorts the results instead of using the second part
of
> the index which is sortfield.  I tried reordering the index fields
with
> no effect
>
> It appears that GIST indexes cannot be used for ORDER BY clause in
> general.  Is that true? Why is there such limitation?
>
> I am comparing the results with MySQL fulltext search functionality
and
> it considerably outperforms Postgres in the case when sorting
described
> above is required.  Without the sorting both MySQL and Postgres are
very
> fast.
>
> Thanks for any help in advance
> -Stas
>

 	Regards,
 		Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@..., http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

Thread View

Thread	Author	Date
[OpenFTS-general] GIST index and ORDER BY	Stas Filippov <stas@st...>

Re: [OpenFTS-general] GIST index and ORDER BY

From: Josh Berkus <josh@ag...> - 2005-11-23 02:04

Stas,

=C2=A0Limit =C2=A0(cost=3D3.69..3.70 rows=3D1 width=3D36)
=C2=A0 =C2=A0-> =C2=A0Sort =C2=A0(cost=3D3.69..3.70 rows=3D1 width=3D36)

You need to run ANALYZE.  Your database thinks there's only one row in the=
=20
table.

=2D-=20
Josh Berkus
Aglio Database Solutions
San Francisco

RE: [OpenFTS-general] GIST index and ORDER BY

From: Stas Filippov <stas@st...> - 2005-11-23 02:47

Attachments: Message as HTML

Thanks for the quick reply!
Yes, this is just a test table, but I tried it on a real table of 15k
rows.  To illustrate further, I populate the sample table with 100k
rows:
 
INSERT INTO sample (sortfield) SELECT generate_series(1, 100000);
UPDATE sample SET textfield='Text Entry '||sortfield;
UPDATE sample SET vectors = to_tsvector( textfield );
VACUUM ANALYZE VERBOSE sample;
SET enable_seqscan=off;
 
EXPLAIN ANALYZE SELECT textfield FROM sample WHERE vectors @@
to_tsquery('entry') ORDER BY sortfield LIMIT 10;
 
QUERY PLAN

------------------------------------------------------------------------
----
 Limit  (cost=332.78..332.80 rows=10 width=23) (actual
time=1469.045..1469.064 rows=10 loops=1)
   ->  Sort  (cost=332.78..333.03 rows=100 width=23) (actual
time=1469.038..1469.045 rows=10 loops=1)
         Sort Key: sortfield
         ->  Bitmap Heap Scan on sample  (cost=2.35..329.46 rows=100
width=23) (actual time=244.583..612.692 rows=100000 loops=1)
               Filter: (vectors @@ '''entri'''::tsquery)
               ->  Bitmap Index Scan on sample_vectors_sortfield_idx
(cost=0.00..2.35 rows=100 width=0) (actual time=243.048..243.048
rows=100000 loops=1)
                     Index Cond: (vectors @@ '''entri'''::tsquery)
 Total runtime: 1513.562 ms
(8 rows)
 
The index on sortfield is still not used.  It takes about the same time
on 15k rows of real data where textfields are larger and more complex.
 
I am using the latest Postgres, version 8.1.0.
 
-Stas
 
 
 
 
-----Original Message-----
From: Josh Berkus [mailto:josh@...] 
Sent: Tuesday, November 22, 2005 9:05 PM
To: openfts-general@...
Cc: Stas Filippov
Subject: Re: [OpenFTS-general] GIST index and ORDER BY
 
Stas,
 
 Limit  (cost=3.69..3.70 rows=1 width=36)
   ->  Sort  (cost=3.69..3.70 rows=1 width=36)
 
You need to run ANALYZE.  Your database thinks there's only one row in
the 
table.
 
-- 
Josh Berkus
Aglio Database Solutions
San Francisco

RE: [OpenFTS-general] GIST index and ORDER BY

From: Oleg Bartunov <oleg@sa...> - 2005-11-23 05:17

Stas,

what if you disable using bitmaps ?
set enable_bitmapscan to off;

=EF=CC=C5=C7
On Tue, 22 Nov 2005, Stas Filippov wrote:

> Thanks for the quick reply!
> Yes, this is just a test table, but I tried it on a real table of 15k
> rows.  To illustrate further, I populate the sample table with 100k
> rows:
>
> INSERT INTO sample (sortfield) SELECT generate_series(1, 100000);
> UPDATE sample SET textfield=3D'Text Entry '||sortfield;
> UPDATE sample SET vectors =3D to_tsvector( textfield );
> VACUUM ANALYZE VERBOSE sample;
> SET enable_seqscan=3Doff;
>
> EXPLAIN ANALYZE SELECT textfield FROM sample WHERE vectors @@
> to_tsquery('entry') ORDER BY sortfield LIMIT 10;
>
> QUERY PLAN
>
> -----------------------------------------------------------------------=
-
> ----
> Limit  (cost=3D332.78..332.80 rows=3D10 width=3D23) (actual
> time=3D1469.045..1469.064 rows=3D10 loops=3D1)
>   ->  Sort  (cost=3D332.78..333.03 rows=3D100 width=3D23) (actual
> time=3D1469.038..1469.045 rows=3D10 loops=3D1)
>         Sort Key: sortfield
>         ->  Bitmap Heap Scan on sample  (cost=3D2.35..329.46 rows=3D100
> width=3D23) (actual time=3D244.583..612.692 rows=3D100000 loops=3D1)
>               Filter: (vectors @@ '''entri'''::tsquery)
>               ->  Bitmap Index Scan on sample_vectors_sortfield_idx
> (cost=3D0.00..2.35 rows=3D100 width=3D0) (actual time=3D243.048..243.04=
8
> rows=3D100000 loops=3D1)
>                     Index Cond: (vectors @@ '''entri'''::tsquery)
> Total runtime: 1513.562 ms
> (8 rows)
>
> The index on sortfield is still not used.  It takes about the same time
> on 15k rows of real data where textfields are larger and more complex.
>
> I am using the latest Postgres, version 8.1.0.
>
> -Stas
>
>
>
>
> -----Original Message-----
> From: Josh Berkus [mailto:josh@...]
> Sent: Tuesday, November 22, 2005 9:05 PM
> To: openfts-general@...
> Cc: Stas Filippov
> Subject: Re: [OpenFTS-general] GIST index and ORDER BY
>
> Stas,
>
> Limit  (cost=3D3.69..3.70 rows=3D1 width=3D36)
>   ->  Sort  (cost=3D3.69..3.70 rows=3D1 width=3D36)
>
> You need to run ANALYZE.  Your database thinks there's only one row in
> the
> table.
>
>

 	Regards,
 		Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@..., http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

Re: [OpenFTS-general] GIST index and ORDER BY

From: Oleg Bartunov <oleg@sa...> - 2005-11-23 05:34

Stas,

I don't have 8.1 available for playing but 8.2dev works as expected

test=# explain analyze select tid, title from h_pages where fts @@ 'printer' order by tid limit 10;
                                                          QUERY PLAN 
----------------------------------------------------------------------------------------------------------------------------
  Limit  (cost=16.62..16.63 rows=4 width=36) (actual time=10.301..10.316 rows=5 loops=1)
    ->  Sort  (cost=16.62..16.63 rows=4 width=36) (actual time=10.296..10.301 rows=5 loops=1)
          Sort Key: tid
          ->  Bitmap Heap Scan on h_pages  (cost=2.01..16.58 rows=4 width=36) (actual time=9.603..9.690 rows=5 loops=1)
                Filter: (fts @@ '''printer'''::tsquery)
                ->  Bitmap Index Scan on fts_idx  (cost=0.00..2.01 rows=4 width=0) (actual time=0.277..0.277 rows=5 loops=1)
                      Index Cond: (fts @@ '''printer'''::tsquery)
  Total runtime: 21.790 ms
(8 rows)

Time: 78.033 ms

test=# \d h_pages
          Table "public.h_pages"
  Column |       Type        | Modifiers 
--------+-------------------+-----------
  tid    | integer           | not null
  path   | character varying | not null
  body   | character varying |
  title  | character varying |
  fts    | tsvector          | 
Indexes:
     "h_pages_pkey" PRIMARY KEY, btree (tid)
     "h_pages_path_key" UNIQUE, btree (path)
     "fts_idx" gist (fts, tid)

btw, your explain output looks like you disable bitmapscan. Did you ?
Check postgresql.conf


 	Oleg

On Tue, 22 Nov 2005, Stas Filippov wrote:

> Hello,
>
> I am unable to use GIST index in ORDER BY clause.  I am trying to
> utilize tsearch2 for searching a textfield in a table and the sort the
> result set by another column.  E.g:
>
> CREATE TABLE sample (
>            :
>            textfield            text,
>            sortfield            int,
>            :.
>            vectors             tsvector
> );
>
> UPDATE sample SET vectors = to_tsvector( textfield );
> CREATE INDEX sample_vectors_sortfield_idx ON sample USING gist( vectors,
> sortfield );
>
> SET enable_seqscan=off;
>
> EXPLAIN SELECT textfield FROM sample WHERE vectors @@ to_tsquery('term')
> ORDER BY sortfield LIMIT 10;
>
>                                               QUERY PLAN
>
> ------------------------------------------------------------------------
> --------------------------------
> Limit  (cost=3.69..3.70 rows=1 width=36)
>   ->  Sort  (cost=3.69..3.70 rows=1 width=36)
>         Sort Key: sortfield
>         ->  Index Scan using sample_vectors_sortfield_idx on sample
> (cost=0.00..3.68 rows=1 width=36)
>               Index Cond: (vectors @@ '''term'''::tsquery)
>               Filter: (vectors @@ '''term'''::tsquery)
> (6 rows)
>
> As you can see, only the first part of the index is used - for searching
> the term; it then sorts the results instead of using the second part of
> the index which is sortfield.  I tried reordering the index fields with
> no effect
>
> It appears that GIST indexes cannot be used for ORDER BY clause in
> general.  Is that true? Why is there such limitation?
>
> I am comparing the results with MySQL fulltext search functionality and
> it considerably outperforms Postgres in the case when sorting described
> above is required.  Without the sorting both MySQL and Postgres are very
> fast.
>
> Thanks for any help in advance
> -Stas
>

 	Regards,
 		Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@..., http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

RE: [OpenFTS-general] GIST index and ORDER BY

From: Stas Filippov <stas@st...> - 2005-11-23 19:27

Actually my output for EXPLAIN ANALYZE is similar, but because of poor
performance it makes me wonder if the second half of the index is being
used.  Can you tell if the second half is used by looking at EXPLAIN
ANALYZE?

In general, can the second part of a two-column index be used for a
query such as:

	SELECT * FROM table WHERE col1='blabla' ORDER BY col2;

where there is an index ON table (col1,col2). In my case instead of
col1='blabla', it's vectors @@ to_tsquery('entry'), I don't know if that
makes a difference.

Not to deviate too far from the original problem -- I am trying to make
the following query perform under half a second:

SELECT textfield FROM sample WHERE vectors @@ to_tsquery('term') ORDER
BY sortfield LIMIT 10;

-Stas


-----Original Message-----
From: Oleg Bartunov [mailto:oleg@...] 
Sent: Wednesday, November 23, 2005 12:35 AM
To: Stas Filippov
Cc: openfts-general@...
Subject: Re: [OpenFTS-general] GIST index and ORDER BY

Stas,

I don't have 8.1 available for playing but 8.2dev works as expected

test=# explain analyze select tid, title from h_pages where fts @@
'printer' order by tid limit 10;
                                                          QUERY PLAN 
------------------------------------------------------------------------
----------------------------------------------------
  Limit  (cost=16.62..16.63 rows=4 width=36) (actual time=10.301..10.316
rows=5 loops=1)
    ->  Sort  (cost=16.62..16.63 rows=4 width=36) (actual
time=10.296..10.301 rows=5 loops=1)
          Sort Key: tid
          ->  Bitmap Heap Scan on h_pages  (cost=2.01..16.58 rows=4
width=36) (actual time=9.603..9.690 rows=5 loops=1)
                Filter: (fts @@ '''printer'''::tsquery)
                ->  Bitmap Index Scan on fts_idx  (cost=0.00..2.01
rows=4 width=0) (actual time=0.277..0.277 rows=5 loops=1)
                      Index Cond: (fts @@ '''printer'''::tsquery)
  Total runtime: 21.790 ms
(8 rows)

Time: 78.033 ms

test=# \d h_pages
          Table "public.h_pages"
  Column |       Type        | Modifiers 
--------+-------------------+-----------
  tid    | integer           | not null
  path   | character varying | not null
  body   | character varying |
  title  | character varying |
  fts    | tsvector          | 
Indexes:
     "h_pages_pkey" PRIMARY KEY, btree (tid)
     "h_pages_path_key" UNIQUE, btree (path)
     "fts_idx" gist (fts, tid)

btw, your explain output looks like you disable bitmapscan. Did you ?
Check postgresql.conf


 	Oleg

On Tue, 22 Nov 2005, Stas Filippov wrote:

> Hello,
>
> I am unable to use GIST index in ORDER BY clause.  I am trying to
> utilize tsearch2 for searching a textfield in a table and the sort the
> result set by another column.  E.g:
>
> CREATE TABLE sample (
>            :
>            textfield            text,
>            sortfield            int,
>            :.
>            vectors             tsvector
> );
>
> UPDATE sample SET vectors = to_tsvector( textfield );
> CREATE INDEX sample_vectors_sortfield_idx ON sample USING gist(
vectors,
> sortfield );
>
> SET enable_seqscan=off;
>
> EXPLAIN SELECT textfield FROM sample WHERE vectors @@
to_tsquery('term')
> ORDER BY sortfield LIMIT 10;
>
>                                               QUERY PLAN
>
>
------------------------------------------------------------------------
> --------------------------------
> Limit  (cost=3.69..3.70 rows=1 width=36)
>   ->  Sort  (cost=3.69..3.70 rows=1 width=36)
>         Sort Key: sortfield
>         ->  Index Scan using sample_vectors_sortfield_idx on sample
> (cost=0.00..3.68 rows=1 width=36)
>               Index Cond: (vectors @@ '''term'''::tsquery)
>               Filter: (vectors @@ '''term'''::tsquery)
> (6 rows)
>
> As you can see, only the first part of the index is used - for
searching
> the term; it then sorts the results instead of using the second part
of
> the index which is sortfield.  I tried reordering the index fields
with
> no effect
>
> It appears that GIST indexes cannot be used for ORDER BY clause in
> general.  Is that true? Why is there such limitation?
>
> I am comparing the results with MySQL fulltext search functionality
and
> it considerably outperforms Postgres in the case when sorting
described
> above is required.  Without the sorting both MySQL and Postgres are
very
> fast.
>
> Thanks for any help in advance
> -Stas
>

 	Regards,
 		Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@..., http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

RE: [OpenFTS-general] GIST index and ORDER BY

From: Oleg Bartunov <oleg@sa...> - 2005-11-23 20:59

On Wed, 23 Nov 2005, Stas Filippov wrote:

> Actually my output for EXPLAIN ANALYZE is similar, but because of poor

I don't think so. I don't see bitmaps are used in your case.

> performance it makes me wonder if the second half of the index is being
> used.  Can you tell if the second half is used by looking at EXPLAIN
> ANALYZE?

I've seen only output of EXPLAIN ANALYZE for your toy database which is not 
I want to spent my time on. Have you analyze performance with and without
final ORDER BY ? Are you sure the problem is here ? How many rows found ?

>
> In general, can the second part of a two-column index be used for a
> query such as:
>
> 	SELECT * FROM table WHERE col1='blabla' ORDER BY col2;
>
> where there is an index ON table (col1,col2). In my case instead of
> col1='blabla', it's vectors @@ to_tsquery('entry'), I don't know if that
> makes a difference.

it depends on distribution of real data. index is not always good thing.

Have you read about multicolumn indices ?
http://www.postgresql.org/docs/8.1/static/indexes-multicolumn.html
http://www.postgresql.org/docs/8.1/static/indexes-bitmap-scans.html
Also, do you really need multicolumn index ?

btw, do you have minimal test suite which illustrate your problem ?
>
> -Stas
>
>
> -----Original Message-----
> From: Oleg Bartunov [mailto:oleg@...]
> Sent: Wednesday, November 23, 2005 12:35 AM
> To: Stas Filippov
> Cc: openfts-general@...
> Subject: Re: [OpenFTS-general] GIST index and ORDER BY
>
> Stas,
>
> I don't have 8.1 available for playing but 8.2dev works as expected
>
> test=# explain analyze select tid, title from h_pages where fts @@
> 'printer' order by tid limit 10;
>                                                          QUERY PLAN
> ------------------------------------------------------------------------
> ----------------------------------------------------
>  Limit  (cost=16.62..16.63 rows=4 width=36) (actual time=10.301..10.316
> rows=5 loops=1)
>    ->  Sort  (cost=16.62..16.63 rows=4 width=36) (actual
> time=10.296..10.301 rows=5 loops=1)
>          Sort Key: tid
>          ->  Bitmap Heap Scan on h_pages  (cost=2.01..16.58 rows=4
> width=36) (actual time=9.603..9.690 rows=5 loops=1)
>                Filter: (fts @@ '''printer'''::tsquery)
>                ->  Bitmap Index Scan on fts_idx  (cost=0.00..2.01
> rows=4 width=0) (actual time=0.277..0.277 rows=5 loops=1)
>                      Index Cond: (fts @@ '''printer'''::tsquery)
>  Total runtime: 21.790 ms
> (8 rows)
>
> Time: 78.033 ms
>
> test=# \d h_pages
>          Table "public.h_pages"
>  Column |       Type        | Modifiers
> --------+-------------------+-----------
>  tid    | integer           | not null
>  path   | character varying | not null
>  body   | character varying |
>  title  | character varying |
>  fts    | tsvector          |
> Indexes:
>     "h_pages_pkey" PRIMARY KEY, btree (tid)
>     "h_pages_path_key" UNIQUE, btree (path)
>     "fts_idx" gist (fts, tid)
>
> btw, your explain output looks like you disable bitmapscan. Did you ?
> Check postgresql.conf
>
>
> 	Oleg
>
> On Tue, 22 Nov 2005, Stas Filippov wrote:
>
>> Hello,
>>
>> I am unable to use GIST index in ORDER BY clause.  I am trying to
>> utilize tsearch2 for searching a textfield in a table and the sort the
>> result set by another column.  E.g:
>>
>> CREATE TABLE sample (
>>            :
>>            textfield            text,
>>            sortfield            int,
>>            :.
>>            vectors             tsvector
>> );
>>
>> UPDATE sample SET vectors = to_tsvector( textfield );
>> CREATE INDEX sample_vectors_sortfield_idx ON sample USING gist(
> vectors,
>> sortfield );
>>
>> SET enable_seqscan=off;
>>
>> EXPLAIN SELECT textfield FROM sample WHERE vectors @@
> to_tsquery('term')
>> ORDER BY sortfield LIMIT 10;
>>
>>                                               QUERY PLAN
>>
>>
> ------------------------------------------------------------------------
>> --------------------------------
>> Limit  (cost=3.69..3.70 rows=1 width=36)
>>   ->  Sort  (cost=3.69..3.70 rows=1 width=36)
>>         Sort Key: sortfield
>>         ->  Index Scan using sample_vectors_sortfield_idx on sample
>> (cost=0.00..3.68 rows=1 width=36)
>>               Index Cond: (vectors @@ '''term'''::tsquery)
>>               Filter: (vectors @@ '''term'''::tsquery)
>> (6 rows)
>>
>> As you can see, only the first part of the index is used - for
> searching
>> the term; it then sorts the results instead of using the second part
> of
>> the index which is sortfield.  I tried reordering the index fields
> with
>> no effect
>>
>> It appears that GIST indexes cannot be used for ORDER BY clause in
>> general.  Is that true? Why is there such limitation?
>>
>> I am comparing the results with MySQL fulltext search functionality
> and
>> it considerably outperforms Postgres in the case when sorting
> described
>> above is required.  Without the sorting both MySQL and Postgres are
> very
>> fast.
>>
>> Thanks for any help in advance
>> -Stas
>>
>
> 	Regards,
> 		Oleg
> _____________________________________________________________
> Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
> Sternberg Astronomical Institute, Moscow University (Russia)
> Internet: oleg@..., http://www.sai.msu.su/~megera/
> phone: +007(095)939-16-83, +007(095)939-23-83
>

 	Regards,
 		Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@..., http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

RE: [OpenFTS-general] GIST index and ORDER BY

From: Stas Filippov <stas@st...> - 2005-11-24 00:14

Here is the EXPLAIN ANALYZE for the same table with 100k rows (I sent it
to the list earlier but apparently it got rejected because the list
email address was in CC field):

INSERT INTO sample (sortfield) SELECT generate_series(1, 100000);
UPDATE sample SET textfield='Text Entry '||sortfield;
UPDATE sample SET vectors = to_tsvector( textfield );
VACUUM ANALYZE VERBOSE sample;
SET enable_seqscan=off;
 
EXPLAIN ANALYZE SELECT textfield FROM sample WHERE vectors @@
to_tsquery('entry') ORDER BY sortfield LIMIT 10;

                                                                       
QUERY PLAN

------------------------------------------------------------------------
----
 Limit  (cost=332.78..332.80 rows=10 width=23) (actual
time=1469.045..1469.064 rows=10 loops=1)
   ->  Sort  (cost=332.78..333.03 rows=100 width=23) (actual
time=1469.038..1469.045 rows=10 loops=1)
         Sort Key: sortfield
         ->  Bitmap Heap Scan on sample  (cost=2.35..329.46 rows=100
width=23) (actual time=244.583..612.692 rows=100000 loops=1)
               Filter: (vectors @@ '''entri'''::tsquery)
               ->  Bitmap Index Scan on sample_vectors_sortfield_idx
(cost=0.00..2.35 rows=100 width=0) (actual time=243.048..243.048
rows=100000 loops=1)
                     Index Cond: (vectors @@ '''entri'''::tsquery)
 Total runtime: 1513.562 ms
(8 rows)


> Have you analyze performance with and without final ORDER BY ? Are you
sure the problem is here ? How many rows found ?

Yes, it runs fast without ORDER BY:

EXPLAIN ANALYZE SELECT textfield FROM sample WHERE vectors @@
to_tsquery('entry') LIMIT 10;
                         QUERY PLAN

------------------------------------------------------------------------
--
 Limit  (cost=2.35..35.06 rows=10 width=19) (actual
time=279.351..279.397 rows=10 loops=1)
   ->  Bitmap Heap Scan on sample  (cost=2.35..329.46 rows=100 width=19)
(actual time=279.344..279.378 rows=10 loops=1)
         Filter: (vectors @@ '''entri'''::tsquery)
         ->  Bitmap Index Scan on sample_vectors_sortfield_idx
(cost=0.00..2.35 rows=100 width=0) (actual time=267.077..267.077
rows=100000 loops=1)
               Index Cond: (vectors @@ '''entri'''::tsquery)
 Total runtime: 281.795 ms
(6 rows)

The problem seems to be with slow sorting.

> Have you read about multicolumn indices ?
> http://www.postgresql.org/docs/8.1/static/indexes-multicolumn.html
> http://www.postgresql.org/docs/8.1/static/indexes-bitmap-scans.html

Yes, but I wouldn't claim that I fully understand it yet :)

> Also, do you really need multicolumn index ?

I am trying to speed up the query and indexes are the only way I know
how.  I believe Postgres is not using the most efficient way of running
the query the way it is. I tried single column indexes for both columns,
both GIST and B-tree.

> btw, do you have minimal test suite which illustrate your problem ?

Would the sample table above be sufficient?

I'm still using 8.1 -- I haven't tried testing on 8.2dev yet

Thanks,
-Stas

RE: [OpenFTS-general] GIST index and ORDER BY

From: Oleg Bartunov <oleg@sa...> - 2005-11-24 09:59

On Wed, 23 Nov 2005, Stas Filippov wrote:

> Here is the EXPLAIN ANALYZE for the same table with 100k rows (I sent it
> to the list earlier but apparently it got rejected because the list
> email address was in CC field):
>
> INSERT INTO sample (sortfield) SELECT generate_series(1, 100000);
> UPDATE sample SET textfield='Text Entry '||sortfield;
> UPDATE sample SET vectors = to_tsvector( textfield );
> VACUUM ANALYZE VERBOSE sample;
> SET enable_seqscan=off;
>
> EXPLAIN ANALYZE SELECT textfield FROM sample WHERE vectors @@
> to_tsquery('entry') ORDER BY sortfield LIMIT 10;
>
>
> QUERY PLAN
>
> ------------------------------------------------------------------------
> ----
> Limit  (cost=332.78..332.80 rows=10 width=23) (actual
> time=1469.045..1469.064 rows=10 loops=1)
>   ->  Sort  (cost=332.78..333.03 rows=100 width=23) (actual
> time=1469.038..1469.045 rows=10 loops=1)
>         Sort Key: sortfield
>         ->  Bitmap Heap Scan on sample  (cost=2.35..329.46 rows=100
> width=23) (actual time=244.583..612.692 rows=100000 loops=1)
>               Filter: (vectors @@ '''entri'''::tsquery)
>               ->  Bitmap Index Scan on sample_vectors_sortfield_idx
> (cost=0.00..2.35 rows=100 width=0) (actual time=243.048..243.048
> rows=100000 loops=1)
>                     Index Cond: (vectors @@ '''entri'''::tsquery)
> Total runtime: 1513.562 ms
> (8 rows)
>
>
>> Have you analyze performance with and without final ORDER BY ? Are you
> sure the problem is here ? How many rows found ?
>
> Yes, it runs fast without ORDER BY:
>
> EXPLAIN ANALYZE SELECT textfield FROM sample WHERE vectors @@
> to_tsquery('entry') LIMIT 10;
>                         QUERY PLAN
>
> ------------------------------------------------------------------------
> --
> Limit  (cost=2.35..35.06 rows=10 width=19) (actual
> time=279.351..279.397 rows=10 loops=1)
>   ->  Bitmap Heap Scan on sample  (cost=2.35..329.46 rows=100 width=19)
> (actual time=279.344..279.378 rows=10 loops=1)
>         Filter: (vectors @@ '''entri'''::tsquery)
>         ->  Bitmap Index Scan on sample_vectors_sortfield_idx
> (cost=0.00..2.35 rows=100 width=0) (actual time=267.077..267.077
> rows=100000 loops=1)
>               Index Cond: (vectors @@ '''entri'''::tsquery)
> Total runtime: 281.795 ms
> (6 rows)
>
> The problem seems to be with slow sorting.

I don't understand why don't create separate index on sortfield and be 
happy ?

test=# explain analyze SELECT textfield FROM sample WHERE vectors @@ to_tsquery('entry') order by  sortfield limit 10;
                                                             QUERY PLAN 
-----------------------------------------------------------------------------------------------------------------------------------
  Limit  (cost=0.00..413.00 rows=10 width=23) (actual time=0.033..0.089 rows=10 loops=1)
    ->  Index Scan using sortfield_idx on sample  (cost=0.00..4130.00 rows=100 width=23) (actual time=0.031..0.066 rows=10 loops=1)
          Filter: (vectors @@ '\'entri\''::tsquery)
  Total runtime: 0.155 ms
(4 rows)

Time: 1.063 ms

Note, you query is not selective, so all 100K rows in table  matched and
fts index would not helps but actually slowdown query. Also, keep in mind
how LIMIT is implemented - first all 100K rows should be fetched from disk,
sorted and only then 10 rows from top outputs to client.

>
>> Have you read about multicolumn indices ?
>> http://www.postgresql.org/docs/8.1/static/indexes-multicolumn.html
>> http://www.postgresql.org/docs/8.1/static/indexes-bitmap-scans.html
>
> Yes, but I wouldn't claim that I fully understand it yet :)
>
>> Also, do you really need multicolumn index ?
>
> I am trying to speed up the query and indexes are the only way I know
> how.  I believe Postgres is not using the most efficient way of running
> the query the way it is. I tried single column indexes for both columns,
> both GIST and B-tree.

GiST indices are not ordered, so they couldn't speedup ORDER BY clause.
Use B-tree single column index on sortfield or multicolumn *B-tree* index
with sortfield as a *first key*. This is how B-tree works.


 	Regards,
 		Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@..., http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83