Right way to create Index with partitioning, looks like indexes are not getting updated

SQL++
, ,
1

Hi

We have document which has an attribute status, the initial value of this status would be PENDING. Later after some batch job runs the status gets updated to SUCCESS or ERROR.

We have following indexes created to retreive the counts based on status,

CREATE INDEX legacy_createdDateTime_class_status_pending_reqDuplicated_false ON promotions(createdDateTime) PARTITION BY HASH(META().Id)
WHERE _class = ‘XXXXXX’ AND status = ‘PENDING’ AND requestDuplicated = false
using GSI WITH {“num_replica”: 1,“num_partition”:6} ;

CREATE INDEX legacy_createdDateTime_class_status_success_reqDuplicated_false ON promotions(createdDateTime) PARTITION BY HASH(META().Id)
WHERE _class = ‘XXXXXX’ AND status = ‘SUCCESS’ AND requestDuplicated = false
using GSI WITH {“num_replica”: 1,“num_partition”:6} ;

CREATE INDEX legacy_createdDateTime_class_status_error_reqDuplicated_false ON promotions(createdDateTime) PARTITION BY HASH(META().Id)
WHERE _class = ‘XXXXXX’ AND status = ‘ERROR’ AND requestDuplicated = false
using GSI WITH {“num_replica”: 1,“num_partition”:6} ;

As an example,
if there are 100 PENDING documents,
out of which 95 became SUCCESS,
5 became ERROR.

When executing n1ql query the count of SUCCESS AND ERROR documents are coming correctly with 95 and 5 respectively. But when executing n1ql count query for PENDING it shows 100 rather than 0. Looks like the index is not getting update. Any help on this would be appreciated.

Count query:

SELECT count(*) FROM promotions commandStatus WHERE _class = “XXXXXX”
AND commandStatus.status = “SUCCESS” (or PENDING or ERROR)
AND commandStatus.requestDuplicated = false
AND commandStatus.createdDateTime BETWEEN 1579478400000 AND 1580428800000

Regards,
Venkat

3

@sri_ram, I see that the three indexes created are both partitioned and partial indexes, that is, they are hash partitioned as well as range partitioned. Is there a reason why they are created this way? Is the resulting index too big to be created on a single node?
Meanwhile, we are investigating the issue you are facing.

Thanks,
Prathibha

4

Yes the data is definitely huge and almost over 100 million.

I forgot to mention one more thing,

When we do select * (to list the documents) instead of select count then I am not getting any results. But select count is giving me 100 as an example.

5

may not be related. otherwise all goes in single partition

must be META().id (i.e id all lower)

drop the index and recreate with PARTITION BY HASH(META().id) and see if problem solves

one index is enough if data is small.

CREATE INDEX ix1  ON  `promotions` ( status, requestDuplicated, `createdDateTime` ) 
PARTITION BY HASH(META().id) WHERE  `_class`  = ‘XXXXXX’ 
using GSI WITH {“num_replica”: 1,“num_partition”:6} ;
6

Will update you soon, many thanks for the input

7

@vsr1

looks like the change has worked. Many thanks for identifying it.

Regards,
Venkat

8

@sri_ram : Did the only change you make was Id to id or did you also move status to index definition?

9

Only change of id worked, didn’t want to move status to index definition as we don’t want to have a huge index containing all status.