[teiid-issues] [JBoss JIRA] (TEIID-5531) Implicit indexing of common tables doesn't consider composite keys

[Steven Hawkins (Jira)]

     [ https://issues.jboss.org/browse/TEIID-5531?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Steven Hawkins resolved TEIID-5531.
-----------------------------------
    Fix Version/s: 11.2.1
       Resolution: Done


Updated the logic to look across all predicates with matching operators.

This is still a naive approach - it does not actually check ndv counts or other stats to see what columns would make for the best index.  However the performance is much improved in situations where the first predicate is against something that is not sufficiently unique.

> Implicit indexing of common tables doesn't consider composite keys
> ------------------------------------------------------------------
>
>                 Key: TEIID-5531
>                 URL: https://issues.jboss.org/browse/TEIID-5531
>             Project: Teiid
>          Issue Type: Quality Risk
>          Components: Query Engine
>            Reporter: Steven Hawkins
>            Assignee: Steven Hawkins
>            Priority: Major
>             Fix For: 12.0, 11.2.1
>
>
> Using a common table that is referenced by a common predicate will create an implicit index, but only for the first attribute - in the case of a composite key a multi-column index will only be created if an array/row value comparison is used.
> For example:
> with (x) as (...)
> select (... from x where x.col1 = y.col1 and x.col2 = y.col2) ... from y
> should add an index on x using both col1 and col2.



--
This message was sent by Atlassian Jira
(v7.12.1#712002)