why the rum index not working

[zalcit]

CREATE TABLE testrum(c1 tsvector);
insert into testrum values (to_tsvector('hello rum, i''m digoal')), (to_tsvector('hello world, i''m postgresql')), (to_tsvector('how are you, i''m digoal'));
EXPLAIN select *,c1 <=> to_tsquery('postgresql') from testrum order by c1 <=> to_tsquery('postgresql');
Sort  (cost=1.81..1.82 rows=3 width=36)
  Sort Key: ((c1 <=> to_tsquery('postgresql'::text)))
  ->  Seq Scan on testrum  (cost=0.00..1.79 rows=3 width=36)

[obartunov]

You didn't make rum index :)

…

On Fri, Apr 29, 2022 at 7:17 AM ZhuQingChao ***@***.***> wrote: CREATE TABLE testrum(c1 tsvector); insert into testrum values (to_tsvector('hello rum, i''m digoal')), (to_tsvector('hello world, i''m postgresql')), (to_tsvector('how are you, i''m digoal')); EXPLAIN select *,c1 <=> to_tsquery('postgresql') from testrum order by c1 <=> to_tsquery('postgresql'); Sort (cost=1.81..1.82 rows=3 width=36) Sort Key: ((c1 <=> to_tsquery('postgresql'::text))) -> Seq Scan on testrum (cost=0.00..1.79 rows=3 width=36) — Reply to this email directly, view it on GitHub <#106>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABQURYUCZ55ZKJOV4LMFAPTVHNPG3ANCNFSM5UUR7LCQ> . You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>

-- Postgres Professional: http://www.postgrespro.com The Russian Postgres Company

[zalcit]

I created the rum index. I am sure.

[funny-falcon]

@zhuqingchao in your example in first comment ( #106 (comment) ) there is no CREATE INDEX.

Please, provide example with CREATE INDEX.

[obartunov]

On Thu, May 5, 2022 at 11:17 AM ZhuQingChao ***@***.***> wrote: I created the rum index. I am sure.

Please, provide how did you create rum index

…

— Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you commented.Message ID: ***@***.***>

-- Postgres Professional: http://www.postgrespro.com The Russian Postgres Company

[zalcit]

@funny-falcon @obartunov Sorry for long time no login to github。
I created the index as follows:
CREATE INDEX idx_testrum ON testrum USING rum(c1 rum_tsvector_ops);
// or just
CREATE INDEX idx_testrum ON testrum USING rum(c1);
Both rum index not working. I wonder if the amount of data is too small?
I also tested with RANDOM_PAGE_COST setting of pg.
The default value of RANDOM_PAGE_COST is 4, When i SET RANDOM_PAGE_COST = 0.1; for test, its no effect.
But this setting effected when i test my Chinese word segmentation plugin:pg_jieba

[obartunov]

On Mon, Jul 4, 2022 at 8:17 AM ZhuQingChao ***@***.***> wrote: @funny-falcon <https://github.com/funny-falcon> @obartunov <https://github.com/obartunov> Sorry for long time no login to github。 I created the index as follows: CREATE INDEX idx_testrum ON testrum USING rum(c1 rum_tsvector_ops); // or just CREATE INDEX idx_testrum ON testrum USING rum(c1); Both rum index not working. I wonder if the amount of data is too small?

yes, index scan is useless for this data. Try to disable seq scan as below: [local]:5433 ***@***.***=# set enable_seqscan to off; SET Time: 0.110 ms [local]:5433 ***@***.***=# EXPLAIN analyze select *,c1 <=> to_tsquery('postgresql') from testrum order by c1 <=> to_tsquery('postgresql'); QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------- Index Scan using idx_testrum on testrum (cost=3600.00..3608.80 rows=3 width=36) (actual time=0.102..0.110 rows=3 loops=1) Order By: (c1 <=> to_tsquery('postgresql'::text)) Planning Time: 0.070 ms Execution Time: 0.136 ms (4 rows) Time: 0.518 ms

…

I also tested with RANDOM_PAGE_COST setting of pg. The default value of RANDOM_PAGE_COST is 4, When i SET RANDOM_PAGE_COST = 0.1; for test, its no effect. But this setting effected when i test my Chinese word segmentation plugin:pg_jieba — Reply to this email directly, view it on GitHub <#106 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABQURYTVVLPTMXJQEVYH3ADVSJXV3ANCNFSM5UUR7LCQ> . You are receiving this because you were mentioned.Message ID: ***@***.***>

-- Postgres Professional: http://www.postgrespro.com The Russian Postgres Company

[zalcit]

So the index is worked, i think.
but i wonder if this is expected behavior? When this data is to small or seq scan or some what else, the rum index is useless.
And this index will be used at the right time ? how to understand the 'right time'. tks

[obartunov]

On Wed, Jul 6, 2022, 07:50 ZhuQingChao ***@***.***> wrote: So the index is worked, i think. but i wonder if this is expected behavior? When this data is to small or seq scan or some what else, the rum index is useless.

This is how Postgres optimizer works. Index is not a silver bullet for speeding up query, it also introduces some overhead.

And this index will be used at the right time ? how to understand the 'right time'. tks

Read how postgres optimizer works.

…

— Reply to this email directly, view it on GitHub <#106 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABQURYSM3Y62TYMRY3VPT7DVSUGAHANCNFSM5UUR7LCQ> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[zalcit]

tks