[GENERAL] How to include BIGINT-column in RUM-index sorted by timestamp
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[])) │
└───────────────────────────────────────────────────────────────────────────────────────────────┘
│ 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
Вложения
В списке pgsql-general по дате отправления: