Uploaded image for project: 'Couchbase Server'
  1. Couchbase Server
  2. MB-27861

Optimize MIN aggregate without group by

    XMLWordPrintable

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • 5.5.0
    • 5.5.0
    • secondary-index
    • Triaged
    • Unknown

    Description

      For MIN aggregate without group by, N1QL sends Limit 1 queries in API2 e.g. if the aggregate is on the first column or aggregate on second column but first column has equality.

      With API3, this optimization is no longer available.

      Either:
      1. Indexer can have the extra logic to determine when such optimizations can be applied
      2. N1QL can call API2 in such cases with LIMIT 1 or API3 without pushing down group aggr.

      Attachments

        No reviews matched the request. Check your Options in the drop-down menu of this sections header.

        Activity

          Sitaram.Vemulapalli Sitaram Vemulapalli added a comment - - edited

          We use to do MIN/MAX use IndexScan2 with LIMIT 1 in following cases.
          – MIN argument needs to be constant or leading key only
          – Predicate on leading key should not include NULL/MISSING
          This is tricky to do in query engine use API2. One thing we can do is COUNT/MIN/MAX/COUNT(DISTINCT) attempt do API2 always. If more than 1 spans API2 will not be used.
          Keshav Murthy any comments.

          Sitaram.Vemulapalli Sitaram Vemulapalli added a comment - - edited We use to do MIN/MAX use IndexScan2 with LIMIT 1 in following cases. – MIN argument needs to be constant or leading key only – Predicate on leading key should not include NULL/MISSING This is tricky to do in query engine use API2. One thing we can do is COUNT/MIN/MAX/COUNT(DISTINCT) attempt do API2 always. If more than 1 spans API2 will not be used. Keshav Murthy any comments.
          Sitaram.Vemulapalli Sitaram Vemulapalli added a comment - - edited

          The rules we use to send limit one is some thing like below

          GROUP BY is NIL (length of 0)
          Number of aggregates are 1
          KeyPos 0 (leading or first non equality)
          ASC Index on Pos 0 MIN return first non-missing,non-null value is encountered scan can be stopped , if none return NULL
          KeyPos 0 (leading or first non equality)
          upto keyPos all leading keys all spans(across) must have have same lows and high , inclusive 3
          Desc Index On Pos 0 MAX return first non-missing,non-null value is encountered scan can be stopped, if none return NULL
          COUNT (DISTINCT <constant>)
          After one item is scanned scan can be stopped

          Sitaram.Vemulapalli Sitaram Vemulapalli added a comment - - edited The rules we use to send limit one is some thing like below GROUP BY is NIL (length of 0) Number of aggregates are 1 KeyPos 0 (leading or first non equality) ASC Index on Pos 0 MIN return first non-missing,non-null value is encountered scan can be stopped , if none return NULL KeyPos 0 (leading or first non equality) upto keyPos all leading keys all spans(across) must have have same lows and high , inclusive 3 Desc Index On Pos 0 MAX return first non-missing,non-null value is encountered scan can be stopped, if none return NULL COUNT (DISTINCT <constant>) After one item is scanned scan can be stopped

          Hi Sitaram Vemulapalli, COUNT (DISTINCT <const_expr> ) will always return 1, pls confirm. 

          prathibha Prathibha Bisarahalli (Inactive) added a comment - Hi Sitaram Vemulapalli , COUNT (DISTINCT <const_expr> ) will always return 1, pls confirm. 

          In that case, should COUNT (DISTINCT <const_expr> ) be pushed down, as it always results in 1 as response. 

          prathibha Prathibha Bisarahalli (Inactive) added a comment - In that case, should COUNT (DISTINCT <const_expr> ) be pushed down, as it always results in 1 as response. 

          It needs to pushdown, The response depends on what is predicate.
          It all depends on group by, query predicate qualify any rows or not.

          Sitaram.Vemulapalli Sitaram Vemulapalli added a comment - It needs to pushdown, The response depends on what is predicate. It all depends on group by, query predicate qualify any rows or not.

          Okay Thanks. 

          prathibha Prathibha Bisarahalli (Inactive) added a comment - Okay Thanks. 

          Build couchbase-server-5.5.0-2610 contains indexing commit dbd0c6daf8cef02aae8a10cc609c12a1a8dc7c37 with commit message:
          MB-27861: Optimize MIN, MAX aggregate without group by
          https://github.com/couchbase/indexing/commit/dbd0c6daf8cef02aae8a10cc609c12a1a8dc7c37

          build-team Couchbase Build Team added a comment - Build couchbase-server-5.5.0-2610 contains indexing commit dbd0c6daf8cef02aae8a10cc609c12a1a8dc7c37 with commit message: MB-27861 : Optimize MIN, MAX aggregate without group by https://github.com/couchbase/indexing/commit/dbd0c6daf8cef02aae8a10cc609c12a1a8dc7c37

          Build couchbase-server-6.0.0-1071 contains indexing commit dbd0c6daf8cef02aae8a10cc609c12a1a8dc7c37 with commit message:
          MB-27861: Optimize MIN, MAX aggregate without group by
          https://github.com/couchbase/indexing/commit/dbd0c6daf8cef02aae8a10cc609c12a1a8dc7c37

          build-team Couchbase Build Team added a comment - Build couchbase-server-6.0.0-1071 contains indexing commit dbd0c6daf8cef02aae8a10cc609c12a1a8dc7c37 with commit message: MB-27861 : Optimize MIN, MAX aggregate without group by https://github.com/couchbase/indexing/commit/dbd0c6daf8cef02aae8a10cc609c12a1a8dc7c37

          Had discussion about this with Deep and we have decided to make this optimization only for KeyPos = 0. For KeyPos > 0, we have logged a tracking bug MB-29605

          prathibha Prathibha Bisarahalli (Inactive) added a comment - - edited Had discussion about this with Deep and we have decided to make this optimization only for KeyPos = 0. For KeyPos > 0, we have logged a tracking bug MB-29605

          Deepkaran Salooja does this need QE verification? if so, can you please provide some steps?

          arunkumar Arunkumar Senthilnathan (Inactive) added a comment - Deepkaran Salooja does this need QE verification? if so, can you please provide some steps?

          This was a small perf optimization. No functional verification needed. Dev has already verified the perf.

          deepkaran.salooja Deepkaran Salooja added a comment - This was a small perf optimization. No functional verification needed. Dev has already verified the perf.

          Rules were simplified due to MB-29553 with commit af8b9cd76f7561ac30a6272eef7e91ac240291f4

          prathibha Prathibha Bisarahalli (Inactive) added a comment - Rules were simplified due to MB-29553 with commit af8b9cd76f7561ac30a6272eef7e91ac240291f4

          Sitaram Vemulapalli I need a clarification about below rules:

          GROUP BY is NIL (length of 0)
          Number of aggregates are 1
          KeyPos 0 (leading or first non equality)
          ASC Index on Pos 0 MIN return first non-missing,non-null value is encountered scan can be stopped , if none return NULL
          KeyPos 0 (leading or first non equality)
          upto keyPos all leading keys all spans(across) must have have same lows and high , inclusive 3
          Desc Index On Pos 0 MAX return first non-missing,non-null value is encountered scan can be stopped, if none return NULL
          COUNT (DISTINCT <constant>)
          After one item is scanned scan can be stopped
          

          if MIN Aggr is on KeyPos > 0 , for example, KeyPos = 3 and first three positions are: ASC, DESC< ASC, then do the rules still apply given that scan is equality low=high, incl 3 for first three positions (0, 1, 2) ?

          prathibha Prathibha Bisarahalli (Inactive) added a comment - Sitaram Vemulapalli I need a clarification about below rules: GROUP BY is NIL (length of 0) Number of aggregates are 1 KeyPos 0 (leading or first non equality) ASC Index on Pos 0 MIN return first non-missing,non-null value is encountered scan can be stopped , if none return NULL KeyPos 0 (leading or first non equality) upto keyPos all leading keys all spans(across) must have have same lows and high , inclusive 3 Desc Index On Pos 0 MAX return first non-missing,non-null value is encountered scan can be stopped, if none return NULL COUNT (DISTINCT <constant>) After one item is scanned scan can be stopped if MIN Aggr is on KeyPos > 0 , for example, KeyPos = 3 and first three positions are: ASC, DESC< ASC, then do the rules still apply given that scan is equality low=high, incl 3 for first three positions (0, 1, 2) ?

          if MIN Aggr is on KeyPos > 0 , for example, KeyPos = 3 and first three positions are: ASC, DESC< ASC, then do the rules still apply given that scan is equality low=high, incl 3 for first three positions (0, 1, 2) ?
          keyPos=3 and first there is equal (first there asc,desc,asc doesn't matter because of equal) but 4th asc/desc matters. asc first, desc last value

          Sitaram.Vemulapalli Sitaram Vemulapalli added a comment - if MIN Aggr is on KeyPos > 0 , for example, KeyPos = 3 and first three positions are: ASC, DESC< ASC, then do the rules still apply given that scan is equality low=high, incl 3 for first three positions (0, 1, 2) ? keyPos=3 and first there is equal (first there asc,desc,asc doesn't matter because of equal) but 4th asc/desc matters. asc first, desc last value

          People

            korrigan.clark Korrigan Clark (Inactive)
            deepkaran.salooja Deepkaran Salooja
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Gerrit Reviews

                There are no open Gerrit changes

                PagerDuty