This section provides notes on using VCI.

• Regardless of whether VCI is used, the content of the result does not change. However, records may be returned in a different order if the ORDER BY clause is not specified.

• To reduce resource consumption, edit postgresql.conf or use the SET statement to enable/disable vci.enable when you use this feature only for specific times or jobs (SQL applications).

• The optimizer hint (pg_hint_plan) cannot be specified for a VCI. The hint clause is ignored if it is specified.

• If a plan other than VCI is specified for the optimizer hint (pg_hint_plan), a VCI may be used. Therefore, if you specify a query plan with the hint clause, use the SET statement to set vci.enable to "off".

• The message below may be output when a scan that uses VCI is performed on the streaming replication standby server:

  "LOG: recovery has paused"
  "HINT: Execute pg_wal_replay_resume() to continue."

  This message is output because application of the WAL to the VCI temporarily pauses due to the scan being performed.

• Even if a scan is performed using a VCI, information in the idx_scan, idx_tup_read, and idx_tup_fetch columns of the collected statistics views, pg_stat_all_indexes and pg_stat_user_indexes, will not be updated.

• Currently, it is not possible to replace the query plan for parallel aggregation with the query plan using VCI. Therefore, if you create a VCI on a column of a partition table and aggregate (sum () etc.) on that column, one of the following plans will be selected. Use different setting parameters according to the situation of the target table.

  • Plan of the parallel aggregations using scan methods other than VCI scan

    It is selected when max_parallel_workers_per_gather is 1 or more.

    explain select sum(value) from test;
                                             QUERY PLAN
    -----------------------------------------------------------------------------------
     Finalize Aggregate  (cost=99906.30..99906.31 rows=1 width=8)
       ->  Gather  (cost=99906.08..99906.29 rows=2 width=8)
             Workers Planned: 2
             ->  Partial Aggregate  (cost=98906.08..98906.09 rows=1 width=8)
                   ->  Parallel Append  (cost=0.00..94739.83 rows=1666500 width=4)
                         ->  Parallel Seq Scan on test_1  (cost=0.00..43203.67 rows=833250 width=4)
                         ->  Parallel Seq Scan on test_2  (cost=0.00..43203.67 rows=833250 width=4)

    This plan is fast when the number of records to be aggregated (number of records that hit the search conditions) is very large. This is because the benefit of parallelizing aggregation is important, not the performance of scanning. For example, each parallel worker will perform a sequential scan and aggregate most of the scanned records.

  • Plan that aggregates VCI scan results by a single aggregator node

    It is selected by setting max_parallel_workers_per_gather to 0 and not creating a query plan of parallel aggregate.

    explain select sum(value) from test;
                                            QUERY PLAN
    -----------------------------------------------------------------------------------
     Aggregate  (cost=145571.00..145571.01 rows=1 width=8)
       ->  Append  (cost=0.00..135572.00 rows=3999600 width=4)
             ->  Custom Scan (VCI Scan) using test_1_id_value_idx on test_1  (cost=0.00..57787.00 rows=1999800 width=4)
                   Allocated Workers: 2
             ->  Custom Scan (VCI Scan) using test_2_id_value_idx on test_2  (cost=0.00..57787.00 rows=1999800 width=4)
                   Allocated Workers: 2

    This plan is fast when the number of aggregated items is not large or when the size of the aggregated column is smaller than the record size. This is because the scan performance is more important, so it is faster to aggregate the results of VCI scans of each partition.

• Originally, if there is only one partition to be accessed, the following VCI aggregation plan can be used. Below is an example of scanning only one partition with partition pruning.

  explain select sum(value) from test where id < 1000001;
                                          QUERY PLAN
  --------------------------------------------------------------------------------------
   Custom Scan (VCI Aggregate)  (cost=62786.50..62786.51 rows=1 width=8)
     Allocated Workers: 2
     ->  Custom Scan (VCI Scan) using test_1_id_value_idx on test_1  (cost=0.00..57787.00 rows=1999800 width=4)
           Filter: (id < 1000001)

  However, the current planner does not try to choose VCI aggregation because it creates a plan for parallel aggregation if the table is partitioned. So in this case, set max_parallel_workers_per_gather to 0 to force the planner to choose VCI aggregation.