Using Index not working correctly or am I using it wrong?

I have the following index-

CREATE INDEX `ix_events_active` ON `default`(`id`,`end_date`,`created_by`,`created_at`,`alert_id`,`name`,`child_counter`,`start_date`,
`zone_record`,`send_active`,`duration_type`,`creator_name`)
WHERE (((((`recipient_type` in [0, 3, 4])
and (`active_type` = 0))
and (`active_type` is not missing))
and (`parent_id` is missing))
and ((meta().`id`) like "event::%"))

And I am using the following query-

SELECT e.id, e.active_type, e.end_date, e.created_by, e.recipient_type, e.created_at, e.alert_id, e.name,
e.child_counter, e.start_date, e.zone_id_record, e.send_active, e.duration_type, e.creator_name AS user_name
FROM default e
WHERE e.active_type = 0
AND DATE_DIFF_STR(e.end_date, '2019-07-08T20:03:13', 'second') >= 0
AND e.created_by IN ['b37ddca31eae49085c96141ca6767ebd']
AND e.recipient_type IN [0, 3, 4] 
AND META(e).id LIKE 'event::%'
AND e.parent_id IS MISSING
AND e.active_type IS NOT MISSING
AND e.id IS NOT MISSING
ORDER BY e.name asc
LIMIT 10 OFFSET 0;

When I use the explain I can see that it is using intersect scan (ix_events_active and primary). Also the query is significantly slow(around 2 seconds). Even explicitly using the USE INDEX syntax doesn’t work, just slightly lesser time (1.3 seconds). I have tried different predicates in indexing and n1ql sections, but none seems to be working. Any solution please?

active_type = 0 implies active_type IS NOT MISSING. So you can drop that in index and query.
Drop primary index. If you can’t drop provide USE INDEX to avoid IntersectScan

The time taken is due to ORDER BY, Checkout https://blog.couchbase.com/create-right-index-get-right-performance/

CREATE INDEX `ix_events_active` ON `default`(`created_by`,`end_date`,`created_at`,`alert_id`,`name`,`child_counter`,`start_date`, `zone_record`,`send_active`,`duration_type`,`creator_name`, `id`)
WHERE `recipient_type` IN [0, 3, 4]
       AND `active_type` = 0
       AND `parent_id` IS MISSING
       AND meta().`id` LIKE "event::%";


SELECT e.id, e.active_type, e.end_date, e.created_by, e.recipient_type,
       e.created_at, e.alert_id, e.name, e.child_counter, e.start_date,
       e.zone_id_record, e.send_active, e.duration_type, e.creator_name AS user_name
FROM default e USE INDEX (ix_events_active)
WHERE e.active_type = 0
AND e.end_date >= "2019-07-08T20:03:13"
AND e.created_by IN ['b37ddca31eae49085c96141ca6767ebd']
AND e.recipient_type IN [0, 3, 4]
AND META(e).id LIKE 'event::%'
AND e.parent_id IS MISSING
AND e.id IS NOT MISSING
ORDER BY e.name asc
LIMIT 10 OFFSET 0;

Thanks for the advice. But I dropped active_type IS NOT MISSING clause. And explicitly used index by name as before. But still time is taking like before. I tried without the order by, still the same. I read few blog posts including what you provided, still couldn’t figure out what’s the thing causing this lag. Then I prefixed ‘EXPLAIN’ in my main code rather than in the couchbase console, and found out couchbase couldn’t recognise index because of parameters and when I used static values instead of parameters it was working perfect! Why this would happen? In that case I used explicit values, but there are some cases where I can’t. Any suggestion please?

You need to post the statement that uses parameters. Predicate as part of index condition can’t have dynamic parameters because prepare statement must work with different dynamic values, It may use different index.
I.e. query predicate that match index condition must be static or set adhoc=true (no prepare statements).
Also in case of IN clause right side is array is dynamic parameter it can’t push to indexer, must be processed in query .

Here is the index prepared for a query which has dynamic parameterised value-

CREATE INDEX `ix_events_template` ON `default`(`created_by`,`name`,`id`,`created_at`,`alert_id`,`send_active`,`creator_name`,
shared_with)
WHERE `active_type` = 4
AND `recipient_type` IN [0, 3, 4]
AND `parent_id` IS MISSING
AND META().`id` LIKE "event::%"

And this is the query-

SELECT e.created_by, e.name, e.id, e.alert_id, e.send_active, e.creator_name AS user_name,
   e.active_type, e.recipient_type
FROM default e
USE INDEX (ix_events_template)
WHERE e.active_type=\$activeType
AND (e.created_by IN ['b37ddca31eae49085c96141ca6767ebd'])
AND e.recipient_type IN [0, 3, 4] 
AND META(e).id LIKE 'event::%'
AND e.parent_id IS MISSING
AND e.created_by IS NOT MISSING
ORDER BY e.created_at DESC  LIMIT 

WHERE e.active_type=\$activeType this is the only predicate having parameter. Worth mentioning, I’m using couchbase server 4.5.1.

So, i have to skip using predicates having dynamic values in indexes, and I can use them only in the query. If that is the case, I understand now, silly me. Thanks for you valuable time!

Move active_type from index where clause to index key.

If index has WHERE active_type = 0 and query has active_type = $activeType . Query planning done during prepare time and at that time query parameters are not available. but Index has only entires of active_type = 0 and later if $activeType = 1 means no rows will return. This result in wrong result that is why index will not qualify (Index qualification done on static values)

1 Like

“Query planning done during prepare time and at that time query parameters are not available”—Now I’ve got it! Thanks for the explanation.

1 Like