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

Two near identical queries: one performs better because select is pushed down in one and not in the other

    XMLWordPrintable

Details

    • Bug
    • Status: Open
    • Major
    • Resolution: Unresolved
    • Morpheus
    • Morpheus
    • analytics
    • Untriaged
    • Centos 64-bit
    • 1
    • Unknown

    Description

      Here are the plans for the two queries. The plans show the queries. The differences can be seen in lines 34-37. In the second plan, the select is below the assign.

       
       
      Plan 1
       
       
      select 7, count (1) from R, S where tobigint (R.u64) /*+ productivity R 65536.0 */  = (S.u64) and R.rand <= 1024 and S.rand <= 1024
      {
      	"requestID": "591ffafb-e8e3-4f6c-96cf-4e8ddcd5b224",
      	"signature": {
      		"*": "*"
      	},
      	"results": [ {
      		"$1" : 7,
      		"$2" : 16529
      	} ]
      	,
      	"plans":{"optimizedLogicalPlan": "distribute result [$$53]
      -- DISTRIBUTE_RESULT  |UNPARTITIONED|
        exchange
        -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
          project ([$$53])
          -- STREAM_PROJECT  |UNPARTITIONED|
            assign [$$53] <- [{\"$1\": 7, \"$2\": $$60}]
            -- ASSIGN  |UNPARTITIONED|
              aggregate [$$60] <- [agg-sql-sum($$64)]
              -- AGGREGATE  |UNPARTITIONED|
                exchange
                -- RANDOM_MERGE_EXCHANGE  |PARTITIONED|
                  aggregate [$$64] <- [agg-sql-count(1)]
                  -- AGGREGATE  |PARTITIONED|
                    exchange
                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                      join (eq($$58, $$59))
                      -- HYBRID_HASH_JOIN [$$59][$$58]  |PARTITIONED|
                        exchange
                        -- HASH_PARTITION_EXCHANGE [$$59]  |PARTITIONED|
                          project ([$$59])
                          -- STREAM_PROJECT  |PARTITIONED|
                            select (le($$S.getField(\"rand\"), 1024))
                            -- STREAM_SELECT  |PARTITIONED|
                              assign [$$59] <- [$$S.getField(\"u64\")]
                              -- ASSIGN  |PARTITIONED|
                                project ([$$S])
                                -- STREAM_PROJECT  |PARTITIONED|
                                  exchange
                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                    data-scan []<-[$$56, $$S, $$57] <- Default.S
                                    -- DATASOURCE_SCAN  |PARTITIONED|
                                      exchange
                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                        empty-tuple-source
                                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
                        exchange
                        -- HASH_PARTITION_EXCHANGE [$$58]  |PARTITIONED|
                          project ([$$58])
                          -- STREAM_PROJECT  |PARTITIONED|
                            assign [$$58] <- [to-bigint($$R.getField(\"u64\"))]
                            -- ASSIGN  |PARTITIONED|
                              select (le($$R.getField(\"rand\"), 1024))
                              -- STREAM_SELECT  |PARTITIONED|
                                project ([$$R])
                                -- STREAM_PROJECT  |PARTITIONED|
                                  exchange
                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                    data-scan []<-[$$54, $$R, $$55] <- Default.R
                                    -- DATASOURCE_SCAN  |PARTITIONED|
                                      exchange
                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                        empty-tuple-source
                                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
      "},
      	"status": "success",
      	"metrics": {
      		"elapsedTime": "1.316047007s",
      		"executionTime": "1.311338763s",
      		"compileTime": "59.512719ms",
      		"resultCount": 1,
      		"resultSize": 31,
      		"processedObjects": 8388608
      	}
      }
       
      Plan 2
       
      select 67, count (1) from R, S where tobigint (R.u64) /*+ productivity R 65536.0 */ /*+ hashjoin build (R) */ = tobigint (S.u64) and R.rand <= 1024 and S.rand <= 1024
      {
      	"requestID": "e7925c8d-78a2-4ec3-bb95-90d8880aad23",
      	"signature": {
      		"*": "*"
      	},
      	"results": [ {
      		"$1" : 67,
      		"$2" : 16529
      	} ]
      	,
      	"plans":{"optimizedLogicalPlan": "distribute result [$$54]
      -- DISTRIBUTE_RESULT  |UNPARTITIONED|
        exchange
        -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
          project ([$$54])
          -- STREAM_PROJECT  |UNPARTITIONED|
            assign [$$54] <- [{\"$1\": 67, \"$2\": $$61}]
            -- ASSIGN  |UNPARTITIONED|
              aggregate [$$61] <- [agg-sql-sum($$66)]
              -- AGGREGATE  |UNPARTITIONED|
                exchange
                -- RANDOM_MERGE_EXCHANGE  |PARTITIONED|
                  aggregate [$$66] <- [agg-sql-count(1)]
                  -- AGGREGATE  |PARTITIONED|
                    exchange
                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                      join (eq($$59, $$60))
                      -- HYBRID_HASH_JOIN [$$60][$$59]  |PARTITIONED|
                        exchange
                        -- HASH_PARTITION_EXCHANGE [$$60]  |PARTITIONED|
                          project ([$$60])
                          -- STREAM_PROJECT  |PARTITIONED|
                            assign [$$60] <- [to-bigint($$S.getField(\"u64\"))]
                            -- ASSIGN  |PARTITIONED|
                              select (le($$S.getField(\"rand\"), 1024))
                              -- STREAM_SELECT  |PARTITIONED|
                                project ([$$S])
                                -- STREAM_PROJECT  |PARTITIONED|
                                  exchange
                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                    data-scan []<-[$$57, $$S, $$58] <- Default.S
                                    -- DATASOURCE_SCAN  |PARTITIONED|
                                      exchange
                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                        empty-tuple-source
                                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
                        exchange
                        -- HASH_PARTITION_EXCHANGE [$$59]  |PARTITIONED|
                          project ([$$59])
                          -- STREAM_PROJECT  |PARTITIONED|
                            assign [$$59] <- [to-bigint($$R.getField(\"u64\"))]
                            -- ASSIGN  |PARTITIONED|
                              select (le($$R.getField(\"rand\"), 1024))
                              -- STREAM_SELECT  |PARTITIONED|
                                project ([$$R])
                                -- STREAM_PROJECT  |PARTITIONED|
                                  exchange
                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                    data-scan []<-[$$55, $$R, $$56] <- Default.R
                                    -- DATASOURCE_SCAN  |PARTITIONED|
                                      exchange
                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                        empty-tuple-source
                                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
      "},
      	"status": "success",
      	"metrics": {
      		"elapsedTime": "740.649411ms",
      		"executionTime": "738.748432ms",
      		"compileTime": "24.568622ms",
      		"resultCount": 1,
      		"resultSize": 32,
      		"processedObjects": 8388608
      	}
      }
      
      

      Attachments

        1. plan1
          3 kB
        2. plan2
          3 kB
        No reviews matched the request. Check your Options in the drop-down menu of this sections header.

        Activity

          There are no comments yet on this issue.

          People

            till Till Westmann
            murali.krishna Murali Krishna
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated:

              Gerrit Reviews

                There are no open Gerrit changes

                PagerDuty