From issues-return-6482-archive-asf-public=cust-asf.ponee.io@phoenix.apache.org Thu May 2 02:56:03 2019 Return-Path: X-Original-To: archive-asf-public@cust-asf.ponee.io Delivered-To: archive-asf-public@cust-asf.ponee.io Received: from mail.apache.org (hermes.apache.org [207.244.88.153]) by mx-eu-01.ponee.io (Postfix) with SMTP id D7868180629 for ; Thu, 2 May 2019 04:56:02 +0200 (CEST) Received: (qmail 27191 invoked by uid 500); 2 May 2019 02:56:01 -0000 Mailing-List: contact issues-help@phoenix.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@phoenix.apache.org Delivered-To: mailing list issues@phoenix.apache.org Received: (qmail 27182 invoked by uid 99); 2 May 2019 02:56:01 -0000 Received: from mailrelay1-us-west.apache.org (HELO mailrelay1-us-west.apache.org) (209.188.14.139) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 02 May 2019 02:56:01 +0000 Received: from jira-lw-us.apache.org (unknown [207.244.88.139]) by mailrelay1-us-west.apache.org (ASF Mail Server at mailrelay1-us-west.apache.org) with ESMTP id 41649E0104 for ; Thu, 2 May 2019 02:56:01 +0000 (UTC) Received: from jira-lw-us.apache.org (localhost [127.0.0.1]) by jira-lw-us.apache.org (ASF Mail Server at jira-lw-us.apache.org) with ESMTP id C406C20FEB for ; Thu, 2 May 2019 02:56:00 +0000 (UTC) Date: Thu, 2 May 2019 02:56:00 +0000 (UTC) From: "Lars Hofhansl (JIRA)" To: issues@phoenix.apache.org Message-ID: In-Reply-To: References: Subject: [jira] [Commented] (PHOENIX-3430) Optimizer not using all columns from secondary index MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394 [ https://issues.apache.org/jira/browse/PHOENIX-3430?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16831396#comment-16831396 ] Lars Hofhansl commented on PHOENIX-3430: ---------------------------------------- It would scan along the index to evaluate the ((TO_DOUBLE("SCORE") = 9.0 AND "ENTITY_ID" < 'entity_id') OR TO_DOUBLE("SCORE") < 9.0) part, it's just can't do a skip scan because of the < comparison. > 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)