This example demonstrates how pgpro_planner transformation works for the following sample query:

EXPLAIN (COSTS FALSE) SELECT * FROM onek
  WHERE unique1 IN (VALUES(1200), (1));

Without this transformation, the query execution plan looks as follows:

Nested Loop
  ->  Unique
        ->  Sort
              Sort Key: "*VALUES*".column1
              ->  Values Scan on "*VALUES*"
  ->  Index Scan using onek_unique1 on onek
        Index Cond: (unique1 = "*VALUES*".column1)
(7 rows)

For the VALUES expression, the planner creates the corresponding temporary table and performs value comparisons using the Nested Loop and Index Cond nodes. These decisions lead to long query execution time.

When pgpro_planner transforms the VALUES expression, the resulting plan uses the more effective Bitmap Heap Scan node instead of Nested Loop, which reduces query execution time.

Bitmap Heap Scan on onek
  Recheck Cond: (unique1 = ANY ('{1200,1}'::integer[]))
  ->  Bitmap Index Scan on onek_unique1
        Index Cond: (unique1 = ANY ('{1200,1}'::integer[]))
(4 rows)

pgpro_planner cannot transform the VALUES expression in the following cases:

This example demonstrates how pgpro_planner arithmetic simplification affects execution plans for the following query:

SELECT * FROM t WHERE x + 0 > 900;

For the initial arithmetic operation, the planner produces the query execution plan with Seq Scan and does not use an index.

Seq Scan on t  (cost=0.00..20.00 rows=333 width=4) (actual time=0.500..0.571 rows=100.00 loops=1)
  Filter: ((x + 0) > 900)
  Rows Removed by Filter: 900
  Buffers: shared hit=5
Planning:
  Buffers: shared hit=14
Planning Time: 0.432 ms
Execution Time: 0.627 ms
(8 rows)

When pgpro_planner simplifies this operation, the planner creates the plan with Index Only Scan and performs value comparisons in Index Cond, which helps to reduce query execution time.

Index Only Scan using idx_t_x on t  (cost=0.28..10.03 rows=100 width=4) (actual time=0.077..0.247 rows=100.00 loops=1)
  Index Cond: (x > 900)
  Heap Fetches: 100
  Index Searches: 1
  Buffers: shared hit=4
Planning Time: 0.217 ms
Execution Time: 0.319 ms
(7 rows)

pgpro_planner has the following limitations for arithmetic simplification:

This example demonstrates how caching of correlated subqueries works in different cases.

Create two sample tables named t and t1 and a unique index on the x column of the first table.

CREATE TABLE t (x int, y int);
INSERT INTO t SELECT id, id%20 FROM generate_series(1,1000) id;
CREATE UNIQUE INDEX t_x_idx ON t (x);
CREATE TABLE t1 (x1 int);
INSERT INTO  t1 SELECT id%2 FROM generate_series(1,1000) id;

Run the following query, where the subquery uses values of the correlated column named y. There is no unique index on this column, so the Memoize node is added to the query execution plan.

EXPLAIN (COSTS OFF)
SELECT x FROM t WHERE (t.x,t.y) = (SELECT sum(x1),1 FROM t1 WHERE x1 = t.y LIMIT 1);
                    QUERY PLAN
------------------------------------------------
  Seq Scan on t
    Filter: (SubPlan 1)
    SubPlan 1
      ->  Memoize
            Cache Key: t.y
            Cache Mode: binary
            ->  Limit
                  ->  Aggregate
                        ->  Seq Scan on t1
                              Filter: (x1 = t.y)
(10 rows)

Run another query, where the subquery uses values of the correlated column named x. The unique index on this column exists but statistical information is not gathered yet, so the Memoize node is generated.

EXPLAIN (COSTS OFF)
SELECT x FROM t WHERE (t.x,t.y) = (SELECT sum(x1),1 FROM t1 WHERE x1 = t.x LIMIT 1);
                    QUERY PLAN
------------------------------------------------
  Seq Scan on t
    Filter: (SubPlan 1)
    SubPlan 1
      ->  Memoize
            Cache Key: t.x
            Cache Mode: binary
            ->  Limit
                  ->  Aggregate
                        ->  Seq Scan on t1
                              Filter: (x1 = t.x)
(10 rows)

Execute the same query once again. Statistical information for the x column is now gathered, and the unique index still exists, so the Memoize node is not added to the plan.

EXPLAIN (COSTS OFF)
SELECT x FROM t WHERE (t.x,t.y) = (SELECT sum(x1),1 FROM t1 WHERE x1 = t.x LIMIT 1);
                            QUERY PLAN
-----------------------------------------------------------------
 Seq Scan on t
   Filter: (x = (SubPlan 1))
   SubPlan 1
     ->  Limit
           ->  Aggregate
                 ->  Seq Scan on t1
                       Filter: (x1 = t.x)
(7 rows)

Remove the previously created index and create a new multicolumn index on both columns named x and y.

CREATE UNIQUE INDEX on t(x,y);
DROP INDEX t_x_idx;

Run the query referencing the correlated y column. Although the multicolumn unique index now exists, values of this column are not unique according to statistical information. The Memoize node is added to the resulting plan.

EXPLAIN (COSTS OFF)
SELECT x FROM t WHERE (t.x,t.y) = (SELECT sum(x1),1 FROM t1 WHERE x1 = t.y LIMIT 1);
                            QUERY PLAN
-----------------------------------------------------------------
  Seq Scan on t
    Filter: (((x = (SubPlan 1).col1) AND (y = (SubPlan 1).col2)))
    SubPlan 1
      ->  Memoize
            Cache Key: t.y
            Cache Mode: binary
            ->  Limit
                  ->  Aggregate
                        ->  Seq Scan on t1
                              Filter: (x1 = t.y)
(10 rows)

Run the query referencing the correlated x column. For this case, the Memoize node is not still generated, since values of this column are unique according to statistical information.

EXPLAIN (COSTS OFF)
SELECT x FROM t WHERE (t.x,t.y) = (SELECT sum(x1),1 FROM t1 WHERE x1 = t.x LIMIT 1);
                            QUERY PLAN
-----------------------------------------------------------------
  Seq Scan on t
    Filter: (((x = (SubPlan 1).col1) AND (y = (SubPlan 1).col2)))
    SubPlan 1
      ->  Limit
            ->  Aggregate
                  ->  Seq Scan on t1
                        Filter: (x1 = t.x)
(7 rows)

pgpro_planner cannot cache results of a correlated subquery in the following cases: