You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
Also, supposing the object_data jsonb has the following fields:
title, article, author and other fields(10)...
I have created the rum index article_rum_idx as follows...
> CREATE INDEX news_item_rum_english_weighted_tsv_idx ON news_items
> USING rum ( weighted_tsv rum_tsvector_addon_ops, time_created_as_from_server)
> WITH (attach = 'time_created_as_from_server', to = 'weighted_tsv');
When I query while referencing object_data and order by rank as follows:
> EXPLAIN ANALYZE
> select object_data, weighted_tsv <=> to_tsquery('English', 'Uhuru') rank
> from news_items
> where
> weighted_tsv @@ websearch_to_tsquery('English', 'Uhuru')
> ORDER BY rank
> limit 10;
Query executes in 418ms
When I query while referncing object_data->'title' and order by rank as follows:
> EXPLAIN ANALYZE
> select object_data->'title', weighted_tsv <=> to_tsquery('English', 'Uhuru') rank
> from news_items
> where
> weighted_tsv @@ websearch_to_tsquery('English', 'Uhuru')
> ORDER BY rank
> limit 10;
Query executes in 1.7s
That is almost 5x slower on exact same query and is consistent amongst different search terms.
Executing the same query without the ORDER BY executes much faster in around 3ms and there is not much difference between the two.
In this case it uses an Index Scan as opposed to the Bitmap Scan while its ordering by Rank and I am wondering how I could structure the query so that it always uses the must faster Index Scan.
Regards.
The text was updated successfully, but these errors were encountered:
Suppose you have this table
> CREATE TABLE news_items (id int, object_data JSONB, weighted_tsv tsvector, time_created_from_server bigint);
Also, supposing the object_data jsonb has the following fields:
title, article, author and other fields(10)...
I have created the rum index article_rum_idx as follows...
When I query while referencing object_data and order by rank as follows:
Query executes in 418ms
When I query while referncing object_data->'title' and order by rank as follows:
Query executes in 1.7s
That is almost 5x slower on exact same query and is consistent amongst different search terms.
Executing the same query without the ORDER BY executes much faster in around 3ms and there is not much difference between the two.
In this case it uses an Index Scan as opposed to the Bitmap Scan while its ordering by Rank and I am wondering how I could structure the query so that it always uses the must faster Index Scan.
Regards.
The text was updated successfully, but these errors were encountered: