Enhancing statistics for synced Iceberg tables

This topic gives the details to gather enhanced statistics for Iceberg tables that are synchronized with the IBM® watsonx.data metastore. It uses the EXT_METASTORE_STATS_SYNC stored procedure to collect and update statistical information within the metastore, which can improve the performance of query optimization and execution.

watsonx.data on IBM Software Hub

Before you begin

  1. Install and activate Query optimizer manager. For more information, see Activating Query Optimizer.
  2. Make sure that the manual synchronization procedure between the Query optimizer manager and the watsonx.data metastore is completed successfully if needed. See Manually syncing Query Optimizer with watsonx.data metastore.
Note: The instructions in this topic can now be executed using the enhanced feature Managing statistical updates from the Optimizer dashboard, which enables advanced query performance enhancements and optimization capabilities across multiple catalogs.

Procedure

Run the following command to get enhanced statistics for an Iceberg table that is synced: 
ExecuteWxdQueryOptimizer 'CALL EXT_METASTORE_STATS_SYNC(
  '<CATALOG_NAME>', 
  '<SCHEMA_NAME>', 
  '<TABLE_NAME>', 
  '<PRESTO_INTERNAL_HOST>', 
  '<PRESTO_USER>', 
  '<PRESTO_PWD>', 
  'true'
)';

Parameters:

  • <CATALOG_NAME>: The name of catalog (case-sensitive).

  • <SCHEMA_NAME>: The name of schema in uppercase.

  • <TABLE_NAME>: The name of table in uppercase. It is recommended to gather statistics for each table individually.

  • <PRESTO_INTERNAL_HOST>: The internal hostname of Presto engine of which the statistics is collected from. You can find the connection details of Presto engine by clicking on the engine in the Infrastructure manager page of watsonx.data.

  • <PRESTO_USER>: The Presto username that is used to run the statistics collection. The default username is admin. If IAM is enabled, the default username is <cpadmin.

  • <PRESTO_PWD>: The Presto password that is used to run the statistics collection. Password can be the CPD password, a base64 API key or token corresponding to the username.

Note: Verify the sync operation in a few minutes by following the procedure in Verifying table sync in watsonx.data.