N1QL Query Optimization help

Couchbase Server
1

Hi Team,

We had a below query to optimize which is currently completes in 8.2s with 202 docs.

SELECT DISTINCT a.hnwid,
a.rcode,
b.zcode,
b.cosid
FROM config a
JOIN config b ON a.zcode=b.zcode
WHERE a.__t=‘ntr-region-details’
AND b.__t=‘ntr-zonecosm’
AND b.cosid IN [300,301,302,307,309,310,311,313,314,315,316,317,318,320,321,322,323,324,325,326,327,329,331,332,333,334,335,337,5001,339,343,345,346,349,350,351,354,353,355,356,357,358,360,364,365,366,367,368,369,370,371,372,374,377,381,380,383,384,385,386,387,389,390,391,392,393,395,396,397,398,399,400,401,405,407,409,411,413,415,416,417,418,419,420,425,426,427,429,5010,431,432,433,434,436,437,439,441,440,443,446,445,449,450,451,453,455,456,457,458,459,462,463,464,465,472,473,474,475,476,477,478,479,480,482,530,483,484,486,487,489,490,491,492,493,494,495,496,497,499,500,501,502,506,507,504,509,511,512,513,514,515,517,518,519,30918]
AND a.zcode IN [300,301,302,307,309,310,311,313,314,315,316,317,318,320,321,322,323,324,325,326,327,329,331,332,333,334,335,337,5001,339,343,345,346,349,350,351,354,353,355,356,357,358,360,364,365,366,367,368,369,370,371,372,374,377,381,380,383,384,385,386,387,389,390,391,392,393,395,396,397,398,399,400,401,405,407,409,411,413,415,416,417,418,419,420,425,426,427,429,5010,431,432,433,434,436,437,439,441,440,443,446,445,449,450,451,453,455,456,457,458,459,462,463,464,465,472,473,474,475,476,477,478,479,480,482,530,483,484,486,487,489,490,491,492,493,494,495,496,497,499,500,501,502,506,507,504,509,511,512,513,514,515,517,518,519,30918] and a.hnwid=53
AND a.zcode<30000;

query_plan_2812.zip (6.0 KB)

Query plan is attached . We check that during Index scan3 "servTime" is taking almost 8.195218515s. The below index is currently used. Please advise.

CREATE INDEX adv_t_zcode_cosid_hnwid ON config(__t,hnwid,zcode,cosid);

2

you have too many in clause elements. 2 IN clauses makes cartesian product

3

Thanks @vsr1 for response. Could you please help me how remove two clause and make a single clause because we need both zcode and cosid to be selected from config bucket.

Thanks,
Debasis

4 
CREATE INDEX ix1 ON config(hnwid,zcode,rcode) WHERE __t="ntr-region-details";
CREATE INDEX ix2 ON config(cosid,zcode) WHERE __t="ntr-zonecosm";

SELECT DISTINCT a.hnwid, a.rcode, b.zcode, b.cosid
FROM config a
JOIN config b USE HASH(BUILD) ON a.zcode = b.zcode
WHERE a.__t = "ntr-region-details"
      AND b.__t = "ntr-zonecosm"
      AND b.cosid IN [300,301,302,307,309,310,311,313,314,315,316,317,318,320,321,322,323,324,325,326,327,329,331,332,333,334,335,337,5001,339,343,345,346,349,350,351,354,353,355,356,357,358,360,364,365,366,367,368,369,370,371,372,374,377,381,380,383,384,385,386,387,389,390,391,392,393,395,396,397,398,399,400,401,405,407,409,411,413,415,416,417,418,419,420,425,426,427,429,5010,431,432,433,434,436,437,439,441,440,443,446,445,449,450,451,453,455,456,457,458,459,462,463,464,465,472,473,474,475,476,477,478,479,480,482,530,483,484,486,487,489,490,491,492,493,494,495,496,497,499,500,501,502,506,507,504,509,511,512,513,514,515,517,518,519,30918]
     AND a.zcode IN [300,301,302,307,309,310,311,313,314,315,316,317,318,320,321,322,323,324,325,326,327,329,331,332,333,334,335,337,5001,339,343,345,346,349,350,351,354,353,355,356,357,358,360,364,365,366,367,368,369,370,371,372,374,377,381,380,383,384,385,386,387,389,390,391,392,393,395,396,397,398,399,400,401,405,407,409,411,413,415,416,417,418,419,420,425,426,427,429,5010,431,432,433,434,436,437,439,441,440,443,446,445,449,450,451,453,455,456,457,458,459,462,463,464,465,472,473,474,475,476,477,478,479,480,482,530,483,484,486,487,489,490,491,492,493,494,495,496,497,499,500,501,502,506,507,504,509,511,512,513,514,515,517,518,519,30918]
     AND a.hnwid = 53
     AND a.zcode < 30000;
1 Like
5

What do “USE HASH(BUILD)” in this scenario ?

6

instead of nested loop join uses hash join

7

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.