question Un-optimize queries are running on metastore_db that's are causing the slowness and high resource uses.. in Support Questions

Un-optimize queries are running on metastore_db that's are causing the slowness and high resource uses..

shrikantverma — Tue, 21 Apr 2026 06:30:39 GMT

This is the query which is running on metastore_db

SELECT
`A0` . `PART_NAME`
FROM
`PARTITIONS` `A0`
LEFT OUTER JOIN
`TBLS` `B0`
ON
`A0` . `TBL_ID` = `B0` . `TBL_ID`
LEFT OUTER JOIN
`DBS` `C0`
ON
`B0` . `DB_ID` = `C0` . `DB_ID`
WHERE
`C0` . `NAME` = ?
AND `C0` . `CTLG_NAME` = ?
AND `B0` . `TBL_NAME` = ?
AND `A0` . `PART_NAME` LIKE ? ESCAPE ?

how we can optimize it ? Is it system generated?
or from where it is coming at metastore_db database that is inside mysql.

SELECT
"AO"
"PART NAME' FROM 'PARTITIONS
AO' LEFT OUTER JOIN 'TBLS
"RO' ON 'AO'
• 'TBL ID'

** Note ** I am using Trino hive architecture.

Thanks in advance for your help....

Re: Un-optimize queries are running on metastore_db that's are causing the slowness and high resource uses..

DianaTorres — Tue, 21 May 2024 18:21:27 GMT

@shrikantverma Welcome to the Cloudera Community!

To help you get the best possible solution, I have tagged our Hive and Spark experts @james_jones @cravani @mszurap who may be able to assist you further.

Please keep us updated on your post, and we hope you find a satisfactory solution to your query.

Re: Un-optimize queries are running on metastore_db that's are causing the slowness and high resource uses..

mszurap — Wed, 22 May 2024 09:14:07 GMT

Hi @shrikantverma

This indeed seems to be a generated query, however based on this only it is not clear if this is coming from the Hive Metastore. You can review with your DB tools which user is submitting these queries, for example for MySQL use "show processlist" or slow query logging to capture which user is submitting these. Alternatively stop the Hive metastore (if allowed) and observe if those queries are still appearing.

As I see this query might be used by HMS while listing partitions by name in MetaStoreDirectSql.java#getPartitionsUsingProjectionAndFilterSpec().

Is this inefficient in your database? Have you checked that with an "explain <query>" or why do you want to optimize this?

The HMS database should have indexes on the affected tables (DBS, TBLS, PARTITIONS) to speed up these queries, the only scan needed is to match the partition names - which can be slower if the Hive table has too many partitions.

Best regards

Miklos

Re: Un-optimize queries are running on metastore_db that's are causing the slowness and high resource uses..

DianaTorres — Tue, 28 May 2024 03:28:37 GMT

@shrikantverma Has the reply helped resolve your issue? If so, please mark the appropriate reply as the solution, as it will make it easier for others to find the answer in the future. Thanks.

Re: Un-optimize queries are running on metastore_db that's are causing the slowness and high resource uses..

shrikantverma — Tue, 28 May 2024 03:43:00 GMT

+----+-------------+-------+------------+-------+----------------------------------+-----------------+---------+-------------+---------+----------+-------------+

| 1 | SIMPLE | C0 | NULL | const | PRIMARY,UNIQUE_DATABASE,CTLG_FK1 | UNIQUE_DATABASE | 389 | const,const | 1 | 100.00 | Using index |

| 1 | SIMPLE | B0 | NULL | const | PRIMARY,UNIQUETABLE,TBLS_N49 | UNIQUETABLE | 268 | const,const | 1 | 100.00 | Using index |

| 1 | SIMPLE | A0 | NULL | ref | PARTITIONS_N49 | PARTITIONS_N49 | 9 | const | 5555098 | 11.11 | Using where