The pgpro_temp_stats extension collects extended statistics on multiple columns of temporary tables. It operates automatically during execution of queries that reference temporary tables.

The pgpro_temp_stats extension is provided with Postgres Pro Enterprise as a separate pre-built package pgpro-temp-stats-ent-17 (for the detailed installation instructions, see Chapter 17).

To enable pgpro_temp_stats, complete the following steps:

The pgpro_temp_stats extension automatically searches for queries that reference temporary tables and collects the required statistical data. This data allows the planner to predict row cardinality for such queries more accurately and reduce query execution time.

SELECT * FROM t1 WHERE (a = 1) AND (b = 0);
DEBUG:  CREATE STATISTICS st0_t1 ON a, b FROM t1;
DEBUG:  ANALYZE t1 (a, b)

SELECT * FROM t2 JOIN t3 ON t2.id = t3.id AND t2.val = t3.val;
DEBUG:  CREATE STATISTICS st0_t2 ON id, val from t2;
DEBUG:  ANALYZE t2 (id, val)
DEBUG:  CREATE STATISTICS st0_t3 ON id, val from t3;
DEBUG:  ANALYZE t3 (id, val)

SELECT * FROM t4 WHERE a > 10 AND date_trunc('week', b) = date '2023-05-01';
DEBUG:  CREATE STATISTICS st0_t4 ON a, date_trunc('week', b) from t4;
DEBUG:  ANALYZE t4 (a, b)

This example demonstrates how the pgpro_temp_stats extension works.

-- The pgpro_temp_stats extension is disabled

-- Create a sample temporary table
CREATE TEMP TABLE t1 (
    a int,
    b int
);

-- Execute ANALYZE for the entire table
ANALYZE t1;

-- Add data to the created table
INSERT INTO t1 SELECT i/100, i/500 FROM generate_series(1, 10000) s(i);

-- Execute a query that references two columns of the created temporary table
-- The estimated number of rows is significantly lower than the actual number of rows
EXPLAIN ANALYZE
SELECT * FROM t1 WHERE (a = 1) AND (b = 0);
                                        QUERY PLAN
------------------------------------------------------------------------------------------------
Seq Scan on t1  (cost=0.00..198.22 rows=1 width=8) (actual time=0.059..3.560 rows=100 loops=1)
Filter: ((a = 1) AND (b = 0))
Rows Removed by Filter: 9900
Buffers: local hit=45
Planning:
Buffers: shared hit=24
Planning Time: 0.313 ms
Execution Time: 3.594 ms
(8 rows)

-- Enable the pgpro_temp_stats extension
SET pgpro_temp_stats.enable = true;

-- Configure messages to see information about creating statistics and analyzing
SET client_min_messages = 'debug1';

-- Execute the same query. For columns referenced by this query,
-- pgpro_temp_stats automatically creates an extended statistics object and runs ANALYZE
-- As a result, the number of rows is estimated more accurate
EXPLAIN ANALYZE
SELECT * FROM t1 WHERE (a = 1) AND (b = 0);
DEBUG:  CREATE STATISTICS st0_t1 ON a, b FROM t1;
DEBUG:  ANALYZE t1 (a, b)
                                            QUERY PLAN
--------------------------------------------------------------------------------------------------
Seq Scan on t1  (cost=0.00..195.00 rows=100 width=8) (actual time=0.090..2.981 rows=100 loops=1)
Filter: ((a = 1) AND (b = 0))
Rows Removed by Filter: 9900
Buffers: local hit=45
Planning:
Buffers: shared hit=143 dirtied=6 written=6, local hit=45
Planning Time: 12.958 ms
Execution Time: 3.009 ms
(8 rows)