Need to tune a N1QL

Hemanth_Shekar · July 31, 2024, 9:30pm

I have the below N1QL Query

SELECT product,
       COUNT(DISTINCT p.uuid) totalRequestedCount
FROM `api_external` AS p
    JOIN `api_external` AS i ON p.uuid = i.data.partnerAppId LEFT
UNNEST OBJECT_PAIRS(i.data.productStatus) AS ps
LET product = IFMISSINGORNULL(i.data.productId, ps.name),
    status = IFMISSINGORNULL(i.data.status, ps.val)
WHERE p.type = 'partnerApp'
    AND i.type = 'integration'
    AND status = 'REQUESTED'
    AND i.data.env='<ENV>'
    AND p.deleted = FALSE
    AND i.deleted = FALSE
   AND product in ["a68d99bbd45c490dbe006d1ce2831ca3","e60f281f832547059f35af3d45f7efa0"] 
    AND p.data.companyId NOT IN ['testCompanyid1', 'testCompanyid2']
	AND  ( IS_NULL (i.backupOf) OR i.backupOf="")
	AND  ( IS_NULL (p.backupOf) OR p.backupOf="")
	group by product

This query takes around 5 secs to execute

The index advisor tells this query is using optimal index

CREATE INDEX apiExt_prtrApp_temp ON `api_external`(`type`,(`data`.`companyId`),`uuid`,(`data`.`appName`),(`data`.`description`),(`data`.`fourthParty`),`deleted`,(`data`.`additionalAdmins`),(`data`.`additionalDevelopers`),`backupOf`) WHERE (`type` = 'partnerApp')
CREATE INDEX temp_ix2 ON `api_external`(`deleted`,`backupOf`,(`data`.`partnerAppId`),(`data`.`env`),(`data`.`productStatus`),`updatedTs`,`createdTs`,(`data`.`productId`),(`data`.`status`)) WHERE (`type` = 'integration')

is there any way to make this N1ql run faster. We are looking at ms response hopefully with the same indexes

dh · August 5, 2024, 11:26am

Your “IS_NULL(…) OR …” clauses can be rewritten as, e.g. : IFNULL(i.backupOf,"") = "" but this isn’t going to make much difference.

You haven’t stated the size of your bucket nor the number of results you get: 5 seconds could be fantastic if billions and millions.

What does profiling show you is the major time consumer? - ideally post the profile as this’ll include counts at each operator.

Hemanth_Shekar · August 20, 2024, 1:50am

Hi @dh
please find below the profiling results.

{
  "#operator": "Authorize",
  "#stats": {
    "#phaseSwitches": 4,
    "execTime": "1.399µs",
    "servTime": "14.297µs"
  },
  "privileges": {
    "List": [
      {
        "Target": "default:api_external",
        "Priv": 7,
        "Props": 0
      }
    ]
  },
  "~child": {
    "#operator": "Sequence",
    "#stats": {
      "#phaseSwitches": 2,
      "execTime": "2.93µs"
    },
    "~children": [
      {
        "#operator": "IndexScan3",
        "#stats": {
          "#heartbeatYields": 1,
          "#itemsOut": 3026,
          "#phaseSwitches": 12107,
          "execTime": "56.532396ms",
          "kernTime": "3.61118285s",
          "servTime": "10.036212ms"
        },
        "as": "p",
        "covers": [
          "cover ((`p`.`type`))",
          "cover (((`p`.`data`).`companyId`))",
          "cover ((`p`.`uuid`))",
          "cover (((`p`.`data`).`appName`))",
          "cover (((`p`.`data`).`description`))",
          "cover (((`p`.`data`).`fourthParty`))",
          "cover ((`p`.`deleted`))",
          "cover (((`p`.`data`).`additionalAdmins`))",
          "cover (((`p`.`data`).`additionalDevelopers`))",
          "cover ((`p`.`backupOf`))",
          "cover ((meta(`p`).`id`))"
        ],
        "filter": "((cover ((`p`.`type`)) = \"partnerApp\") and (cover ((`p`.`deleted`)) = false) and (not (cover (((`p`.`data`).`companyId`)) in [\"testCompanyid1\", \"testCompanyid2\"])) and ((cover (((`p`.`data`).`companyId`)) < \"testCompanyid2\") or (\"testCompanyid2\" < cover (((`p`.`data`).`companyId`)))) and (cover ((`p`.`backupOf`)) is null))",
        "filter_covers": {
          "cover ((`p`.`type`))": "partnerApp"
        },
        "index": "apiExt_prtrApp_type",
        "index_id": "771601742488e452",
        "index_projection": {
          "entry_keys": [
            0,
            1,
            2,
            6,
            9
          ]
        },
        "keyspace": "api_external",
        "namespace": "default",
        "spans": [
          {
            "exact": true,
            "range": [
              {
                "high": "\"partnerApp\"",
                "inclusion": 3,
                "index_key": "`type`",
                "low": "\"partnerApp\""
              },
              {
                "high": "\"testCompanyid1\"",
                "inclusion": 0,
                "index_key": "(`data`.`companyId`)",
                "low": "null"
              },
              {
                "inclusion": 0,
                "index_key": "`uuid`"
              },
              {
                "inclusion": 0,
                "index_key": "(`data`.`appName`)"
              },
              {
                "inclusion": 0,
                "index_key": "(`data`.`description`)"
              },
              {
                "inclusion": 0,
                "index_key": "(`data`.`fourthParty`)"
              },
              {
                "high": "false",
                "inclusion": 3,
                "index_key": "`deleted`",
                "low": "false"
              },
              {
                "inclusion": 0,
                "index_key": "(`data`.`additionalAdmins`)"
              },
              {
                "inclusion": 0,
                "index_key": "(`data`.`additionalDevelopers`)"
              },
              {
                "high": "null",
                "inclusion": 3,
                "index_key": "`backupOf`",
                "low": "null"
              }
            ]
          },
          {
            "exact": true,
            "range": [
              {
                "high": "\"partnerApp\"",
                "inclusion": 3,
                "index_key": "`type`",
                "low": "\"partnerApp\""
              },
              {
                "high": "\"testCompanyid2\"",
                "inclusion": 0,
                "index_key": "(`data`.`companyId`)",
                "low": "\"testCompanyid1\""
              },
              {
                "inclusion": 0,
                "index_key": "`uuid`"
              },
              {
                "inclusion": 0,
                "index_key": "(`data`.`appName`)"
              },
              {
                "inclusion": 0,
                "index_key": "(`data`.`description`)"
              },
              {
                "inclusion": 0,
                "index_key": "(`data`.`fourthParty`)"
              },
              {
                "high": "false",
                "inclusion": 3,
                "index_key": "`deleted`",
                "low": "false"
              },
              {
                "inclusion": 0,
                "index_key": "(`data`.`additionalAdmins`)"
              },
              {
                "inclusion": 0,
                "index_key": "(`data`.`additionalDevelopers`)"
              },
              {
                "high": "null",
                "inclusion": 3,
                "index_key": "`backupOf`",
                "low": "null"
              }
            ]
          },
          {
            "exact": true,
            "range": [
              {
                "high": "\"partnerApp\"",
                "inclusion": 3,
                "index_key": "`type`",
                "low": "\"partnerApp\""
              },
              {
                "inclusion": 0,
                "index_key": "(`data`.`companyId`)",
                "low": "\"testCompanyid2\""
              },
              {
                "inclusion": 0,
                "index_key": "`uuid`"
              },
              {
                "inclusion": 0,
                "index_key": "(`data`.`appName`)"
              },
              {
                "inclusion": 0,
                "index_key": "(`data`.`description`)"
              },
              {
                "inclusion": 0,
                "index_key": "(`data`.`fourthParty`)"
              },
              {
                "high": "false",
                "inclusion": 3,
                "index_key": "`deleted`",
                "low": "false"
              },
              {
                "inclusion": 0,
                "index_key": "(`data`.`additionalAdmins`)"
              },
              {
                "inclusion": 0,
                "index_key": "(`data`.`additionalDevelopers`)"
              },
              {
                "high": "null",
                "inclusion": 3,
                "index_key": "`backupOf`",
                "low": "null"
              }
            ]
          }
        ],
        "using": "gsi",
        "#time_normal": "00:00.066",
        "#time_absolute": 0.066568608
      },
      {
        "#operator": "Sequence",
        "#stats": {
          "#phaseSwitches": 1,
          "execTime": "1.543µs"
        },
        "~children": [
          {
            "#operator": "NestedLoopJoin",
            "#stats": {
              "#itemsIn": 3026,
              "#itemsOut": 2443,
              "#phaseSwitches": 27932,
              "execTime": "51.492505ms",
              "kernTime": "4.423013795s"
            },
            "alias": "i",
            "on_clause": "(cover ((`p`.`uuid`)) = cover (((`i`.`data`).`partnerAppId`)))",
            "~child": {
              "#operator": "IndexScan3",
              "#stats": {
                "#itemsOut": 2443,
                "#phaseSwitches": 24902,
                "execTime": "90.663302ms",
                "kernTime": "12.178049ms",
                "servTime": "4.279160339s"
              },
              "as": "i",
              "covers": [
                "cover ((`i`.`type`))",
                "cover (((`i`.`data`).`partnerAppId`))",
                "cover (((`i`.`data`).`env`))",
                "cover (((`i`.`data`).`productStatus`))",
                "cover ((`i`.`updatedTs`))",
                "cover ((`i`.`createdTs`))",
                "cover (((`i`.`data`).`productId`))",
                "cover (((`i`.`data`).`status`))",
                "cover ((`i`.`deleted`))",
                "cover ((`i`.`backupOf`))",
                "cover ((meta(`i`).`id`))"
              ],
              "filter": "((cover ((`i`.`type`)) = \"integration\") and (cover (((`i`.`data`).`env`)) = \"SANDBOX\") and (cover ((`i`.`deleted`)) = false) and (cover ((`i`.`backupOf`)) is null))",
              "filter_covers": {
                "cover ((`i`.`type`))": "integration"
              },
              "index": "apiExt_intg_type",
              "index_id": "6c452582111482e8",
              "index_projection": {
                "entry_keys": [
                  0,
                  1,
                  2,
                  3,
                  6,
                  7,
                  8,
                  9
                ]
              },
              "keyspace": "api_external",
              "namespace": "default",
              "nested_loop": true,
              "spans": [
                {
                  "exact": true,
                  "range": [
                    {
                      "high": "\"integration\"",
                      "inclusion": 3,
                      "index_key": "`type`",
                      "low": "\"integration\""
                    },
                    {
                      "high": "cover ((`p`.`uuid`))",
                      "inclusion": 3,
                      "index_key": "(`data`.`partnerAppId`)",
                      "low": "cover ((`p`.`uuid`))"
                    },
                    {
                      "high": "\"SANDBOX\"",
                      "inclusion": 3,
                      "index_key": "(`data`.`env`)",
                      "low": "\"SANDBOX\""
                    },
                    {
                      "inclusion": 0,
                      "index_key": "(`data`.`productStatus`)"
                    },
                    {
                      "inclusion": 0,
                      "index_key": "`updatedTs`"
                    },
                    {
                      "inclusion": 0,
                      "index_key": "`createdTs`"
                    },
                    {
                      "inclusion": 0,
                      "index_key": "(`data`.`productId`)"
                    },
                    {
                      "inclusion": 0,
                      "index_key": "(`data`.`status`)"
                    },
                    {
                      "high": "false",
                      "inclusion": 3,
                      "index_key": "`deleted`",
                      "low": "false"
                    },
                    {
                      "high": "null",
                      "inclusion": 3,
                      "index_key": "`backupOf`",
                      "low": "null"
                    }
                  ]
                }
              ],
              "using": "gsi",
              "#time_normal": "00:04.369",
              "#time_absolute": 4.369823641
            },
            "#time_normal": "00:00.051",
            "#time_absolute": 0.051492505
          },
          {
            "#operator": "Unnest",
            "#stats": {
              "#itemsIn": 2443,
              "#itemsOut": 3496,
              "#phaseSwitches": 11882,
              "execTime": "42.523221ms",
              "kernTime": "4.432003037s"
            },
            "as": "ps",
            "expr": "object_pairs(cover (((`i`.`data`).`productStatus`)))",
            "outer": true,
            "#time_normal": "00:00.042",
            "#time_absolute": 0.042523221
          }
        ],
        "#time_normal": "00:00.000",
        "#time_absolute": 0.0000015429999999999999
      },
      {
        "#operator": "Sequence",
        "#stats": {
          "#phaseSwitches": 1,
          "execTime": "2.03µs"
        },
        "~children": [
          {
            "#operator": "Let",
            "#stats": {
              "#itemsIn": 3496,
              "#itemsOut": 3496,
              "#phaseSwitches": 13988,
              "execTime": "20.014698ms",
              "kernTime": "4.454534597s"
            },
            "bindings": [
              {
                "expr": "ifmissingornull(cover (((`i`.`data`).`productId`)), (`ps`.`name`))",
                "var": "product"
              },
              {
                "expr": "ifmissingornull(cover (((`i`.`data`).`status`)), (`ps`.`val`))",
                "var": "status"
              }
            ],
            "#time_normal": "00:00.020",
            "#time_absolute": 0.020014698
          },
          {
            "#operator": "Filter",
            "#stats": {
              "#itemsIn": 3496,
              "#itemsOut": 566,
              "#phaseSwitches": 8128,
              "execTime": "14.897275ms",
              "kernTime": "4.459677788s"
            },
            "condition": "((`status` = \"APPROVED\") and (`product` in [\"30040f71c95a474d8ec7e3804c5358dc\"]))",
            "#time_normal": "00:00.014",
            "#time_absolute": 0.014897275
          },
          {
            "#operator": "InitialGroup",
            "#stats": {
              "#itemsIn": 566,
              "#itemsOut": 1,
              "#phaseSwitches": 1138,
              "execTime": "13.844479ms",
              "kernTime": "4.46075606s"
            },
            "aggregates": [
              "count(DISTINCT cover (((`p`.`data`).`companyId`)))"
            ],
            "group_keys": [
              "`product`"
            ],
            "#time_normal": "00:00.013",
            "#time_absolute": 0.013844479
          }
        ],
        "#time_normal": "00:00.000",
        "#time_absolute": 0.0000020299999999999996
      },
      {
        "#operator": "IntermediateGroup",
        "#stats": {
          "#itemsIn": 1,
          "#itemsOut": 1,
          "#phaseSwitches": 8,
          "execTime": "27.481µs",
          "kernTime": "4.474623759s"
        },
        "aggregates": [
          "count(DISTINCT cover (((`p`.`data`).`companyId`)))"
        ],
        "group_keys": [
          "`product`"
        ],
        "#time_normal": "00:00.000",
        "#time_absolute": 0.000027481
      },
      {
        "#operator": "FinalGroup",
        "#stats": {
          "#itemsIn": 1,
          "#itemsOut": 1,
          "#phaseSwitches": 8,
          "execTime": "24.008µs",
          "kernTime": "4.474671944s"
        },
        "aggregates": [
          "count(DISTINCT cover (((`p`.`data`).`companyId`)))"
        ],
        "group_keys": [
          "`product`"
        ],
        "#time_normal": "00:00.000",
        "#time_absolute": 0.000024007999999999998
      },
      {
        "#operator": "InitialProject",
        "#stats": {
          "#itemsIn": 1,
          "#itemsOut": 1,
          "#phaseSwitches": 8,
          "execTime": "1.439592ms",
          "kernTime": "4.474705431s",
          "state": "running"
        },
        "discard_original": true,
        "result_terms": [
          {
            "expr": "`product`"
          },
          {
            "as": "totalRequestedCount",
            "expr": "count(DISTINCT cover (((`p`.`data`).`companyId`)))"
          }
        ],
        "#time_normal": "00:00.001",
        "#time_absolute": 0.001439592
      },
      {
        "#operator": "Stream",
        "#stats": {
          "#itemsIn": 1,
          "#itemsOut": 1,
          "#phaseSwitches": 2,
          "execTime": "20.477µs"
        },
        "#time_normal": "00:00.000",
        "#time_absolute": 0.000020477
      }
    ],
    "#time_normal": "00:00.000",
    "#time_absolute": 0.0000029300000000000003
  },
  "~versions": [
    "7.2.2-N1QL",
    "7.2.2-6401-enterprise"
  ],
  "#time_normal": "00:00.000",
  "#time_absolute": 0.000015696
}

Hemanth_Shekar · August 20, 2024, 1:53am

@dh - There is not much data. The complete bucket doesnt have more than 70 K documents. The disk space is 120 MB

dh · August 20, 2024, 8:11am

I’m unsure there will be much of a better plan for this. The nested loop join is only taking ~1.5 ms for the index scan per outer - it is not fully qualifying all index keys so must be doing some index scanning. Since the majority match it wouldn’t be beneficial to have a narrower index and just fetch matches (this may be a viable strategy if the number of matches was small compared to the number of iterations).

I am not convinced a hash join would perform any better, however you could try it and see (since you’re using Enterprise Edition); add the directive here:

 JOIN `api_external` AS i USE HASH(BUILD) ON p.uuid = i.data.partnerAppId

(Since the bucket isn’t too large I wouldn’t expect an excessive size to the hash table - so increase in memory footprint.)

Since you’re on EE, have you considered using CBO and up-to-date statistics to see what choice can be made via costing?

vsr1 · August 20, 2024, 9:21pm

When not parameterized (i.e constants) NOT IN DNF transformed to multiple ORs and uses three spans. This can perfrom well some cases ex: testCompanyid1 or testCompanyid2 values are very high. IndexScan will take care of those. Only issue is NOT IN has thousands .

Where as non parameterized can’t alter plan it will use single span.

mreiche · August 20, 2024, 9:48pm

Thanks for answering my (now deleted) question. I removed it because I thought it might not be relevant to optimizing the user’s query.

Hemanth_Shekar · August 29, 2024, 9:08pm

wowwww that changed the performance from 5 secs to under 200 ms

househippo · October 11, 2024, 8:03am

@Hemanth_Shekar …

The index from the above are really not optimized.

With your new updated query with the hash join

SELECT product, COUNT(DISTINCT p.uuid) totalRequestedCount FROM api_externalAS p JOINapi_external AS i USE HASH(BUILD) ON p.uuid = i.data.partnerAppId LEFT UNNEST OBJECT_PAIRS(i.data.productStatus) AS ps LET product = IFMISSINGORNULL(i.data.productId, ps.name), status = IFMISSINGORNULL(i.data.status, ps.val) WHERE p.type = 'partnerApp' AND i.type = 'integration' AND status = 'REQUESTED' AND i.data.env='<ENV>' AND p.deleted = FALSE AND i.deleted = FALSE AND product in ["a68d99bbd45c490dbe006d1ce2831ca3","e60f281f832547059f35af3d45f7efa0"] AND p.data.companyId NOT IN ['testCompanyid1', 'testCompanyid2'] AND ( IS_NULL (i.backupOf) OR i.backupOf="") AND ( IS_NULL (p.backupOf) OR p.backupOf="") group by product

I think these would be better indexes

CREATE INDEX apiExt_prtrApp_temp_v2 ON api_external(data.companyId,deleted,backupOf,uuid) WHERE type = ‘partnerApp’ AND (IS_NULL(backupOf) OR backupOf = “”) AND deleted = FALSE

CREATE INDEX temp_ix2_v2 ON api_external((data.env),deleted,backupOf,(data.partnerAppId),(data.productStatus),updatedTs,createdTs,(data.productId),(data.status)) WHERE type = ‘integration’ AND (IS_NULL(backupOf) OR backupOf = “”) AND data.env = '<ENV>'

system · January 9, 2025, 8:04am

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

Topic		Replies	Views
We have been working on a N1QL. need help in changing it so that we can achieve the requirement SQL++ query , n1ql	21	812	July 13, 2023
Can You suggest the optimized indexes for the below N1QL SQL++ query , n1ql	3	222	July 2, 2024
Tuning a query in N1ql SQL++	1	707	May 10, 2018
N1QL performance covered index SQL++ index	5	475	November 2, 2023
Help creating n1ql index SQL++ query , n1ql	5	1259	August 22, 2017

Need to tune a N1QL

Related topics