Tuning a query in N1ql

SQL++
1

I am running below query that takes 35 sec to give 462 count.

SELECT dcpc.cpc, dcpc.cty_iso2, dcpc.cty_iso2_ext, dcpc.cur_iso3 FROM default AS dcpc 
WHERE dcpc.dtype = "tpc" AND dcpc.cty_iso2 IN (SELECT RAW country.iso2 FROM default AS dcpc UNNEST dcpc.countries AS country WHERE dcpc.dtype = "ttx" AND dcpc.locale = "EN")

It is using secondary index on dtype. Inorder to improve its performance i created one more index :

Create index idx_iso2 on default(DISTINCT ARRAYRAY dcpc.iso2 FOR dcpc in countries END) WITH { "num_replica":1 }

When checked into execution plan it is not using the above index.
Any suggestion here ?

Thanks
Ritu

2 
Create index idx_iso2 on default(locale) WHERE type = "ttx";

Create index ix1 on default(cty_iso2, cpc, cty_iso2_ext, cur_iso3) WHERE type = "tpc";

SELECT dcpc.cpc, dcpc.cty_iso2, dcpc.cty_iso2_ext, dcpc.cur_iso3 FROM default AS dcpc
WHERE dcpc.dtype = “tpc” AND dcpc.cty_iso2 IN (SELECT RAW ARRAY_FLATTEN(ARRAY_AGG(dcpc.country[*].iso2),2) FROM default AS dcpc WHERE dcpc.dtype = “ttx” AND dcpc.locale = “EN”)

The right side of IN clause dynamic expression so can’t be pushed to indexscan, that is why it takes time.
You can separate 2 queries . In your client execute subquery get results and pass that to second query has parameter with adhoc=true