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

From: Andreas Joseph Krogh <andreas(at)visena(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: How to include BIGINT-column in RUM-index sorted by timestamp
Date: 2017-05-15 15:24:40
Message-ID: VisenaEmail.1b.3c6d64e6b4786a71.15c0ca87872@tc7-visena
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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
andreas(at)visena(dot)com <mailto:andreas(at)visena(dot)com>
www.visena.com <https://www.visena.com>
<https://www.visena.com>

Browse pgsql-general by date

  From Date Subject
Next Message Jack 2017-05-15 17:00:13 Conditional join in function
Previous Message matshyeq 2017-05-15 10:34:31 Re: Pattern Matching question - PG 9.6