After upgrading from cb 4.5 to cb 4.5.1,what we observer that ,if we creating index it will take more memory for GSI,compare to 4.5.
IN CB_4.5 we almost create 10 index and with same configuration and records, in CB_4.5.1 my all memory show exhausted.
any specific reason why.
Regards,
Rahul Kumar
@rkumar,
Could you please try the following and measure the time.
DROP index default.idx8;
CREATE INDEX idx8 ON default(LOCATION_ID,SOURCE_ID, DISTINCT ARRAY cv.QUESTION_ID FOR cv IN CONTENTS END , MENTION_TIME) WHERE ((“2001-01-01” <=MENTION_TIME) and (TYPE = “DOCUMENT”));
SELECT round(AVG( s.SCORE),1),COUNT(c.SENTIMENTS),count(distinct t.ID),t.PERSON_ID
FROM default t USE INDEX(idx8) UNNEST t.CONTENTS c UNNEST c.SENTIMENTS s
WHERE t.TYPE=“DOCUMENT” AND t.SOURCE_ID=289 AND t.LOCATION_ID=3600
AND ANY cv IN t.CONTENTS SATISFIES cv.QUESTION_ID = 1347 AND ANY sv IN cv.SENTIMENTS SATISFIES sv.CATEGORY_ID IN [4001,4002,4003,4004,4005,4010,4011,4012,4013,4014,4015,4016,4017,4018,4019,4020,4021,4022,4023,4024,4025,4026,4027,4028,4029,4030,4031,4032,4033,4034,4035,4036,4037,4038,4039,4040] END END
AND t.MENTION_TIME >=‘2001-01-01’ AND t.MENTION_TIME<‘2015-01-01’ AND ANY client IN t.CLIENT SATISFIES client =“retailpsi” END GROUP BY (t.PERSON_ID) ;
CREATE INDEX idx81 ON default(LOCATION_ID,SOURCE_ID,DISTINCT ARRAY client FOR client IN CLIENT END, MENTION_TIME) WHERE ((“2001-01-01” <=MENTION_TIME) and (TYPE = “DOCUMENT”));
CREATE INDEX idx_category ON default ( DISTINCT ARRAY ( DISTINCT ARRAY s.CATEGORY_ID FOR s IN c.SENTIMENTS END ) FOR c IN CONTENTS END )
WHERE TYPE = “DOCUMENT” AND MENTION_TIME >= “2001-01-01”;
CREATE INDEX idx_question ON default ( DISTINCT ARRAY c.QUESTION_ID FOR c IN CONTENTS END )
WHERE TYPE = “DOCUMENT” AND MENTION_TIME >= “2001-01-01”;
EXPLAIN SELECT round(AVG( s.SCORE),1),COUNT(c.SENTIMENTS),count(distinct t.ID),t.PERSON_ID
FROM default t USE INDEX(idx81,idx_question,idx_category) UNNEST t.CONTENTS c UNNEST c.SENTIMENTS s
WHERE t.TYPE=“DOCUMENT” AND t.SOURCE_ID=289 AND t.LOCATION_ID=3600
AND c.QUESTION_ID = 1347 AND s.CATEGORY_ID IN [4001,4002,4003,4004,4005,4010,4011,4012,4013,4014,4015,4016,4017,4018,4019,4020,4021,4022,4023,4024,4025,4026,4027,4028,4029,4030,4031,4032,4033,4034,4035,4036,4037,4038,4039,4040]
AND t.MENTION_TIME >=‘2001-01-01’ AND t.MENTION_TIME<‘2015-01-01’ AND ANY client IN t.CLIENT SATISFIES client =“retailpsi” END GROUP BY (t.PERSON_ID) ;
DROP index default.idx8c1;
CREATE INDEX idx8c1 ON default(LOCATION_ID,SOURCE_ID,DISTINCT ARRAY cv.QUESTION_ID FOR cv IN CONTENTS END, MENTION_TIME,PERSON_ID,ID,CLIENT,CONTENTS) WHERE ((“2001-01-01” <=MENTION_TIME) and (TYPE = “DOCUMENT”));
EXPLAIN SELECT round(AVG( s.SCORE),1),COUNT(c.SENTIMENTS),count(distinct t.ID),t.PERSON_ID
FROM default t USE INDEX(idx8c1) UNNEST t.CONTENTS c UNNEST c.SENTIMENTS s
WHERE t.TYPE=“DOCUMENT” AND t.SOURCE_ID=289 AND t.LOCATION_ID=3600
AND ANY cv IN t.CONTENTS SATISFIES cv.QUESTION_ID = 1347 AND ANY sv IN cv.SENTIMENTS SATISFIES sv.CATEGORY_ID IN [4001,4002,4003,4004,4005,4010,4011,4012,4013,4014,4015,4016,4017,4018,4019,4020,4021,4022,4023,4024,4025,4026,4027,4028,4029,4030,4031,4032,4033,4034,4035,4036,4037,4038,4039,4040] END END
AND t.MENTION_TIME >=‘2001-01-01’ AND t.MENTION_TIME<‘2015-01-01’ AND ANY client IN t.CLIENT SATISFIES client =“retailpsi” END GROUP BY (t.PERSON_ID) ;