Extended Statistics in PG19: Virtual Columns, pg_restore_extended_stats(), pg_clear_extended_stats() and pg_dump

By /

Introduction:

Have you ever wondered why an Optimized PostgreSQL query suddenly becomes slow, and why the query planner chose a poor execution plan? The culprit is often an inaccurate row-count estimate. By default, PostgreSQL collects statistics for individual columns, which may not capture relationships between columns or calculated values. This can lead the planner to make inaccurate estimates and choose a less efficient execution plan.

PostgreSQL 19 fundamentally shifts these dynamics by making extended statistics more powerful and easier to manage. We can now build extended statistics directly on virtual generated columns, giving the optimizer better visibility into complex, computed data structures. Additionally, database administrators gain new management utilities—like pg_clear_extended_stats() and pg_restore_extended_stats()—making it easier to clear, restore, and maintain accurate statistics during database operations.

What this blog covers:

In this blog, we will explore these enhancements through hands-on examples and compare extended statistics in PostgreSQL 18 and PostgreSQL 19. We will also walk through creating extended statistics on virtual generated columns, clearing extended statistics using the pg_clear_extended_stats() function, and backing up and restoring them using the pg_dump --statistics-only command-line utility and the pg_restore_extended_stats() function.

Extended Statistics on Virtual Generated Columns:
This feature helps the query planner collect statistics on virtual generated columns, whose values are calculated from other columns. This gives the optimizer more accurate row estimates and helps to choose better query plans.

Clearing Extended Statistics using pg_clear_extended_stats() function:
This feature helps to clear the previously collected extended-statistical data while keeping the underlying statistics object definition intact.

Preserving Extended Statistics via pg_dump
PostgreSQL 19 includes extended-statistics data in logical backups. This allows the statistics to be restored along with the database and helps to maintain accurate query plans after restores and migrations.

Restoring Extended Statistics using pg_restore_extended_stats() function:
This feature helps to restore previously saved extended-statistical data into the system catalog, allowing the planner to use the restored statistics without performing a full ANALYZE.

1. Extended Statistics on Virtual Generated Columns

A virtual generated column is a column whose value is automatically calculated from other columns in the same row. The calculated value is not stored separately; PostgreSQL computes it when needed.

Comparing this feature in PostgreSQL 18 and PostgreSQL 19 

To test this feature, let’s create the same virtual generated column on both versions(PG18 & PG19) and then try to create extended statistics on both versions:
Connect  to the PG18 database and verify the version: 

postgres=# select version();
-[ RECORD 1 ]--------------------------------------------------------------------------------------------------------------------------------
version | PostgreSQL 18.6 (Ubuntu 18.6-1.pgdg24.04+2) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.3.0-6ubuntu2~24.04.1) 13.3.0, 64-bit

Creating a Table with a Virtual Generated Column 

postgres=# CREATE TABLE orders_virtual_compare (
    order_id INT,
    price NUMERIC,
    quantity INT,
    total_amount NUMERIC
        GENERATED ALWAYS AS (price * quantity) VIRTUAL
);
CREATE TABLE

Inserting Sample Data into the Table

postgres=# INSERT INTO orders_virtual_compare (order_id, price, quantity)
VALUES
(1, 100, 2),
(2, 200, 5),
(3, 50, 10),
(4, 100, 2),
(5, 500, 4),
(6, 200, 5),
(7, 50, 10),
(8, 1000, 2),
(9, 250, 4),
(10, 500, 4);
INSERT 0 10

Verifying the Inserted Data

postgres=# SELECT * FROM orders_virtual_compare;
-[ RECORD 1 ]+-----
order_id     | 1
price        | 100
quantity     | 2
total_amount | 200
-[ RECORD 2 ]+-----
order_id     | 2
price        | 200
quantity     | 5
total_amount | 1000
-[ RECORD 3 ]+-----
order_id     | 3
price        | 50
quantity     | 10
total_amount | 500
-[ RECORD 4 ]+-----
order_id     | 4
price        | 100
quantity     | 2
total_amount | 200
-[ RECORD 5 ]+-----
order_id     | 5
price        | 500
quantity     | 4
total_amount | 2000

Here, total_amount is a virtual generated column. It automatically calculates total_amount as price × quantity (500 × 4 = 2000).
Now, let’s create extended statistics on the virtual generated column: 

This confirms that PostgreSQL 18 does not support creating extended statistics on virtual generated columns. The virtual column can be created and queried, but extended statistics cannot be collected on it. PostgreSQL 19 provides us with the ability to create extended statistics on virtual generated columns, allowing the planner to use statistics for better row estimates.
Lets us create a table with a virtual generated column on PostgreSQL19.

Connect to the PG19 database and verify the version: 

postgres=# select version();
                                                  version

----------------------------------------------------------------------------
--------------------------------
 PostgreSQL 19beta4 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.3.0-6
ubuntu2~24.04.1) 13.3.0, 64-bit
(1 row)

Then create a table with virtual generated columns:

 CREATE TABLE orders_stats_demo (
    order_id INT,
    price NUMERIC,
    quantity INT,
    total_amount NUMERIC
        GENERATED ALWAYS AS (price * quantity) VIRTUAL
);
CREATE TABLE

Insert the sample data:

INSERT INTO orders_stats_demo (order_id, price, quantity)
SELECT
    gs,
    CASE
        WHEN gs <= 8000 THEN 100
        ELSE 500
    END,
    CASE
        WHEN gs <= 8000 THEN 5
        ELSE 4
    END
FROM generate_series(1, 10000) AS gs;
INSERT 0 10000

Verify the count:

postgres=# SELECT total_amount, count(*)
FROM orders_stats_demo
GROUP BY total_amount
ORDER BY total_amount;
 total_amount | count
--------------+-------
          500 |  8000
         2000 |  2000
(2 rows)

Before creating statistics, the EXPLAIN ANALYZE plan shows:


After creating the extended statistics on this table:

How this helps DBAs:

This example shows how PostgreSQL 19 improves the planner’s understanding of virtual generated columns.. Before creating extended statistics, PostgreSQL estimated only 50 rows for the query, while the actual result was 8,000 rows. After creating extended statistics and running ANALYZE, the estimated rows increased to 8,000, matching the actual result. This gives the query planner more accurate information about the data distribution and can help it choose a more appropriate execution plan. For DBAs, this will be useful when managing applications that frequently query virtual or calculated columns. 

2. Clearing Extended Statistics — pg_clear_extended_stats()

PostgreSQL 19 introduces the pg_clear_extended_stats() function, which provides a new way to manage collected extended-statistical data. The function removes the collected statistics data but keeps the extended statistics object definition intact. This allows DBAs to clear existing statistical data and collect fresh statistics when required, without dropping and recreating the statistics objects. In PostgreSQL 18, there is no built-in function to clear the collected data while keeping the statistics definition.  But PostgreSQL 19 adds this capability.

Let’s do the comparison test of PG18 & PG19 for pg_clear_extended_stats() function step by step.

Checking pg_clear_extended_stats() Availability in PostgreSQL 18

postgres=# select version();
-[ RECORD 1 ]--------------------------------------------------------------------------------------------------------------------------------
version | PostgreSQL 18.6 (Ubuntu 18.6-1.pgdg24.04+2) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.3.0-6ubuntu2~24.04.1) 13.3.0, 64-bit.

postgres=# SELECT pg_clear_extended_stats(
    'public',
    'orders_virtual_compare',
    'public',
    'orders_virtual_stats',
    false
);
ERROR:  function pg_clear_extended_stats(unknown, unknown, unknown, unknown, boolean) does not exist

Checking pg_clear_extended_stats() Availability in PostgreSQL 19

postgres=# SELECT pg_clear_extended_stats(
    'public',
    'orders_virtual_compare',
    'public',
    'orders_virtual_stats',
    false
);
 pg_clear_extended_stats
-------------------------
(1 row)

Verifying Existing Extended Statistics

postgres=# SELECT statistics_name, expr, n_distinct,
       most_common_vals, most_common_freqs
FROM pg_stats_ext_exprs
WHERE statistics_name = 'orders_stats_demo_ext';

statistics_name    |     expr     | n_distinct | most_common_vals | most
_common_freqs
-----------------------+--------------+------------+------------------+-----
--------------
 orders_stats_demo_ext | total_amount |          2 | {500,2000}       | {0.8
,0.2}

The output showed that PostgreSQL had collected statistics for total_amount, including two distinct values (500 and 2000) with frequencies of 0.8 and 0.2.
Next, I used pg_clear_extended_stats() to clear the collected statistics:

postgres=# SELECT pg_clear_extended_stats(
    'public',
    'orders_stats_demo',
    'public',
    'orders_stats_demo_ext',
    false
);
 pg_clear_extended_stats
-------------------------
(1 row)

pg_clear_extended_stats() is a PostgreSQL function that clears the collected extended-statistics data while preserving the statistics object definition. Fresh statistics can then be collected by running ANALYZE.

Checking Statistics After Clearing with pg_clear_extended_stats()

postgres=# SELECT statistics_name, expr, n_distinct,
       most_common_vals, most_common_freqs
FROM pg_stats_ext_exprs
WHERE statistics_name = 'orders_stats_demo_ext';
    statistics_name    |     expr     | n_distinct | most_common_vals | most
_common_freqs
-----------------------+--------------+------------+------------------+-----
--------------
 orders_stats_demo_ext | total_amount |            |                  |
(1 row)

This confirms that pg_clear_extended_stats() cleared the collected extended-statistics data, but did not remove the statistics object itself. The statistics_name and expr are still present, while n_distinct, most_common_vals, and most_common_freqs are empty. This means the statistics definition is preserved, but its collected data has been removed. Fresh statistics can be collected again by running ANALYZE on the table.

How This Helps DBAs:

PostgreSQL 19 can collect statistics for virtual generated columns, helping the query planner make better row estimates and choose better execution plans. If the data distribution changes, existing statistics may become outdated. In such cases, pg_clear_extended_stats() allows DBAs to clear the collected statistics while keeping the statistics definition. Fresh statistics can then be collected so the planner can make better decisions.

3. Restoring Extended Statistics – pg_restore_extended_stats()

PostgreSQL 19 introduces pg_restore_extended_stats(), a function that allows DBAs to restore previously saved extended-statistics data. This is useful during database upgrades, restores, or migrations because the planner can use the restored statistics without having to collect all the statistics again using ANALYZE.

In PostgreSQL 18, extended-statistical data is not included in the statistics-restore functions. Only basic, single-column statistics can be restored using functions such as pg_restore_relation_stats() and pg_restore_attribute_stats(). Extended statistics need to be collected again using ANALYZE.

Comparing pg_restore_extended_stats() in PostgreSQL 18 and 19

PG18:

postgres=# select version();
-[ RECORD 1 ]--------------------------------------------------------------------------------------------------------------------------------
version | PostgreSQL 18.6 (Ubuntu 18.6-1.pgdg24.04+2) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.3.0-6ubuntu2~24.04.1) 13.3.0, 64-bit

Checking pg_restore_extended_stats() Availability in PostgreSQL 18

postgres=# SELECT proname
FROM pg_proc
WHERE proname = 'pg_restore_extended_stats';
(0 rows)

PG19:

postgres=# select version();
                                                  version

----------------------------------------------------------------------------
--------------------------------
 PostgreSQL 19beta4 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.3.0-6
ubuntu2~24.04.1) 13.3.0, 64-bit
(1 row)

Verifying Function Availability and Details in PostgreSQL 19

postgres=# \df+ pg_restore_extended_stats
List of functions
-[ RECORD 1 ]-------+-------------------------------------------------
Schema              | pg_catalog
Name                | pg_restore_extended_stats
Result data type    | boolean
Argument data types | VARIADIC kwargs "any"
Type                | func
Volatility          | volatile
Parallel            | unsafe
Owner               | postgres
Security            | invoker
Leakproof?          | no
Access privileges   |
Language            | internal
Internal name       | pg_restore_extended_stats
Description         | restore statistics on extended statistics object

Now, let’s see how this pg_restore_extended_stats() feature can be used to restore previously collected extended-statistics data. For that, we will create a test table with correlated data, create extended statistics, collect the statistics, and take a statistics-only dump. We will then clear the collected statistics, restore them from the dump, and verify whether the statistics have been restored successfully.

Create a test table and insert 10,000 rows of correlated data

pg19_feature_test=# CREATE TABLE restore_stats_demo (
    id INT,
    country TEXT,
    city TEXT
);
CREATE TABLE

pg19_feature_test=# INSERT INTO restore_stats_demo (id, country, city)
SELECT gs,
       CASE
           WHEN gs <= 4000 THEN 'India'
           WHEN gs <= 8000 THEN 'India'
           ELSE 'USA'
       END,
       CASE
           WHEN gs <= 4000 THEN 'Hyderabad'
           WHEN gs <= 8000 THEN 'Bengaluru'
           WHEN gs <= 9000 THEN 'New York'
           ELSE 'Chicago'
       END
FROM generate_series(1,10000) AS gs;
INSERT 0 10000

Create extended statistics on this table:

pg19_feature_test=# CREATE STATISTICS restore_stats_demo_ext
ON country, city
FROM restore_stats_demo;
CREATE STATISTICS

 Collect the statistics

ANALYZE restore_stats_demo;

 Verify the collected statistics

pg19_feature_test=# SELECT statistics_name, attnames, n_distinct, most_common_vals, most_common_freqs
FROM pg_stats_ext
WHERE statistics_name = 'restore_stats_demo_ext';

statistics_name     |    attnames    |                n_distinct
        |                           most_common_vals
   | most_common_freqs
------------------------+----------------+----------------------------------
--------+-------------------------------------------------------------------
---+-------------------
 restore_stats_demo_ext | {country,city} | [{"attributes": [2, 3], "ndistinc
t": 4}] | {{India,Bengaluru},{India,Hyderabad},{USA,Chicago},{USA,"New York"
}} | {0.4,0.4,0.1,0.1}

Create a Statistics-Only Dump

pg19_feature_test=# \q
postgres@testing:~$ /usr/local/pgsql19/bin/pg_dump -h /tmp -p 5433 -d pg19_feature_test --statistics-only -f /tmp/restore_stats_demo.sql

Verifying pg_restore_extended_stats() in the Statistics Dump

postgres@testing:~$ grep -n -A15 "restore_stats_demo_ext" /tmp/restore_stats_demo.sql
127:-- Statistics for Name: restore_stats_demo_ext; Type: EXTENDED STATISTICS DATA; Schema: public; Owner: postgres
128---
129-
130-SELECT * FROM pg_catalog.pg_restore_extended_stats(
131-    'version', '190000'::integer,
132-    'schemaname', 'public',
133-    'relname', 'restore_stats_demo',
134-    'statistics_schemaname', 'public',
135:    'statistics_name', 'restore_stats_demo_ext',
136-    'inherited', 'f'::boolean,
137-    'n_distinct', '[{"attributes": [2, 3], "ndistinct": 4}]'::pg_ndistinct,
138-    'dependencies', '[{"attributes": [3], "dependency": 2, "degree": 1.000000}]'::pg_dependencies,
139-    'most_common_vals', '{{India,Bengaluru},{India,Hyderabad},{USA,Chicago},{USA,"New York"}}'::text[],
140-    'most_common_freqs', '{0.4,0.4,0.1,0.1}'::double precision[],
141-    'most_common_base_freqs', '{0.32000000000000006,0.32000000000000006,0.020000000000000004,0.020000000000000004}'::double precision[]
142-);
143-
144-
145---
146--- Statistics for Name: sales_normal_stats; Type: EXTENDED STATISTICS DATA; Schema: public; Owner: postgres
147---
148-
149-SELECT * FROM pg_catalog.pg_restore_extended_stats(
150-    'version', '190000'::integer,

Clearing Extended Statistics Using pg_clear_extended_stats()

pg19_feature_test=# SELECT pg_clear_extended_stats(
    'public',
    'restore_stats_demo',
    'public',
    'restore_stats_demo_ext',
    false
);
 pg_clear_extended_stats
-------------------------
(1 row)

Verify that the collected statistics were cleared or not

pg19_feature_test=# SELECT statistics_name,
       attnames,
       n_distinct,
       most_common_vals,
       most_common_freqs
FROM pg_stats_ext
WHERE statistics_name = 'restore_stats_demo_ext';
 statistics_name | attnames | n_distinct | most_common_vals | most_common_fr
eqs
-----------------+----------+------------+------------------+---------------
----
(0 rows)

This confirms that collected statistics are removed.

 Verify that the statistics object still exists

pg19_feature_test=# SELECT stxname
FROM pg_statistic_ext
WHERE stxname = 'restore_stats_demo_ext';
        stxname
------------------------
 restore_stats_demo_ext
(1 row)

 So, the statistics data was cleared, but the statistics definition remained. 

Restore the statistics :

postgres@testing:~$ /usr/local/pgsql19/bin/psql -h /tmp -p 5433 \
-d pg19_feature_test -f /tmp/restore_stats_demo.sql
SET
SET
SET
SET
SET
SET
 set_config
------------

(1 row)

SET
SET
SET
SET
 pg_restore_relation_stats
---------------------------
 t
(1 row)

 pg_restore_attribute_stats
----------------------------
 t
(1 row)

 pg_restore_attribute_stats
----------------------------
 t
(1 row)

 pg_restore_attribute_stats
----------------------------
 t
(1 row)

 pg_restore_relation_stats
---------------------------
 t
(1 row)

 pg_restore_attribute_stats
----------------------------
 t
(1 row)

 pg_restore_attribute_stats
----------------------------
 t
(1 row)

 pg_restore_attribute_stats
----------------------------
 t
(1 row)

 pg_restore_extended_stats
---------------------------
 t
(1 row)

 pg_restore_extended_stats
---------------------------
 t
(1 row)

 pg_restore_extended_stats
---------------------------
 t
(1 row)

The statistics-only dump was successfully restored using psql. The pg_restore_extended_stats entries returned t, indicating that the extended-statistics data was restored successfully. The output also shows that relation-level and attribute-level statistics were restored as part of the dump.

Verify the restored statistics

pg19_feature_test=# SELECT statistics_name,
       attnames,
       n_distinct,
       most_common_vals,
       most_common_freqs
FROM pg_stats_ext
WHERE statistics_name = 'restore_stats_demo_ext';
    statistics_name     |    attnames    |                n_distinct
        |                           most_common_vals
   | most_common_freqs
------------------------+----------------+----------------------------------
--------+-------------------------------------------------------------------
---+-------------------
 restore_stats_demo_ext | {country,city} | [{"attributes": [2, 3], "ndistinc
t": 4}] | {{India,Bengaluru},{India,Hyderabad},{USA,Chicago},{USA,"New York"
}} | {0.4,0.4,0.1,0.1}

How this helps DBAs:

PostgreSQL 19 introduces pg_restore_extended_stats() to restore previously collected extended statistics. In this lab, the statistics were dumped using pg_dump –statistics-only and then cleared using pg_clear_extended_stats(). After restoring the dump, the original extended-statistics data was successfully restored without running ANALYZE. This helps DBAs retain useful planner statistics during database restore, upgrade, or migration operations.

4.pg_dump Includes Restorable Extended Statistics

PostgreSQL 18 supports pg_dump --statistics-only for backing up statistics, but it does not include the collected data from extended statistics created by using CREATE STATISTICS. It can restore relation-level and column-level statistics using pg_restore_relation_stats() and pg_restore_attribute_stats(), but there is no function to restore extended-statistics data.
PostgreSQL 19 improves this by including extended-statistics data in pg_dump --statistics-only. It also introduces pg_restore_extended_stats(), which allows the saved extended-statistics data to be restored. This means DBAs can preserve and restore extended statistics during database upgrades, migrations, and restores.

Here is the YouTube video:

Conclusion:

The PostgreSQL 19 extended statistics features tested here represent a significant advancement in query optimization and operational reliability. By eliminating estimation bottlenecks on correlated data and virtual generated columns. PostgreSQL directly improves planner accuracy and execution performance. When paired with new administrative functions like pg_clear_extended_stats() and pg_restore_extended_stats(), database teams gain faster, more reliable statistics portability across upgrades and environments. Ultimately, these tools bridge the gap between complex data models and seamless, predictable database operations.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top