You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@impala.apache.org by Vibhath Ileperuma <vi...@gmail.com> on 2021/01/29 12:58:35 UTC
Re: Impala fails to scan only the correct base table when a query is
submitted to the view.
Hi all,
According to the attached email thread, partition pruning is also not
properly happening when the value of the partition column is generated from
a function.
Is there a possible workaround for this?
Further, is it possible for us to implement the optimization of deducing
the contradictions between greater than and less than predicates. Could you
please suggest to me where we should look at, in the code if we are
implementing it and how complex it will be.
Thanks & Regards
*Vibhath Ileperuma*
On Tue, Dec 8, 2020 at 10:52 PM Tim Armstrong <ta...@cloudera.com>
wrote:
> You're seeing the effects of one optimization where scans with trivially
> false predicates or contradictory predicates are eliminated. That
> optimization can deduce contradictions between equality predicates but
> doesn't do more complex reasoning about whether ranges specified by
> inequality predicates overlap.
>
> Partition pruning is the optimization that is generally more important and
> works with a much bigger set of predicates. I don't know what your scenario
> is, but I've seen in the past that use cases with views like yours tend to
> also be partitioned on the columns where there's significant opportunity
> for data elimination.
> E.g.
>
> create view test_view as
> select * from functional.alltypes
> where year >= 2010
> union all
> select * from functional.alltypestiny
> where year <= 2009
>
> [localhost.EXAMPLE.COM:21050] functional> explain select * from test_view
> where year < 2010;
> Query: explain select * from test_view where year < 2010
>
> +---------------------------------------------------------------------------------+
> | Explain String
> |
>
> +---------------------------------------------------------------------------------+
> | Max Per-Host Resource Reservation: Memory=8.00KB Threads=2
> |
> | Per-Host Resource Estimates: Memory=16MB
> |
> | Codegen disabled by planner
> |
> |
> |
> | PLAN-ROOT SINK
> |
> | |
> |
> | 00:UNION
> |
> | | pass-through-operands: all
> |
> | | row-size=89B cardinality=8
> |
> | |
> |
> | |--02:SCAN HDFS [functional.alltypestiny]
> |
> | | partition predicates: `year` <= 2009, functional.alltypestiny.year
> < 2010 |
> | | HDFS partitions=4/4 files=4 size=460B
> |
> | | row-size=89B cardinality=8
> |
> | |
> |
> | 01:SCAN HDFS [functional.alltypes]
> |
> | partition predicates: `year` >= 2010, functional.alltypes.year < 2010
> |
> | partitions=0/24 files=0 size=0B
> |
> | row-size=89B cardinality=0
> |
>
> +---------------------------------------------------------------------------------+
>
> Note that there are 0 partitions.
>
> If the values aren't present in a particular table as well, other forms of
> data elimination at runtime can be very effective at avoiding scanning data
> - https://blog.cloudera.com/faster-performance-for-selective-queries/,
> https://blog.cloudera.com/speeding-up-select-queries-with-parquet-page-indexes/.
> Those won't show up in the plan.
>
> On Tue, Dec 8, 2020 at 7:35 AM Vibhath Ileperuma <
> vibhatharunapriya@gmail.com> wrote:
>
>> Hi Team,
>>
>>
>>
>> When I’m evaluating the execution of queries submitted to a view, I
>> noticed the following issue.
>>
>>
>>
>> *Problem:*
>>
>> Impala fails to scan only the correct base table when a query is
>> submitted to the view.
>>
>> Consider following Example
>>
>> Let’s say we create two tables and a view as follows.
>>
>> *CREATE* *TABLE* TEST_TABLE_1(
>>
>> COL1 *BIGINT*,
>>
>> COL_RANGE *BIGINT*
>>
>> )
>>
>>
>>
>> *CREATE* *TABLE* TEST_TABLE_2(
>>
>> COL1 *BIGINT*,
>>
>> COL_RANGE *BIGINT*
>>
>> )
>>
>>
>>
>> *CREATE* *VIEW* TEST_VIEW *AS*
>>
>> *SELECT* COL1, COL_RANGE *FROM* TEST_TABLE_1 *WHERE* COL_RANGE <= 5
>>
>> *UNION* *ALL*
>>
>> *SELECT* COL1, COL_RANGE *FROM* TEST_TABLE_2 *WHERE* COL_RANGE > 5
>>
>>
>>
>> If we submit a select query with only one equal condition for
>> ‘COL_RANGE’, impala scans only the correct base table.
>>
>> *SELECT* * *FROM* TEST_VIEW *WHERE* COL_RANGE = 2
>>
>> Operator #Hosts Avg Time Max Time #Rows Est. #Rows Peak
>> Mem Est. Peak Mem Detail
>>
>>
>> -------------------------------------------------------------------------------------------------------------------
>>
>> F02:ROOT 1 0.000ns
>> 0.000ns 0 0
>>
>> 03:EXCHANGE 1 0.000ns 0.000ns 1 1
>> 16.00 KB 16.00 KB UNPARTITIONED
>>
>> F01:EXCHANGE SENDER 1 0.000ns 0.000ns
>> 7.52 KB 0
>>
>> 00:UNION 1 0.000ns 0.000ns 1
>> 1 0 0
>>
>> 01:SCAN LOCAL 1 6.999ms 6.999ms 1 1
>> 49.00 KB 32.00 MB default.test_table_1
>>
>>
>>
>> *SELECT* * *FROM* TEST_VIEW *WHERE* COL_RANGE = 7
>>
>> Operator #Hosts Avg Time Max Time #Rows Est. #Rows Peak Mem Est. Peak Mem Detail
>>
>> ---------------------------------------------------------------------------------------------------------------------
>>
>> F02:ROOT 1 0.000ns 0.000ns 0 0
>>
>> 03:EXCHANGE 1 0.000ns 0.000ns 1 1 16.00 KB 16.00 KB UNPARTITIONED
>>
>> F01:EXCHANGE SENDER 1 999.987us 999.987us 7.52 KB 0
>>
>> 00:UNION 1 0.000ns 0.000ns 1 1 0 0
>>
>> 02:SCAN LOCAL 1 4.999ms 4.999ms 1 1 49.00 KB 32.00 MB default.test_table_2
>>
>>
>>
>>
>>
>> But if we submit a query with a range condition, it always scan both the
>> tables.
>>
>> *SELECT* * *FROM* TEST_VIEW *WHERE* COL_RANGE < 4
>>
>> Operator #Hosts Avg Time Max Time #Rows Est. #Rows Peak Mem Est. Peak Mem Detail
>>
>> -------------------------------------------------------------------------------------------------------------------
>>
>> F03:ROOT 1 0.000ns 0.000ns 0 0
>>
>> 03:EXCHANGE 1 0.000ns 0.000ns 2 2 16.00 KB 16.00 KB UNPARTITIONED
>>
>> F02:EXCHANGE SENDER 1 0.000ns 0.000ns 7.52 KB 0
>>
>> 00:UNION 1 0.000ns 0.000ns 2 2 0 0
>>
>> |--02:SCAN LOCAL 1 2.999ms 2.999ms 0 1 41.00 KB 32.00 MB default.test_table_2
>>
>> 01:SCAN LOCAL 1 4.999ms 4.999ms 2 1 49.00 KB 32.00 MB default.test_table_1
>>
>>
>>
>> *SELECT* * *FROM* TEST_VIEW *WHERE* COL_RANGE > 5
>>
>> Operator #Hosts Avg Time Max Time #Rows Est. #Rows Peak Mem Est. Peak Mem Detail
>>
>> -------------------------------------------------------------------------------------------------------------------
>>
>> F03:ROOT 1 0.000ns 0.000ns 0 0
>>
>> 03:EXCHANGE 1 0.000ns 0.000ns 3 2 16.00 KB 16.00 KB UNPARTITIONED
>>
>> F02:EXCHANGE SENDER 1 1.000ms 1.000ms 7.52 KB 0
>>
>> 00:UNION 1 0.000ns 0.000ns 3 2 0 0
>>
>> |--02:SCAN LOCAL 1 5.000ms 5.000ms 3 1 49.00 KB 32.00 MB default.test_table_2
>>
>> 01:SCAN LOCAL 1 7.000ms 7.000ms 0 1 41.00 KB 32.00 MB default.test_table_1
>>
>>
>>
>>
>>
>>
>>
>> I would be really grateful if you could check on this and advise how to
>> overcome this issue.
>>
>> Thanks & Regards
>>
>> *Vibhath Ileperuma*
>>
>>
>>