Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Date: Thu, 22 Jun 2017 00:59:00 +0000 (UTC)
From: "Bryan Pendleton (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Message-ID: <JIRA.13080654.1497792917000.75682.1498093140132@Atlassian.JIRA>
In-Reply-To: <JIRA.13080654.1497792917000@Atlassian.JIRA>
References: <JIRA.13080654.1497792917000@Atlassian.JIRA> <JIRA.13080654.1497792917826@jira-lw-us.apache.org>
Subject: [jira] [Commented] (DERBY-6940) Enhance derby statistics for more
 accurate selectivity estimates.
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
archived-at: Thu, 22 Jun 2017 00:59:04 -0000


    [ https://issues.apache.org/jira/browse/DERBY-6940?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16058547#comment-16058547 ] 

Bryan Pendleton commented on DERBY-6940:
----------------------------------------

Thanks for the background information, and for the new patch. This all sounds quite promising to me.

I will download your updated patch and study it and see how it behaves in my environment.


> Enhance derby statistics for more accurate selectivity estimates.
> -----------------------------------------------------------------
>
>                 Key: DERBY-6940
>                 URL: https://issues.apache.org/jira/browse/DERBY-6940
>             Project: Derby
>          Issue Type: Sub-task
>          Components: SQL
>            Reporter: Harshvardhan Gupta
>            Assignee: Harshvardhan Gupta
>            Priority: Minor
>         Attachments: DERBY-6940_2.diff, DERBY-6940_3.diff, derby-6940.diff
>
>
> Derby should collect extra statistics during index build time, statistics refresh time which will help optimizer make more precise selectivity estimates and chose better execution paths.
> We eventually want to utilize the new statistics to make better selectivity estimates / cost estimates that will help find the best query plan. Currently Derby keeps two type of stats - the total row count and the number of unique values.
> We are initially extending the stats to include null count, the minimum value and maximum value associated with each of the columns of an index. This would be useful in selectivity estimates for operators such as [ IS NULL, <, <=, >, >= ] , all of which currently rely on hardwired selectivity estimates.


--
This message was sent by Atlassian JIRA
(v6.4.14#64029)