Return-Path: X-Original-To: archive-asf-public-internal@cust-asf2.ponee.io Delivered-To: archive-asf-public-internal@cust-asf2.ponee.io Received: from cust-asf.ponee.io (cust-asf.ponee.io [163.172.22.183]) by cust-asf2.ponee.io (Postfix) with ESMTP id E2C5C200D4C for ; Thu, 16 Nov 2017 02:34:04 +0100 (CET) Received: by cust-asf.ponee.io (Postfix) id E1834160BF6; Thu, 16 Nov 2017 01:34:04 +0000 (UTC) Delivered-To: archive-asf-public@cust-asf.ponee.io Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by cust-asf.ponee.io (Postfix) with SMTP id 366B5160BF4 for ; Thu, 16 Nov 2017 02:34:04 +0100 (CET) Received: (qmail 10335 invoked by uid 500); 16 Nov 2017 01:34:03 -0000 Mailing-List: contact dev-help@drill.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@drill.apache.org Delivered-To: mailing list dev@drill.apache.org Received: (qmail 10324 invoked by uid 99); 16 Nov 2017 01:34:03 -0000 Received: from pnap-us-west-generic-nat.apache.org (HELO spamd3-us-west.apache.org) (209.188.14.142) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 16 Nov 2017 01:34:03 +0000 Received: from localhost (localhost [127.0.0.1]) by spamd3-us-west.apache.org (ASF Mail Server at spamd3-us-west.apache.org) with ESMTP id 7790A1807F9 for ; Thu, 16 Nov 2017 01:34:02 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd3-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: -100.002 X-Spam-Level: X-Spam-Status: No, score=-100.002 tagged_above=-999 required=6.31 tests=[RP_MATCHES_RCVD=-0.001, SPF_PASS=-0.001, USER_IN_WHITELIST=-100] autolearn=disabled Received: from mx1-lw-us.apache.org ([10.40.0.8]) by localhost (spamd3-us-west.apache.org [10.40.0.10]) (amavisd-new, port 10024) with ESMTP id 4ESl4RhlSbDL for ; Thu, 16 Nov 2017 01:34:01 +0000 (UTC) Received: from mailrelay1-us-west.apache.org (mailrelay1-us-west.apache.org [209.188.14.139]) by mx1-lw-us.apache.org (ASF Mail Server at mx1-lw-us.apache.org) with ESMTP id 903D75F4E5 for ; Thu, 16 Nov 2017 01:34:01 +0000 (UTC) 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 B61FBE0D65 for ; Thu, 16 Nov 2017 01:34:00 +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 3C090240D6 for ; Thu, 16 Nov 2017 01:34:00 +0000 (UTC) Date: Thu, 16 Nov 2017 01:34:00 +0000 (UTC) From: "Padma Penumarthy (JIRA)" To: dev@drill.apache.org Message-ID: In-Reply-To: References: Subject: [jira] [Created] (DRILL-5972) Slow performance for query on INFORMATION_SCHEMA.TABLE MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394 archived-at: Thu, 16 Nov 2017 01:34:05 -0000 Padma Penumarthy created DRILL-5972: --------------------------------------- Summary: Slow performance for query on INFORMATION_SCHEMA.TABLE Key: DRILL-5972 URL: https://issues.apache.org/jira/browse/DRILL-5972 Project: Apache Drill Issue Type: Bug Components: Storage - Information Schema Affects Versions: 1.11.0 Reporter: Padma Penumarthy Assignee: Padma Penumarthy Fix For: 1.13.0 A query like the following on INFORMATION_SCHEMA takes a long time to execute. select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE from INFORMATION_SCHEMA.`TABLES` WHERE TABLE_NAME LIKE '%' AND ( TABLE_SCHEMA = 'hive.default' ) ORDER BY TABLE_TYPE, TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME; Reason being we fetch table information for all schemas instead of just 'hive.default' schema. If we change the predicate like this, it executes very fast. select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE from INFORMATION_SCHEMA.`TABLES` WHERE ( TABLE_SCHEMA = 'hive.default' ) AND TABLE_NAME LIKE '%' ORDER BY TABLE_TYPE, TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME; The difference is in the order in which we evaluate the expressions in the predicate. In the first case, we first evaluate TABLE_NAME LIKE '%' and decide that it is inconclusive (since we do not know the schema). So, we go get all tables for all the schemas. In the second case, we first evaluate TABLE_SCHEMA = 'hive.default' and decide that we need to fetch only tables for that schema. -- This message was sent by Atlassian JIRA (v6.4.14#64029)