Calculation of "constants"

mattschutz — Wed, 28 Jul 2021 15:10:03 GMT

I've read query plans in SQL Server forever using a GUI, but text plans are like Greek to me (I don't speak Greek). I'm trying to figure out if statements like this calculate the date "constant" for each row and cause performance lag, or if Impala knows to calculate it once and apply to all rows.

select * from myTable -- the "months_add" is basically a constant, but I can see -- why a function might be calculated for each row where tran_timestamp > months_add(now(), -1) limit 1; select * from myTable -- but what about this? this is definitely a constant -- so is this different than the query above and is -- the "constant" calculated for each row? where tran_timestamp > months_add('2021-01-01', -1) limit 1

Re: Calculation of "constants"

robbiez — Fri, 30 Jul 2021 07:21:22 GMT

The calculations on constants should be processed in the compiling phase. If you have a look at the query plans as below, you can see that both "months_add(now(), -1)" and "months_add('2020-01-01', -1)" are evaluated already. Impala doesn't need to repeat evaluation for each row.

[localhost:21000] default> explain select * from mytable where tran_timestamp > months_add(now(), -1);
Query: explain select * from mytable where tran_timestamp > months_add(now(), -1)
+------------------------------------------------------------------------------------+
| Explain String                                                                     |
+------------------------------------------------------------------------------------+
| Max Per-Host Resource Reservation: Memory=0B Threads=2                             |
| Per-Host Resource Estimates: Memory=10MB                                           |
| WARNING: The following tables are missing relevant table and/or column statistics. |
| default.mytable                                                                    |
|                                                                                    |
| PLAN-ROOT SINK                                                                     |
| |                                                                                  |
| 01:EXCHANGE [UNPARTITIONED]                                                        |
| |                                                                                  |
| 00:SCAN HDFS [default.mytable]                                                     |
|    HDFS partitions=1/1 files=0 size=0B                                             |
|    predicates: tran_timestamp > TIMESTAMP '2021-06-30 17:08:33.432148000'          |
|    row-size=16B cardinality=0                                                      |
+------------------------------------------------------------------------------------+
Fetched 13 row(s) in 0.99s
[localhost:21000] default> explain select * from mytable where tran_timestamp > months_add('2020-01-01', -1);
Query: explain select * from mytable where tran_timestamp > months_add('2020-01-01', -1)
+------------------------------------------------------------------------------------+
| Explain String                                                                     |
+------------------------------------------------------------------------------------+
| Max Per-Host Resource Reservation: Memory=0B Threads=2                             |
| Per-Host Resource Estimates: Memory=10MB                                           |
| WARNING: The following tables are missing relevant table and/or column statistics. |
| default.mytable                                                                    |
|                                                                                    |
| PLAN-ROOT SINK                                                                     |
| |                                                                                  |
| 01:EXCHANGE [UNPARTITIONED]                                                        |
| |                                                                                  |
| 00:SCAN HDFS [default.mytable]                                                     |
|    HDFS partitions=1/1 files=0 size=0B                                             |
|    predicates: tran_timestamp > TIMESTAMP '2019-12-01 00:00:00'                    |
|    row-size=16B cardinality=0                                                      |
+------------------------------------------------------------------------------------+
Fetched 13 row(s) in 0.02s

Re: Calculation of "constants"

mattschutz — Fri, 30 Jul 2021 12:20:32 GMT

EXACTLY what I needed. Thank you @robbiez !

question Calculation of "constants" in Support Questions

Calculation of "constants"

Re: Calculation of "constants"

Re: Calculation of "constants"