Hi PostgresPro (RUM-creators).
With the latest version of RUM: https://github.com/postgrespro/rum
, one is able to store BIGINT as part of the index, it seems. I'm however not
able to make a query use the index without the Filter-step.
This table is roughly like this:
CREATE TABLE email_delivery( received_timestamp timestamp not null, folder_id
bigint NOT NULL referencesorigo_email_folder(entity_id), fts_all tsvector );
I have created the index like this:
CREATE index rum_idx ON origo_email_delivery using rum (fts_all
rum_tsvector_addon_ops, folder_id, received_timestamp)with (attach =
'received_timestamp', to = 'fts_all');
The query is:
EXPLAIN (COSTS OFF) SELECT del.entity_id, del.received_timestamp,
del.received_timestamp <=>'3000-01-01' :: TIMESTAMP, del.folder_id FROM
email_delivery delWHERE del.fts_all @@ to_tsquery('simple', 'andre:*&jose:*')
ANDdel.folder_id IN (44965, 2470520) ORDER BY del.received_timestamp <=>
'3000-01-01':: TIMESTAMP LIMIT 10;
which gives the following plan:
┌───────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN
│
├───────────────────────────────────────────────────────────────────────────────────────────────┤
│ Limit
│
│ -> Index Scan using rum_idx on email_delivery del
│
│ Index Cond: (fts_all @@ '''andre'':* & ''jose'':*'::tsquery)
│
│ Order By: (received_timestamp <=> '3000-01-01 00:00:00'::timestamp
without time zone) │
│ Filter: (folder_id = ANY ('{44965,2470520}'::bigint[]))
│
└───────────────────────────────────────────────────────────────────────────────────────────────┘
This indicates that the folder_id column, which is part of the index, isn't
actually "part of the index", meaning it's not used in the index for some
reason.
Can you tell me how to craft an index so that folder_id = ANY
('{44965,2470520}'::bigint[]) is part of the Index Cond?
Thanks.
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com <mailto:andr...@visena.com>
www.visena.com <https://www.visena.com>
<https://www.visena.com>
