[ https://issues.apache.org/jira/browse/PHOENIX-3430?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Swaroopa Kadam reassigned PHOENIX-3430: --------------------------------------- Assignee: Swaroopa Kadam > Optimizer not using all columns from secondary index > ---------------------------------------------------- > > Key: PHOENIX-3430 > URL: https://issues.apache.org/jira/browse/PHOENIX-3430 > Project: Phoenix > Issue Type: Bug > Affects Versions: 4.8.0 > Reporter: Zhenhua Xu > Assignee: Swaroopa Kadam > Priority: Major > > ================ Setup ================= > DROP TABLE IF EXISTS TEST.TEMP; > CREATE TABLE TEST.TEMP ( > ORGANIZATION_ID CHAR(15) NOT NULL, > NETWORK_ID CHAR(15) NOT NULL, > ENTITY_ID CHAR(15) NOT NULL, > SCORE DOUBLE > CONSTRAINT TOP_ENTITY_PK PRIMARY KEY ( > ORGANIZATION_ID, > NETWORK_ID, > ENTITY_ID > ) > ) VERSIONS=1; > CREATE INDEX IF NOT EXISTS TEMP_INDEX ON TEST.TEMP (ORGANIZATION_ID, > NETWORK_ID, SCORE DESC, ENTITY_ID DESC); > EXPLAIN > SELECT entity_id, MAX(score) FROM TEST.TEMP > WHERE organization_id = 'organization_id' > AND (network_id = 'network_id' OR network_id='network_id1') > AND ((score = 9.0 AND entity_id < 'entity_id') OR score < 9.0) > GROUP BY entity_id > ORDER BY MAX(score) DESC, entity_id DESC > LIMIT 100; > =============== Execution Plan =============== > -CLIENT 1-CHUNK PARALLEL 1-WAY SKIP SCAN ON 2 KEYS OVER TEST.TEMP_INDEX > ['organization_id','network_id '] - ['organization_id','network_id1 '] > --SERVER FILTER BY FIRST KEY ONLY AND ((TO_DOUBLE("SCORE") = 9.0 AND > "ENTITY_ID" < 'entity_id') OR TO_DOUBLE("SCORE") < 9.0) > --SERVER AGGREGATE INTO DISTINCT ROWS BY ["ENTITY_ID"] > -CLIENT MERGE SORT > -CLIENT TOP 100 ROWS SORTED BY [MAX(TO_DOUBLE("SCORE")) DESC, "ENTITY_ID" > DESC] > The execution plan shows a server-side skip scans using only the first 2 > columns in the TEMP_INDEX secondary index. It could have used the SCORE and > ENTITY_ID columns to speed up server side scans also. -- This message was sent by Atlassian JIRA (v7.6.3#76005)