JOIN ON KEYS and Index scan timed out

SQL++
1

Hi experts,

I’m trying to join two documents types with this N1QL query:

SELECT serv.date_time AS date_time, serv.rc AS rc, serv.r_id, serv.w_id, SUM(serv.rrc_conn_stp_fail_trans), sum(pkt.ps_setup_fail_trans_bgr)
FROM default as serv JOIN default pkt ON KEYS TO_STRING("p_pkt" || "::" || to_string(serv.r_id) || "::" || to_string(serv.w_id) || "::" || to_string(serv.wcel_id) || "::" || to_string(serv.mcc_id) || "::" || to_string(serv.mnc_id) || "::" || to_string(serv.date_time)) 
WHERE serv.type="p_serv" AND serv.w_id=733140
GROUP BY serv.date_time, serv.rc, serv.r_id, serv.w_id
ORDER BY date_time ASC;

When I have around 1MM documents in DB it works, but when the DB is getting bigger it is returning this error after 2 minutes:

"code": 12015,
"msg": "Index scan timed out - cause: Index scan timed out"

explain.txt.zip (1.5 KB)

Maybe guys you have a recommendation to improve the query?,

I read in the documentation that it is possible to change the time scan, but I’ve couldnt find how to change this parameter.

Thanks guys, hope you well!

2

Hi @hector

This means, the index scan is scanning way too many items. See the articles I posted in the forum. (I couldn’t download the zip file).

what’s your index definition?

For this query, I’d expect a index on this as the starting point:

create index i1 on default(w_id) where type = “p_serv”;

To make the index scan a covering scan, you can include additional fields…

3

hi @keshav_m

Please improve joins its not ready to use in production scnenarios even though right side columns covered its dead slow unusable

4

hi @krishnasahu2016,

If you give me your index definitions, plan, I can comment.

5

i have groupid,user as covered index used in filter based on user i join with same bucket using on keys then order by of other columns out of scope of covered index

e.g. document (Original filter document)
ID:1
{ groupid:1,user:USER1,pricetoday:78,priceytd:88878,pricemtd:838393 etc lots of numbers and other attributes which are sortable}

join document check entity access
ID/;USER1
{entities[{ent1,ent2’}]}

6

Hi @krishnasahu2016,

Your problem was not the JOIN. It was the ORDER BY without using an index. I have answered on the other thread.