Understanding N1QL Plan Text

SQL++
1

Hi,

I have below query:

SELECT a.data_number AS dataNumber
     FROM mybucket a 
WHERE a.type_ = 'TEST'
     AND a.group_number = '1'
    AND a.accounting_year = 2021
    AND a.accounting_month = 1
 AND a.transaction_code = 'xxx'
 AND a.transaction_date IS NOT MISSING
ORDER BY a.transaction_date 
LIMIT 10 OFFSET 10

CREATE INDEX `idx-mybucket-TEST-01-Group` ON `mybucket`(`type_`,`group_number`,`accounting_year`,`accounting_month`,`total`,`premium_category`,`billing_frequency`,`counter`,`data_number`,`currency`,`transaction_code`,`transaction_date`) PARTITION BY hash(`accounting_year`,`accounting_month`) WHERE (`type_` = "TEST") WITH { "defer_build":true, "num_partition":8 }

I’ve found although I limit to 10 rows, the itemsOut is around 17k data that makes it slow during the process:

{
  "#operator": "Sequence",
  "#stats": {
    "#phaseSwitches": 1,
    "execTime": "5.429µs"
  },
  "~children": [
    {
      "#operator": "Authorize",
      "#stats": {
        "#phaseSwitches": 3,
        "execTime": "24.508µs",
        "servTime": "2.261147ms"
      },
      "privileges": {
        "List": [
          {
            "Target": "default:mybucket",
            "Priv": 7
          }
        ]
      },
      "~child": {
        "#operator": "Sequence",
        "#stats": {
          "#phaseSwitches": 1,
          "execTime": "10.001µs"
        },
        "~children": [
          {
            "#operator": "Sequence",
            "#stats": {
              "#phaseSwitches": 1,
              "execTime": "3.297µs"
            },
            "~children": [
              {
                "#operator": "IndexScan3",
                "#stats": {
                  "#itemsOut": 17590,
                  "#phaseSwitches": 70363,
                  "execTime": "112.942389ms",
                  "kernTime": "10.93407ms",
                  "servTime": "196.44694ms"
                },
                "as": "a",
                "covers": [
                  "cover ((`a`.`type_`))",
                  "cover ((`a`.`group_number`))",
                  "cover ((`a`.`accounting_year`))",
                  "cover ((`a`.`accounting_month`))",
                  "cover ((`a`.`total`))",
                  "cover ((`a`.`premium_category`))",
                  "cover ((`a`.`billing_frequency`))",
                  "cover ((`a`.`counter`))",
                  "cover ((`a`.`data_number`))",
                  "cover ((`a`.`currency`))",
                  "cover ((`a`.`transaction_code`))",
                  "cover ((`a`.`transaction_date`))",
                  "cover ((meta(`a`).`id`))"
                ],
                "filter_covers": {
                  "cover ((`a`.`type_`))": "TEST"
                },
                "index": "idx-mybucket-TEST-01-Group",
                "index_id": "6c3926823f85f417",
                "index_projection": {
                  "entry_keys": [
                    0,
                    1,
                    2,
                    3,
                    8,
                    10,
                    11
                  ]
                },
                "keyspace": "mybucket",
                "namespace": "default",
                "spans": [
                  {
                    "exact": true,
                    "range": [
                      {
                        "high": "\"TEST\"",
                        "inclusion": 3,
                        "low": "\"TEST\""
                      },
                      {
                        "high": "\"1\"",
                        "inclusion": 3,
                        "low": "\"1\""
                      },
                      {
                        "high": "2021",
                        "inclusion": 3,
                        "low": "2021"
                      },
                      {
                        "high": "1",
                        "inclusion": 3,
                        "low": "1"
                      },
                      {
                        "inclusion": 0
                      },
                      {
                        "inclusion": 0
                      },
                      {
                        "inclusion": 0
                      },
                      {
                        "inclusion": 0
                      },
                      {
                        "inclusion": 0
                      },
                      {
                        "inclusion": 0
                      },
                      {
                        "high": "\"xxx\"",
                        "inclusion": 3,
                        "low": "\"xxx\""
                      },
                      {
                        "inclusion": 1,
                        "low": "null"
                      }
                    ]
                  }
                ],
                "using": "gsi",
                "#time_normal": "00:00.309",
                "#time_absolute": 0.30938932900000005
              },
              {
                "#operator": "Sequence",
                "#stats": {
                  "#phaseSwitches": 1,
                  "execTime": "2.943µs"
                },
                "~children": [
                  {
                    "#operator": "Filter",
                    "#stats": {
                      "#itemsIn": 17590,
                      "#itemsOut": 17590,
                      "#phaseSwitches": 70363,
                      "execTime": "109.082068ms",
                      "kernTime": "211.354155ms"
                    },
                    "condition": "((((((cover ((`a`.`type_`)) = \"TEST\") and (cover ((`a`.`group_number`)) = \"1\")) and (cover ((`a`.`accounting_year`)) = 2021)) and (cover ((`a`.`accounting_month`)) = 1)) and (cover ((`a`.`transaction_code`)) = \"xxx\")) and (cover ((`a`.`transaction_date`)) is not missing))",
                    "#time_normal": "00:00.109",
                    "#time_absolute": 0.109082068
                  },
                  {
                    "#operator": "InitialProject",
                    "#stats": {
                      "#itemsIn": 17590,
                      "#itemsOut": 17590,
                      "#phaseSwitches": 70363,
                      "execTime": "82.005176ms",
                      "kernTime": "238.496995ms"
                    },
                    "result_terms": [
                      {
                        "as": "dataNumber",
                        "expr": "cover ((`a`.`data_number`))"
                      }
                    ],
                    "#time_normal": "00:00.082",
                    "#time_absolute": 0.082005176
                  }
                ],
                "#time_normal": "00:00.000",
                "#time_absolute": 0.000002943
              }
            ],
            "#time_normal": "00:00.000",
            "#time_absolute": 0.000003297
          },
          {
            "#operator": "Order",
            "#stats": {
              "#itemsIn": 17590,
              "#itemsOut": 20,
              "#phaseSwitches": 35205,
              "execTime": "25.402968ms",
              "kernTime": "295.21339ms"
            },
            "limit": "10",
            "offset": "10",
            "sort_terms": [
              {
                "expr": "cover ((`a`.`transaction_date`))"
              }
            ],
            "#time_normal": "00:00.025",
            "#time_absolute": 0.025402968
          },
          {
            "#operator": "Offset",
            "#stats": {
              "#itemsIn": 20,
              "#itemsOut": 10,
              "#phaseSwitches": 31,
              "execTime": "13.572µs"
            },
            "expr": "10",
            "#time_normal": "00:00.000",
            "#time_absolute": 0.000013572
          },
          {
            "#operator": "Limit",
            "#stats": {
              "#itemsIn": 10,
              "#itemsOut": 10,
              "#phaseSwitches": 21,
              "execTime": "7.677µs"
            },
            "expr": "10",
            "#time_normal": "00:00.000",
            "#time_absolute": 0.000007677
          },
          {
            "#operator": "FinalProject",
            "#stats": {
              "#itemsIn": 10,
              "#itemsOut": 10,
              "#phaseSwitches": 31,
              "execTime": "9.742µs",
              "kernTime": "121.047µs"
            },
            "#time_normal": "00:00.000",
            "#time_absolute": 0.000009742000000000001
          }
        ],
        "#time_normal": "00:00.000",
        "#time_absolute": 0.000010001
      },
      "#time_normal": "00:00.002",
      "#time_absolute": 0.0022856549999999997
    },
    {
      "#operator": "Stream",
      "#stats": {
        "#itemsIn": 10,
        "#itemsOut": 10,
        "#phaseSwitches": 23,
        "execTime": "97.627µs",
        "kernTime": "323.01018ms"
      },
      "#time_normal": "00:00.000",
      "#time_absolute": 0.00009762699999999999
    }
  ],
  "~versions": [
    "6.5.0-N1QL",
    "6.5.1-6299-enterprise"
  ],
  "#time_normal": "00:00.000",
  "#time_absolute": 0.000005429
}

What do I missed in here? Why total itemsOut is still 17k instead of 10?
Do I need to create another indexes to fasten the query performance?

Thanks

2

The index listed one using query plan are different.

3

Hi @vsr1,
Actually I’ve tried to rename and remove unused fields from my couchbase data.(maybe I missed to remove/rename some fields)

But that query plan is the result from my query.

4

The plan does’t seems right. Drop and re-create and try again.

5

Hi @vsr1,

I;ve modified my post using simplified query and remove the join. It gets faster, but still I need to know why the itemsOut is not as expected. Do you mean my index is already correct for that query?

6

@Han_Chris1,

CREATE INDEX `idx-mybucket-TEST-01-Group` ON `mybucket` ( `group_number` , `accounting_year` , `accounting_month` ,  `transaction_code` , `transaction_date`, `total` , `premium_category` , `billing_frequency` , `counter` , `data_number` , `currency`   ) PARTITION BY hash( `accounting_year` , `accounting_month` ) WHERE ( `type_` = “TEST”) WITH { “defer_build”:true, “num_partition”:8 }

SELECT a.data_number AS dataNumber
FROM mybucket a
WHERE a.type_ = ‘TEST’
AND a.group_number = ‘1’
AND a.accounting_year = 2021
AND a.accounting_month = 1
AND a.transaction_code = ‘xxx’
AND a.transaction_date IS NOT MISSING
ORDER BY a.transaction_date
LIMIT 10 OFFSET 10

Use above index . Query order by follows index keys order (equality predicates in query don’t have to present in query order by)

1 Like
7

Thanks @vsr1 for your help, it works as expected.

Still curious for JOIN query, for e.g:

SELECT a.data_number,b.name
FROM mybucket a
INNER
JOIN Analytics b ON KEYS CONCAT(‘DATA2::’,a.data_number)
WHERE a.type_ = ‘TEST’
AND a.group_number = ‘1’
AND a.accounting_year = 2021
AND a.accounting_month = 1
AND a.transaction_code = ‘xxx’
AND a.transaction_date IS NOT MISSING
AND b.type_ = ‘TEST2’
AND b.name IS NOT MISSING
ORDER BY b.name
LIMIT 10 OFFSET 10

I have created another index like this:

CREATE INDEX idx-mybucket-TEST-02-Group ON mybucket(name) WHERE type_ = ‘TEST2’

but total itemsOut still 17k instead of 10.
I assume using on keys will not trigger another index.
How to fix that?

8

The order is right side of JOIN. Can’t use index order and required sort. So, need to produce all qualified results. Lost entry might be come sorted first

9

So, if I use JOIN although using keys, will be slow?
Is there another work around? JOIN using reguler index instead of key?

10

You have mored predicates on left side. switching join order may not help, due to no predicates on right

11

Hi @vsr1 ,

I have another case:
Query :

SELECT COUNT(*) AS total
FROM myBucket a
INNER JOIN myBucketEXT b ON a.column1 = b.column2
AND b.TABLE_NAME = ‘TABLE1’
AND b.column7 = ‘1’
AND b.column5 = ‘N’
AND b.column6 = ‘’
WHERE a.TABLE_NAME = ‘TABLE2’
AND a.column4 = ‘1’
AND a.column3 = ‘xxx’

Index:

CREATE INDEX idx-1 ON myBucket(column1,column5,column6,column7) WHERE (TABLE_NAME = ‘TABLE1’)
CREATE INDEX idx-2 ON myBuckeEXT(column2,column3,column4) WHERE (TABLE_NAME = ‘TABLE2’)

Plan Text:

{
“#operator”: “Sequence”,
“#stats”: {
“#phaseSwitches”: 1,
“execTime”: “4.312µs”
},
“~children”: [
{
“#operator”: “Authorize”,
“#stats”: {
“#phaseSwitches”: 3,
“execTime”: “70.314µs”,
“servTime”: “6.171736ms”
},
“privileges”: {
“List”: [
{
“Target”: “default:myBucket”,
“Priv”: 7
},
{
“Target”: “default:myBucketEXT”,
“Priv”: 7
}
]
},
“~child”: {
“#operator”: “Sequence”,
“#stats”: {
“#phaseSwitches”: 1,
“execTime”: “36.316µs”
},
“~children”: [
{
“#operator”: “IndexScan3”,
“#stats”: {
“#itemsOut”: 6371,
“#phaseSwitches”: 25487,
“execTime”: “128.718507ms”,
“kernTime”: “9.513134985s”,
“servTime”: “35.320478ms”
},
“as”: “a”,
“covers”: [
“cover ((a.column3))”,
“cover ((a.column4))”,
“cover ((a.column1))”,
“cover ((meta(a).id))”
],
“filter_covers”: {
“cover ((a.TABLE_NAME))”: “TABLE2”
},
“index”: “idx-2”,
“index_id”: “a0488468612eec5d”,
“keyspace”: “myBucket”,
“namespace”: “default”,
“spans”: [
{
“exact”: true,
“range”: [
{
“high”: “"xxx"”,
“inclusion”: 3,
“low”: “"xxx"”
},
{
“high”: “"1"”,
“inclusion”: 3,
“low”: “"1"”
},
{
“inclusion”: 0,
“low”: “null”
}
]
}
],
“using”: “gsi”,
“#time_normal”: “00:00.164”,
“#time_absolute”: 0.16403898499999997
},
{
“#operator”: “Sequence”,
“#stats”: {
“#phaseSwitches”: 1,
“execTime”: “5.867µs”
},
“~children”: [
{
“#operator”: “NestedLoopJoin”,
“#stats”: {
“#itemsIn”: 6371,
“#itemsOut”: 2814,
“#phaseSwitches”: 49485,
“execTime”: “134.054161ms”,
“kernTime”: “10.382196529s”
},
“alias”: “b”,
“on_clause”: “(((((cover ((a.column1)) = cover ((b.column2))) and (cover ((b.TABLE_NAME)) = "TABLE1")) and (cover ((b.column7)) = "1")) and (cover ((b.column5)) = "N")) and (cover ((b.column6)) = ""))”,
“~child”: {
“#operator”: “IndexScan3”,
“#stats”: {
“#itemsOut”: 2814,
“#phaseSwitches”: 43111,
“execTime”: “197.234975ms”,
“kernTime”: “33.704883ms”,
“servTime”: “10.053109291s”
},
“as”: “b”,
“covers”: [
“cover ((b.column5))”,
“cover ((b.column6))”,
“cover ((b.column7))”,
“cover ((b.column2))”,
“cover ((meta(b).id))”
],
“filter_covers”: {
“cover ((b.TABLE_NAME))”: “TABLE1”
},
“index”: “idx-1”,
“index_id”: “3e16cb0ea46ece90”,
“keyspace”: “myBucketEXT”,
“namespace”: “default”,
“nested_loop”: true,
“spans”: [
{
“exact”: true,
“range”: [
{
“high”: “"N"”,
“inclusion”: 3,
“low”: “"N"”
},
{
“high”: “""”,
“inclusion”: 3,
“low”: “""”
},
{
“high”: “"1"”,
“inclusion”: 3,
“low”: “"1"”
},
{
“high”: “cover ((a.column1))”,
“inclusion”: 3,
“low”: “cover ((a.column1))”
}
]
}
],
“using”: “gsi”
},
“#time_normal”: “00:00.134”,
“#time_absolute”: 0.134054161
},
{
“#operator”: “Filter”,
“#stats”: {
“#itemsIn”: 2814,
“#itemsOut”: 2814,
“#phaseSwitches”: 11259,
“execTime”: “59.68489ms”,
“kernTime”: “10.456659277s”
},
“condition”: “(((cover ((a.TABLE_NAME)) = "TABLE2") and (cover ((a.column4)) = "1")) and (cover ((a.column3)) = "xxx"))”,
“#time_normal”: “00:00.059”,
“#time_absolute”: 0.059684890000000004
},
{
“#operator”: “InitialGroup”,
“#stats”: {
“#itemsIn”: 2814,
“#itemsOut”: 1,
“#phaseSwitches”: 5633,
“execTime”: “32.642534ms”,
“kernTime”: “10.483754569s”
},
“aggregates”: [
“count()"
],
“group_keys”: [],
“#time_normal”: “00:00.032”,
“#time_absolute”: 0.032642534
}
],
“#time_normal”: “00:00.000”,
“#time_absolute”: 0.000005867
},
{
“#operator”: “IntermediateGroup”,
“#stats”: {
“#itemsIn”: 1,
“#itemsOut”: 1,
“#phaseSwitches”: 7,
“execTime”: “10.244µs”,
“kernTime”: “10.516438915s”
},
“aggregates”: [
"count()”
],
“group_keys”: ,
“#time_normal”: “00:00.000”,
“#time_absolute”: 0.000010244
},
{
“#operator”: “FinalGroup”,
“#stats”: {
“#itemsIn”: 1,
“#itemsOut”: 1,
“#phaseSwitches”: 7,
“execTime”: “16.615µs”,
“kernTime”: “10.516524892s”
},
“aggregates”: [
“count()"
],
“group_keys”: [],
“#time_normal”: “00:00.000”,
“#time_absolute”: 0.000016615
},
{
“#operator”: “Sequence”,
“#stats”: {
“#phaseSwitches”: 1,
“execTime”: “6.044µs”
},
“~children”: [
{
“#operator”: “InitialProject”,
“#stats”: {
“#itemsIn”: 1,
“#itemsOut”: 1,
“#phaseSwitches”: 8,
“execTime”: “47.428µs”,
“kernTime”: “10.516554579s”
},
“result_terms”: [
{
“as”: “totalPotentialClient”,
“expr”: "count()”
}
],
“#time_normal”: “00:00.000”,
“#time_absolute”: 0.000047428
},
{
“#operator”: “FinalProject”,
“#stats”: {
“#itemsIn”: 1,
“#itemsOut”: 1,
“#phaseSwitches”: 4,
“execTime”: “7.129µs”,
“kernTime”: “4.365µs”
},
“#time_normal”: “00:00.000”,
“#time_absolute”: 0.000007129
}
],
“#time_normal”: “00:00.000”,
“#time_absolute”: 0.000006043999999999999
}
],
“#time_normal”: “00:00.000”,
“#time_absolute”: 0.000036316
},
“#time_normal”: “00:00.006”,
“#time_absolute”: 0.00624205
},
{
“#operator”: “Stream”,
“#stats”: {
“#itemsIn”: 1,
“#itemsOut”: 1,
“#phaseSwitches”: 5,
“execTime”: “43.707µs”,
“kernTime”: “10.522966804s”
},
“#time_normal”: “00:00.000”,
“#time_absolute”: 0.000043707
}
],
“~versions”: [
“6.5.0-N1QL”,
“6.5.1-6299-enterprise”
],
“#time_normal”: “00:00.000”,
“#time_absolute”: 0.000004312
}

How to optimize this query? I have using advised index but the result still around 13s

12

You are using 6.5.1-6299-enterprise try with hash join

13

Hi @vsr1 ,

I’ve tried this :

SELECT COUNT(*) AS total
FROM myBucket a
INNER JOIN myBucketEXT USE HASH(BUILD) b ON a.column1 = b.column2
AND b.TABLE_NAME = ‘TABLE1’
AND b.column7 = ‘1’
AND b.column5 = ‘N’
AND b.column6 = ‘’
WHERE a.TABLE_NAME = ‘TABLE2’
AND a.column4 = ‘1’
AND a.column3 = ‘xxx’

but takes longer time (±50s)

14

Try switch INNER JOIN myBucketEXT USE HASH(PROBE) b
or switch join order without hash join

15

Hi @vsr1 ,

Even when I remove the join, only this :

SELECT COUNT(*) AS total
FROM myBucketEXT b
WHERE b.TABLE_NAME = ‘TABLE1’
AND b.column7 = ‘1’
AND b.column5 = ‘N’
AND b.column6 = ‘’

It takes around 4s, (FYI total data is 2mio)
Is it takes longer time to count large data?

16

Check out profile timings. increase indexer memory.
Also try partition index

17

Hi @vsr1

increase indexer memory means increase in this setting? image

anyway I also have another query using calculation and it takes longer time.
I know we can use functional index, but in my case, the variable to be calculated comes from input parameter:

AND TONUMBER(a) + (TONUMBER(b) * rateIDRToUSD) between 5000000000 AND 10000000000

Is it possible to optimize this kind of query ?

18

Yes. You already have 60GB. It depends on input parameter no further optimization is possible