Uploaded image for project: 'Phoenix'
  1. Phoenix
  2. PHOENIX-1645

Wrong execution plan generated for indexed query which leads to slow performance

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Invalid
    • 4.3.0
    • 4.3.0
    • None
    • None

    Description

      Query: select /*+ INDEX(INDEXED_TABLE idx1 idx2 idx3 idx4) */ count(core) from INDEXED_TABLE where core < 10 and db < 200

      Optimal explain plan generated in Phoenix v4.2: 1-CHUNK PARALLEL 1-WAY RANGE SCAN OVER IDX4 [*] - [10]
      SERVER FILTER BY TO_LONG(DB) < 200
      SERVER AGGREGATE INTO SINGLE ROW

      Wrong plan generated in 4.3 that uses skip scan join to base table. Performance of this plan compared to v4.2 is close to 20X slower with 2M rows data: CLIENT 28-CHUNK PARALLEL 1-WAY FULL SCAN OVER INDEXED_TABLE
      SERVER FILTER BY USAGE.DB < 200
      SERVER AGGREGATE INTO SINGLE ROW
      SKIP-SCAN-JOIN TABLE 0
      CLIENT 1-CHUNK PARALLEL 1-WAY RANGE SCAN OVER IDX1 [*] - [10]
      SERVER FILTER BY FIRST KEY ONLY
      DYNAMIC SERVER FILTER BY ("HOST", "DOMAIN", "FEATURE", "DATE") IN (($22.$24, $22.$25, $22.$26, $22.$27))

      DDL: CREATE TABLE $TABLE (HOST CHAR(2) NOT NULL,DOMAIN VARCHAR NOT NULL,FEATURE VARCHAR NOT NULL,DATE DATE NOT NULL,USAGE.CORE BIGINT,USAGE.DB BIGINT,STATS.ACTIVE_VISITOR INTEGER CONSTRAINT PK PRIMARY KEY (HOST, DOMAIN, FEATURE, DATE)) IMMUTABLE_ROWS=true,MAX_FILESIZE=30485760;CREATE INDEX idx1 ON $TABLE (CORE);CREATE INDEX idx2 ON $TABLE (DB);CREATE INDEX idx3 ON $TABLE (DB,ACTIVE_VISITOR);CREATE INDEX idx4 ON $TABLE (CORE,DB,ACTIVE_VISITOR);CREATE INDEX ids1 ON $TABLE (CORE) SALT_BUCKETS=16;CREATE INDEX ids2 ON $TABLE (DB) SALT_BUCKETS=16;CREATE INDEX ids3 ON $TABLE (DB,ACTIVE_VISITOR) SALT_BUCKETS=16;CREATE INDEX ids4 ON $TABLE (CORE,DB,ACTIVE_VISITOR) SALT_BUCKETS=16;

      Also see perf. run at: http://phoenix-bin.github.io/client/performance/phoenix-20150206042353.htm

      Attachments

        Activity

          People

            jamestaylor James R. Taylor
            mujtabachohan Mujtaba Chohan
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: