Postgres Pro
Downloads
Home   >   mailing lists

Thread: [GENERAL] How to include BIGINT-column in RUM-index sorted by timestamp

[GENERAL] How to include BIGINT-column in RUM-index sorted by timestamp

From
Andreas Joseph Krogh
Date:
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 references origo_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 del
WHERE del.fts_all @@ to_tsquery('simple', 'andre:*&jose:*') AND del.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
Attachment