
===================
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 - (https://github.com/prestodb/presto/labels/cte_materialization)


