How to speed up querying the hive external partition table

[wangmiao1002]

There is a significant difference in execution time between the same SQL statement in the trino query and the presto query. It can be seen that the parsing process of trino is basically dismantling the SQL statement and obtaining partition information. How can I avoid this

[hashhar]

Share SHOW CREATE TABLE output.

Share hive connector configs and config.properties for both.

Also collect EXPLAIN ANALYZE output from both.

[wangmiao1002]

1.The table structure information is as follows

CREATE TABLE hive.default.applog_online ( message varchar, date date, mod varchar, logtype varchar, host varchar ) WITH ( external_location = 'hdfs://nameservice1/user/hive/warehouse/applog_online', format = 'TEXTFILE', partitioned_by = ARRAY['date','mod','logtype','host'], textfile_field_separator = ' ' )

The compressed format of the text created during this table, with gz compressed files stored below

2.Config.properties configuration information
coordinator=true
node-scheduler.include-coordinator=false
http-server.http.port=8090
query.max-memory=20GB
query.max-memory-per-node=15GB
query.max-total-memory=30GB
discovery.uri=http://dx-loghdfs12.dx:8090
optimizer.use-table-scan-node-partitioning=false
optimizer.use-cost-based-partitioning=false

hive.properties configuration information
connector.name=hive
hive.metastore.uri=thrift://10.19.40.152:9083,thrift://10.19.40.153:9083
hive.config.resources=/usr/local/trino/etc/conf/core-site.xml,/usr/local/trino/etc/conf/hdfs-site.xml
hive.metastore-timeout=300s
hive.metastore-cache-maximum-size=5000000
hive.metastore-cache-ttl=60m
hive.metastore-stats-cache-ttl=60m
hive.metastore-cache.cache-partitions=true
hive.query-partition-filter-required=true
hive.file-status-cache-tables="default.applog_online"
hive.table-statistics-enabled=false

3. EXPLAIN ANALYZE :
   `trino:default> EXPLAIN ANALYZE SELECT message FROM (select message,date from applog_online where (logtype = 'application') AND (mod = 'xinka_batch') ) WHERE (date >= date '2023-10-13') AND (date <= date '2023-10-13') AND (message LIKE '%10008226415%' AND message LIKE '%W1635506479%') LIMIT 10000;
   Q

---

Trino version: 426
Queued: 301.36us, Analysis: 7.27ms, Planning: 1.49m, Execution: 1.50m
Fragment 1 [SINGLE]
CPU: 10.38ms, Scheduled: 13.42ms, Blocked 10.79s (Input: 10.46s, Output: 0.00ns), Input: 2 rows (2.45kB); per task: avg.: 2.00 std.dev.: 0.00, Output: 2
Output layout: [message]
Output partitioning: SINGLE []
Limit[count = 10000]
│ Layout: [message:varchar]
│ Estimates: {rows: 10000 (537.11kB), cpu: 537.11k, memory: 0B, network: 0B}
│ CPU: 1.00ms (0.05%), Scheduled: 1.00ms (0.02%), Blocked: 0.00ns (0.00%), Output: 2 rows (2.45kB)
│ Input avg.: 2.00 rows, Input std.dev.: 0.00%
└─ LocalExchange[partitioning = SINGLE]
│ Layout: [message:varchar]
│ Estimates: {rows: 10000 (537.11kB), cpu: 0, memory: 0B, network: 0B}
│ CPU: 2.00ms (0.11%), Scheduled: 2.00ms (0.03%), Blocked: 309.00ms (2.87%), Output: 2 rows (2.45kB)
│ Input avg.: 0.06 rows, Input std.dev.: 556.78%
└─ RemoteSource[sourceFragmentIds = [2]]
Layout: [message:varchar]
CPU: 1.00ms (0.05%), Scheduled: 1.00ms (0.02%), Blocked: 10.46s (97.13%), Output: 2 rows (2.45kB)
Input avg.: 0.06 rows, Input std.dev.: 556.78%

Fragment 2 [SOURCE]
CPU: 1.85s, Scheduled: 6.31s, Blocked 0.00ns (Input: 0.00ns, Output: 0.00ns), Input: 92840 rows (165.83MB); per task: avg.: 30946.67 std.dev.: 12650.97,
Output layout: [message]
Output partitioning: SINGLE []
LimitPartial[count = 10000]
│ Layout: [message:varchar]
│ Estimates: {rows: 10000 (537.11kB), cpu: 537.11k, memory: 0B, network: 0B}
│ CPU: 1.00ms (0.05%), Scheduled: 1.00ms (0.02%), Blocked: 0.00ns (0.00%), Output: 2 rows (2.45kB)
│ Input avg.: 0.02 rows, Input std.dev.: 974.68%
└─ ScanFilter[table = hive:default:applog_online, filterPredicate = ("$like"("message", "$literal$"(from_base64('DgAAAFZBUklBQkxFX1dJRFRIAQAAAAEAAAASAAA
Layout: [message:varchar]
Estimates: {rows: ? (?), cpu: ?, memory: 0B, network: 0B}/{rows: ? (?), cpu: ?, memory: 0B, network: 0B}
CPU: 1.84s (99.73%), Scheduled: 6.30s (99.92%), Blocked: 0.00ns (0.00%), Output: 2 rows (2.45kB)
Input avg.: 967.08 rows, Input std.dev.: 125.23%
message := message:string:REGULAR
mod:string:PARTITION_KEY
:: [[xinka_batch]]
date:date:PARTITION_KEY
:: [[2023-10-13]]
host:string:PARTITION_KEY
:: [[hhr-xinka]]
logtype:string:PARTITION_KEY
:: [[application]]
Input: 92840 rows (165.83MB), Filtered: 100.00%, Physical input: 11.80MB, Physical input time: 521.00ms

(1 row)

Query 20231027_031814_00009_iessp, FINISHED, 3 nodes
Splits: 162 total, 162 done (100.00%)
1:30 [92.8K rows, 11.8MB] [1.03K rows/s, 134KB/s]`