Select a documentation version: <button class="button button-with-icon cloud-select-dropdown__button button-primary" aria-haspopup="true">18.1.1</button>

- [Current (18.4.0)](https://docs.cloud.google.com/alloydb/omni/linux/current/docs/columnar-engine/monitor.md.txt)
- [18.4.0](https://docs.cloud.google.com/alloydb/omni/linux/18.4.0/docs/columnar-engine/monitor.md.txt)
- [18.3.0](https://docs.cloud.google.com/alloydb/omni/linux/18.3.0/docs/columnar-engine/monitor.md.txt)
- [18.1.1](https://docs.cloud.google.com/alloydb/omni/linux/18.1.1/docs/columnar-engine/monitor.md.txt)
- [18.1.0](https://docs.cloud.google.com/alloydb/omni/linux/18.1.0/docs/columnar-engine/monitor.md.txt)

<br />

This page describes how to monitor utilization of the columnar engine.

<br />

## Verify usage of the columnar engine using `EXPLAIN`

To observe the new columnar operators that appear in a query's generated query
plan, you can verify the usage of the columnar engine by using the `EXPLAIN` statement.

```
EXPLAIN (ANALYZE,COSTS OFF,BUFFERS,TIMING OFF,SUMMARY OFF)
  SELECT l_returnflag, l_linestatus, l_quantity, l_extendedprice,
         l_discount, l_tax
    FROM lineitem
   WHERE l_shipdate <= date '1992-08-06'
;
                                 QUERY PLAN
---
 Append (actual rows=3941797 loops=1)
   Buffers: shared hit=9
   ->  Custom Scan (columnar scan) on lineitem (actual rows=3941797 loops=1)
         Filter: (l_shipdate <= '1992-08-06'::date)
         Rows Removed by Columnar Filter: 56054083
         Columnar cache search mode: columnar filter only
         Buffers: shared hit=9
   ->  Seq Scan on lineitem (never executed)
  Filter: (l_shipdate <= '1992-08-06'::date)
```

- `Custom Scan (columnar scan)` indicates that columnar-engine scanning is included in the query plan.
- `Rows Removed by Columnar Filter` lists the number of rows filtered out by the columnar vectorized execution.
- `Columnar cache search mode` can be `columnar filter only`, `native`, or `row store scan`. The planner chooses the search mode automatically based on costing and pushdown evaluation capability.

When the planner chooses the `native` mode, it pushes down some of the
columnar operators to the scan:

- `Rows Aggregated by Columnar Scan` lists the number of rows that are aggregated.
- `Rows Sorted by Columnar Scan` lists the number of rows that are sorted.
- `Rows Limited by Columnar Scan` lists the limited number of rows that were scanned.

With joins, columnar scan operators can also use the `Late Materialization` mode.

```
EXPLAIN (ANALYZE,COSTS OFF,BUFFERS,TIMING OFF,SUMMARY OFF)
  SELECT l_shipmode, o_orderpriority
    FROM orders, lineitem
   WHERE o_orderkey = l_orderkey
         AND l_shipmode in ('AIR', 'FOB')
         AND l_receiptdate >= date '1995-01-01'
;
                                 QUERY PLAN
---
 Hash Join (actual rows=9865288 loops=1)
   Hash Cond: (lineitem.l_orderkey = orders.o_orderkey)
   Buffers: temp read=127738 written=127738
   ->  Append (actual rows=9865288 loops=1)
         ->  Custom Scan (columnar scan) on lineitem (actual rows=9865288 loops=1)
               Filter: ((l_shipmode = ANY ('{AIR,FOB}'::bpchar[])) AND
               (l_receiptdate >= '1995-01-01'::date))
               Rows Removed by Columnar Filter: 50130592
               Columnar cache search mode: native
         ->  Index Scan using idx_lineitem_orderkey_fkidx on lineitem
             (never executed)
               Filter: ((l_shipmode = ANY ('{AIR,FOB}'::bpchar[])) AND
               (l_receiptdate >= '1995-01-01'::date))
   ->  Hash (actual rows=15000000 loops=1)
         Buckets: 1048576  Batches: 32  Memory Usage: 37006kB
         Buffers: temp written=83357
         ->  Append (actual rows=15000000 loops=1)
               ->  Custom Scan (columnar scan) on orders (actual rows=15000000
                   loops=1)
                     Rows Removed by Columnar Filter: 0
                     Columnar projection mode: late materialization
                     Columnar cache search mode: native
               ->  Seq Scan on orders (never executed)
```

`Columnar projection mode` can be `late materialization`.
Columnar operators choose this mode automatically when the planner optimizes
the projection by deferring the materialization of some column values.

## View columnar engine vectorized join usage

You can verify the new vectorized join operator by using the `EXPLAIN` statement to
observe the new `Vectorized Hash Join` operator that appears in a query's
generated query plan.

```
EXPLAIN (ANALYZE,COSTS OFF,BUFFERS,TIMING OFF,SUMMARY OFF)
  SELECT l_quantity, l_extendedprice, l_discount, l_tax
    FROM lineitem, orders
    WHERE l_shipdate <= date '2022-08-06'
    AND  l_orderkey = o_orderkey
    AND  o_orderdate <= date '2025-03-07';
                                      QUERY PLAN
---
 Vectorized Hash Join (actual rows=3934686 loops=1)
   Vectorized partitioning, Partitions: 16 (Disk usage: 208MB)
   (Peak Memory Usage: 27MB, Threads: 1)
   Hash Cond: (lineitem.l_orderkey = orders.o_orderkey)
   Rows Removed by Bloom Filter: 0
   Buffers: temp read=26728 written=26728
   ->  Append (actual rows=3934686 loops=1)
         ->  Custom Scan (columnar scan) on lineitem (actual rows=3934686 loops=1)
               Filter: (l_shipdate <= '2022-08-06'::date)
               Rows Removed by Columnar Filter: 56051366
               Columnar cache search mode: native
         ->  Seq Scan on lineitem (never executed)
               Filter: (l_shipdate <= '2022-08-06'::date)
   ->  Vectorized Hash (actual rows=7245824 loops=1)
         Build bloom filter, Memory Usage: 1024kB
         ->  Append (actual rows=7245824 loops=1)
               ->  Custom Scan (columnar scan) on orders (actual rows=7245824 loops=1)
                     Filter: (o_orderdate <= '2025-03-07'::date)
                     Rows Removed by Columnar Filter: 7754176
                     Columnar cache search mode: native
               ->  Seq Scan on orders (never executed)
                     Filter: (o_orderdate <= '2025-03-07'::date)
```

- `Vectorized Hash Join ` indicates that the join between the two relations used the vectorized hash join.
- `Vectorized partitioning, Partitions: ` lists the number of partitions the data was split into.

## View information about tables with columns in the column store

You can view information about the tables or the materialized views with columns
in the column store by querying the `g_columnar_relations` view.

```
SELECT * FROM g_columnar_relations;

┌─[ RECORD 1 ]────────┬───────────────────┐
│ relation_name       │ tbl_parallel_test │
│ schema_name         │ public            │
│ database_name       │ advisor           │
│ status              │ Usable            │
│ size                │ 581431259         │
│ columnar_unit_count │ 3                 │
│ invalid_block_count │ 0                 │
│ total_block_count   │ 8337              │
├─[ RECORD 2 ]────────┼───────────────────┤
│ relation_name       │ lineitem          │
│ schema_name         │ public            │
│ database_name       │ advisor           │
│ status              │ Usable            │
│ size                │ 423224944         │
│ columnar_unit_count │ 29                │
│ invalid_block_count │ 0                 │
│ total_block_count   │ 115662            │
├─[ RECORD 3 ]────────┼───────────────────┤

```

## View information about the columns in the column store

You can view information about the columns in the column store by querying
the `g_columnar_columns` view, including those columns' size and the
last access time.

```
SELECT database_name, schema_name, relation_name, column_name, size_in_bytes, last_accessed_time FROM g_columnar_columns;
```

### View the column store in the web UI

You can also view the column store inventory in the AlloyDB Omni web user interface:

1. [Connect to the web UI](https://docs.cloud.google.com/alloydb/omni/linux/18.1.1/docs/connect-web-ui.md.txt) and select your cluster.
2. In the navigation menu, click **Columnar Engine**.
3. In the **Columnar Store data** table, review the columns populated in the column store:
   - **Database name**: The database that contains the relation.
   - **Schema name**: The schema of the relation.
   - **Relation name**: The name of the table or materialized view.
   - **Column name**: The name of the columnarized column.
   - **Column type**: The SQL data type of the column.
   - **Status** : The readiness status of the column in the column store (for example, `Usable`).
   - **Size**: The memory footprint of the column in the column store.
   - **Accesses**: The number of times the column has been accessed by columnar scans.
   - **Last accessed**: The timestamp of the most recent columnar scan access.

## View columnar engine execution statistics for recent queries

You can view columnar engine execution statistics for recent queries using the
`g_columnar_stat_statements` view. This view adds columnar engine statistics to
the `pg_stat_statements` view provided by the `pg_stat_statements` extension. To
use this view, you must first enable the `pg_stat_statements` extension.

1. Enable the `pg_stat_statements` extension:

   ```
   CREATE EXTENSION pg_stat_statements;
   ```
2. Run the queries whose statistics you want to view. You can do this manually, or you can let enough time pass so that your applications run these queries with `pg_stat_statements` enabled.
3. Query the `g_columnar_stat_statements` and `pg_stat_statements` views. Note the following query retrieves all the columnar execution statistics including those that were collected before the extension `pg_stat_statements` was created. The `10` value of `userid` indicates that the statistics were collected before the extension `pg_stat_statements` was created.

   ```
   SELECT *
   FROM pg_stat_statements(TRUE) AS pg_stats
        FULL JOIN g_columnar_stat_statements AS g_stats
        ON pg_stats.userid = g_stats.user_id AND
           pg_stats.dbid = g_stats.db_id AND
           pg_stats.queryid = g_stats.query_id
   WHERE columnar_unit_read > 0;

   ┌─[ RECORD 1 ]────────┬───────────────────────────────
   │ userid              │ 10                            │
   │ dbid                │ 33004                         │
   │ queryid             │ 6779068104316758833           │
   │ query               │ SELECT  l_returnflag,        ↵│
   │                     │         l_linestatus,        ↵│
   │                     │         l_quantity,          ↵│
   │                     │         l_extendedprice,     ↵│
   │                     │         l_discount,          ↵│
   │                     │         l_tax                ↵│
   │                     │FROM  lineitem                ↵│
   │                     │WHERE  l_shipdate <= date $1│
   │ calls               │ 1                             │
   │ total_time          │ 299.969983                    │
   │ min_time            │ 299.969983                    │
   │ max_time            │ 299.969983                    │
   │ mean_time           │ 299.969983                    │
   │ stddev_time         │ 0                             │
   │ rows                │ 392164                        │
   │ shared_blks_hit     │ 0                             │
   │ shared_blks_read    │ 0                             │
   │ shared_blks_dirtied │ 0                             │
   │ shared_blks_written │ 0                             │
   │ local_blks_hit      │ 0                             │
   │ local_blks_read     │ 0                             │
   │ local_blks_dirtied  │ 0                             │
   │ local_blks_written  │ 0                             │
   │ temp_blks_read      │ 0                             │
   │ temp_blks_written   │ 0                             │
   │ blk_read_time       │ 0                             │
   │ blk_write_time      │ 0                             │
   │ user_id             │ 10                            │
   │ db_id               │ 33004                         │
   │ query_id            │ 6779068104316758833           │
   │ columnar_unit_read  │ 29                            │
   │ page_read           │ 115662                        │
   │ rows_filtered       │ 0                             │
   │ columnar_scan_time  │ 0                             │
   └─────────────────────┴───────────────────────────────┘
   ```

### View columnar query statistics in the web UI

After enabling the `pg_stat_statements` extension, you can also inspect columnar query execution statistics in the AlloyDB Omni web user interface:

1. [Connect to the web UI](https://docs.cloud.google.com/alloydb/omni/linux/18.1.1/docs/connect-web-ui.md.txt) and select your cluster.
2. In the navigation menu, click **Columnar Engine**.
3. In the **Columnar Queries** table, review the execution statistics for analytical queries that used the column store:
   - **Query ID**: The identifier of the sanitized query.
   - **Query**: The sanitized SQL statement text, with constant values replaced by placeholders.
   - **Times called**: The number of times the query was executed.
   - **Avg. row fetched**: The average number of rows returned per execution.
   - **Mean latency**: The average execution latency of the query.
   - **Max latency**: The maximum execution latency recorded for the query.
   - **Columnar unit read**: The number of columnar units read from the column store.
   - **Page read**: The number of pages read during execution.

## View column store memory usage

To see the amount of unused RAM available to the columnar engine, you
can query the `google_columnar_engine_memory_available` function. The
output integer shows the available memory in megabytes (MB).

```sql
SELECT google_columnar_engine_memory_available();
```

### View column store memory size and settings in the web UI

You can also check the column store memory consumption and configuration settings in the AlloyDB Omni web user interface:

1. [Connect to the web UI](https://docs.cloud.google.com/alloydb/omni/linux/18.1.1/docs/connect-web-ui.md.txt) and select your cluster.
2. In the navigation menu, click **Columnar Engine**.
3. Review the **Columnar Store settings** section, which displays the following status and memory indicators:
   - **Columnar Engine** : Indicates whether the columnar engine is active (for example, `Enabled`).
   - **Memory size** : Displays the amount of column store memory in use out of the total allocated columnar memory (for example, `2.0 MB used of 1024.0 MB`).
   - **Auto Columnarization** : Indicates whether automatic columnarization is enabled (for example, `Enabled`).
   - **Run frequency** : Displays the schedule interval for automatic columnarization runs (for example, `Every 5 minutes`).
   - **Columnar scan** : Indicates whether columnar scan execution is enabled (for example, `Enabled`).

## View columnar engine storage cache

To view and monitor data about the storage cache of the columnar engine, you
can use the following commands:

| Command | Description |
|---|---|
| `SELECT google_columnar_engine_storage_cache_used();` | Returns the size of the used columnar engine storage cache. |
| `SELECT google_columnar_engine_storage_cache_available();` | Returns the size of the unused columnar engine storage cache. |
| `SHOW google_columnar_engine.storage_cache_size;` | Returns the total amount of the configured size of storage cache for the columnar engine. |