Monitor the columnar engine

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

Verify usage of the columnar engine using EXPLAIN

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

psql Client

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 being 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.

psql Client

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.

Use the COLUMNAR_ENGINE option for EXPLAIN

The EXPLAIN command supports the COLUMNAR_ENGINE option. When specified, the command prints out Columnar Check messages in the EXPLAIN plan to show what might have led the planner or executor to make certain columnar engine related choices for the plan. The command also prints out additional details that are specific to the columnar engine. The message is associated with the non-columnar scan node. It typically indicates the first cause that prevents a columnar scan from being chosen for a scan. Some example causes are the table is too small, a needed column of the table is not in the CE store, or a needed column has a CE unsupported data type.

The following example output of the command prints out a Columnar Check message:

psql Client

EXPLAIN (COLUMNAR_ENGINE, COSTS OFF)
SELECT * FROM sample_small_table
WHERE col1 > 10000;
              QUERY PLAN
--------------------------------------
 Seq Scan on sample_small_table
   Filter: (col1 > 10000)
   Columnar Check: table is too small

Based on the message, the user can do one of the following:

  • Address the specific cause and then rerun the command to confirm the columnar plan.
  • Iterate over the same process. There can be more than one cause, and the clause only tries to list the first one.

The following example shows additional details being printed that are specific to the columnar engine:

psql Client

EXPLAIN (ANALYZE, COLUMNAR_ENGINE, COSTS OFF, TIMING OFF, SUMMARY OFF, VERBOSE) SELECT SUM(c1) FROM counter_table WHERE c2 > 0 and c2 < 80000;
                                         QUERY PLAN
---------------------------------------------------------------------------------------------
 Aggregate (actual rows=1 loops=1)
   Output: sum(c1)
   ->  Append (actual rows=79999 loops=1)
         ->  Custom Scan (columnar scan) on public.counter_table (actual rows=79999 loops=1)
               Output: c1
               Filter: ((counter_table.c2 > 0) AND (counter_table.c2 < 80000))
               Rows Removed by Columnar Filter: 1
               Rows Aggregated by Columnar Scan: 79999
               Bytes fetched from storage cache: 1392655
               Columnar cache search mode: native
         ->  Seq Scan on public.counter_table (never executed)
               Output: c1
               Filter: ((counter_table.c2 > 0) AND (counter_table.c2 < 80000))
(13 rows)

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.

psql Client

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.

psql Client

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

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.

psql Client

  1. Enable the pg_stat_statements extension: 
    CREATE EXTENSION pg_stat_statements;
  2. Make the queries whose statistics you want to view. You can do this manually, or you can let enough time pass so that your applications make 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 null 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 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 resulting integer shows the available memory in megabytes (MB).

SELECT google_columnar_engine_memory_available();