Skip to content

Latest commit

 

History

History
138 lines (85 loc) · 6.01 KB

cte-materialization.rst

File metadata and controls

138 lines (85 loc) · 6.01 KB
Raw

CTE Materialization

Common Table Expressions (CTEs) are subqueries that appear in a WITH clause provided by the user. Their repeated usage in a query can lead to redundant computations, excessive data retrieval, and high resource consumption.

To address this, Presto supports CTE Materialization allowing intermediate CTEs to be reused within the scope of the same query. Materializing CTEs can improve performance when the same CTE is used multiple times in a query by reducing recomputation of the CTE. However, there is also a cost to writing to and reading from disk, so the optimization may not be beneficial for very simple CTEs or CTEs that are not used many times in a query.

Materialized CTEs are stored in temporary tables that are bucketed based on random hashing. To use this feature, the connector used by the query must support the creation of temporary tables. Currently, only the :doc:`/connector/hive` offers this capability. The QueryStats (com.facebook.presto.spi.eventlistener.QueryStatistics#writtenIntermediateBytes) expose a metric to the event listener to monitor the bytes written to intermediate storage by temporary tables.

How to use CTE Materialization

The following configurations and session properties enable CTE materialization and modify its settings.

cte-materialization-strategy

  • Type: string
  • Allowed values: ALL, NONE, HEURISTIC, HEURISTIC_COMPLEX_QUERIES_ONLY
  • Default value: NONE

Specifies the strategy for materializing Common Table Expressions (CTEs) in queries.

NONE - no CTEs will be materialized.

ALL - all CTEs in the query will be materialized.

HEURISTIC - greedily materializes the earliest parent CTE, which is repeated >= cte_heuristic_replication_threshold times.

HEURISTIC_COMPLEX_QUERIES_ONLY greedily materializes the earliest parent CTE which meets the HEURISTIC criteria and has a join or aggregate.

Use the cte_materialization_strategy session property to set on a per-query basis.

cte-heuristic-replication-threshold

  • Type: integer
  • Minimum value: 0
  • Default value: 4

When cte-materialization-strategy is set to HEURISTIC or HEURISTIC_COMPLEX_QUERIES_ONLY, then CTEs will be materialized if they appear in a query at least cte-heuristic-replication-threshold number of times.

Use the cte_heuristic_replication_threshold session property to set on a per-query basis.

query.cte-partitioning-provider-catalog

  • Type: string
  • Default value: system

The name of the catalog that provides custom partitioning for CTE materialization. This setting specifies which catalog should be used for CTE materialization.

Use the cte_partitioning_provider_catalog session property to set on a per-query basis.

cte-filter-and-projection-pushdown-enabled

  • Type: boolean
  • Default value: true

Flag to enable or disable the pushdown of common filters and projects into the materialized CTE.

Use the cte_filter_and_projection_pushdown_enabled session property to set on a per-query basis.

hive.cte-virtual-bucket-count

  • Type: integer
  • Default value: 128

The number of buckets to be used for materializing CTEs in queries. This setting determines how many buckets are used when materializing the CTEs, potentially affecting the performance of queries involving CTE materialization. A higher number of buckets might improve parallelism but also increases overhead in terms of memory and network communication.

Recommended value: 4 - 10 times the size of the cluster.

Use the hive.cte_virtual_bucket_count session property to set on a per-query basis.

hive.temporary-table-storage-format

  • Type: string
  • Allowed values: PAGEFILE, ORC, DWRF, ALPHA, PARQUET, AVRO, RCBINARY, RCTEXT, SEQUENCEFILE, JSON, TEXTFILE, CSV
  • Default value: ORC

This setting determines the data format for temporary tables generated by CTE materialization. The recommended value is PAGEFILE :doc:`/develop/serialized-page`, as it is the most performant, since it avoids serialization and deserialization during reads and writes, allowing for direct storage of Presto pages.

Use the hive.temporary_table_storage_format session property to set on a per-query basis.

hive.temporary-table-compression-codec

  • Type: string
  • Allowed values: SNAPPY, NONE, GZIP, LZ4, ZSTD
  • Default value: SNAPPY

This property defines the compression codec to be used for temporary tables generated by CTE materialization.

Use the hive.temporary_table_compression_codec session property to set on a per-query basis.

hive.bucket-function-type-for-cte-materialization

  • Type: string
  • Allowed values: HIVE_COMPATIBLE, PRESTO_NATIVE
  • Default value: PRESTO_NATIVE

This setting specifies the Hash function type for CTE materialization.

Use the hive.bucket_function_type_for_cte_materialization session property to set on a per-query basis.

query.max-written-intermediate-bytes

  • Type: DataSize
  • Default value: 2TB

This setting defines a cap on the amount of data that can be written during CTE Materialization. If a query exceeds this limit, it will fail.

Use the query_max_written_intermediate_bytes session property to set on a per-query basis.

How to Participate in Development

List of issues - ( cte_materialization )