Thread

[PATCH 5/7] Add tests for quals push down to table AM

Julien Tachoires <julien@tachoires.me> — 2025-12-02T09:45:32Z

With the help of the EXPLAIN command, we check if the rows are filtered
out by the executor or by the table AM. We also make sure that by
default, quals are not pushed down.
---
 src/test/regress/expected/qual_pushdown.out | 253 ++++++++++++++++++++
 src/test/regress/parallel_schedule          |   2 +-
 src/test/regress/sql/qual_pushdown.sql      |  48 ++++
 3 files changed, 302 insertions(+), 1 deletion(-)
 create mode 100644 src/test/regress/expected/qual_pushdown.out
 create mode 100644 src/test/regress/sql/qual_pushdown.sql

diff --git a/src/test/regress/expected/qual_pushdown.out b/src/test/regress/expected/qual_pushdown.out
new file mode 100644
index 00000000000..7949fb949b5
--- /dev/null
+++ b/src/test/regress/expected/qual_pushdown.out
@@ -0,0 +1,253 @@
+DROP TABLE IF EXISTS qa;
+NOTICE:  table "qa" does not exist, skipping
+DROP TABLE IF EXISTS qb;
+NOTICE:  table "qb" does not exist, skipping
+CREATE TABLE qa (i INTEGER, ii INTEGER);
+CREATE TABLE qb (j INTEGER);
+INSERT INTO qa SELECT n, n * n  FROM generate_series(1, 1000) as n;
+INSERT INTO qb SELECT n FROM generate_series(1, 1000) as n;
+ANALYZE qa;
+ANALYZE qb;
+-- By default, the quals are not pushed down. The tuples are filtered out by
+-- the executor.
+EXPLAIN (ANALYZE, COSTS off, TIMING off, SUMMARY off, BUFFERS off) SELECT ii FROM qa WHERE i = 100;
+                QUERY PLAN                 
+-------------------------------------------
+ Seq Scan on qa (actual rows=1.00 loops=1)
+   Filter: (i = 100)
+   Rows Removed In Executor by Filter: 999
+(3 rows)
+
+EXPLAIN (ANALYZE, COSTS off, TIMING off, SUMMARY off, BUFFERS off) SELECT ii FROM qa WHERE i < 10;
+                QUERY PLAN                 
+-------------------------------------------
+ Seq Scan on qa (actual rows=9.00 loops=1)
+   Filter: (i < 10)
+   Rows Removed In Executor by Filter: 991
+(3 rows)
+
+EXPLAIN (ANALYZE, COSTS off, TIMING off, SUMMARY off, BUFFERS off) SELECT ii FROM qa WHERE 100 = i;
+                QUERY PLAN                 
+-------------------------------------------
+ Seq Scan on qa (actual rows=1.00 loops=1)
+   Filter: (100 = i)
+   Rows Removed In Executor by Filter: 999
+(3 rows)
+
+EXPLAIN (ANALYZE, COSTS off, TIMING off, SUMMARY off, BUFFERS off) SELECT ii FROM qa WHERE 10 > i;
+                QUERY PLAN                 
+-------------------------------------------
+ Seq Scan on qa (actual rows=9.00 loops=1)
+   Filter: (10 > i)
+   Rows Removed In Executor by Filter: 991
+(3 rows)
+
+EXPLAIN (ANALYZE, COSTS off, TIMING off, SUMMARY off, BUFFERS off) SELECT ii FROM qa WHERE i = SQRT(25)::INT;
+                QUERY PLAN                 
+-------------------------------------------
+ Seq Scan on qa (actual rows=1.00 loops=1)
+   Filter: (i = 5)
+   Rows Removed In Executor by Filter: 999
+(3 rows)
+
+EXPLAIN (ANALYZE, COSTS off, TIMING off, SUMMARY off, BUFFERS off) SELECT ii FROM qa WHERE i = (SELECT 100);
+                QUERY PLAN                 
+-------------------------------------------
+ Seq Scan on qa (actual rows=1.00 loops=1)
+   Filter: (i = (InitPlan expr_1).col1)
+   Rows Removed In Executor by Filter: 999
+   InitPlan expr_1
+     ->  Result (actual rows=1.00 loops=1)
+(5 rows)
+
+EXPLAIN (ANALYZE, COSTS off, TIMING off, SUMMARY off, BUFFERS off) SELECT ii FROM qa WHERE i = (SELECT SQRT(j)::INT FROM qb WHERE j = 100);
+                    QUERY PLAN                     
+---------------------------------------------------
+ Seq Scan on qa (actual rows=1.00 loops=1)
+   Filter: (i = (InitPlan expr_1).col1)
+   Rows Removed In Executor by Filter: 999
+   InitPlan expr_1
+     ->  Seq Scan on qb (actual rows=1.00 loops=1)
+           Filter: (j = 100)
+           Rows Removed In Executor by Filter: 999
+(7 rows)
+
+EXPLAIN (ANALYZE, COSTS off, TIMING off, SUMMARY off, BUFFERS off) SELECT ii FROM qa JOIN qb ON (qa.i = qb.j) WHERE j = 100;
+                   QUERY PLAN                    
+-------------------------------------------------
+ Nested Loop (actual rows=1.00 loops=1)
+   ->  Seq Scan on qa (actual rows=1.00 loops=1)
+         Filter: (i = 100)
+         Rows Removed In Executor by Filter: 999
+   ->  Seq Scan on qb (actual rows=1.00 loops=1)
+         Filter: (j = 100)
+         Rows Removed In Executor by Filter: 999
+(7 rows)
+
+EXPLAIN (ANALYZE, COSTS off, TIMING off, SUMMARY off, BUFFERS off) SELECT ii FROM qa WHERE i = ii AND ii < 10;
+                QUERY PLAN                 
+-------------------------------------------
+ Seq Scan on qa (actual rows=1.00 loops=1)
+   Filter: ((ii < 10) AND (i = ii))
+   Rows Removed In Executor by Filter: 999
+(3 rows)
+
+-- Enable quals push down
+ALTER TABLE qa SET (quals_push_down=on);
+ALTER TABLE qb SET (quals_push_down=on);
+-- Now, we expect to see the tuples being filtered out by the table AM
+EXPLAIN (ANALYZE, COSTS off, TIMING off, SUMMARY off, BUFFERS off) SELECT ii FROM qa WHERE i = 100;
+                QUERY PLAN                 
+-------------------------------------------
+ Seq Scan on qa (actual rows=1.00 loops=1)
+   Filter: (i = 100)
+   Rows Removed In Table AM by Filter: 999
+(3 rows)
+
+SELECT ii FROM qa WHERE i = 100;
+  ii   
+-------
+ 10000
+(1 row)
+
+EXPLAIN (ANALYZE, COSTS off, TIMING off, SUMMARY off, BUFFERS off) SELECT ii FROM qa WHERE i < 10;
+                QUERY PLAN                 
+-------------------------------------------
+ Seq Scan on qa (actual rows=9.00 loops=1)
+   Filter: (i < 10)
+   Rows Removed In Table AM by Filter: 991
+(3 rows)
+
+SELECT ii FROM qa WHERE i < 10;
+ ii 
+----
+  1
+  4
+  9
+ 16
+ 25
+ 36
+ 49
+ 64
+ 81
+(9 rows)
+
+EXPLAIN (ANALYZE, COSTS off, TIMING off, SUMMARY off, BUFFERS off) SELECT ii FROM qa WHERE 100 = i;
+                QUERY PLAN                 
+-------------------------------------------
+ Seq Scan on qa (actual rows=1.00 loops=1)
+   Filter: (100 = i)
+   Rows Removed In Table AM by Filter: 999
+(3 rows)
+
+SELECT ii FROM qa WHERE 100 = i;
+  ii   
+-------
+ 10000
+(1 row)
+
+EXPLAIN (ANALYZE, COSTS off, TIMING off, SUMMARY off, BUFFERS off) SELECT ii FROM qa WHERE 10 > i;
+                QUERY PLAN                 
+-------------------------------------------
+ Seq Scan on qa (actual rows=9.00 loops=1)
+   Filter: (10 > i)
+   Rows Removed In Table AM by Filter: 991
+(3 rows)
+
+SELECT ii FROM qa WHERE 10 > i;
+ ii 
+----
+  1
+  4
+  9
+ 16
+ 25
+ 36
+ 49
+ 64
+ 81
+(9 rows)
+
+EXPLAIN (ANALYZE, COSTS off, TIMING off, SUMMARY off, BUFFERS off) SELECT ii FROM qa WHERE i = SQRT(25)::INT;
+                QUERY PLAN                 
+-------------------------------------------
+ Seq Scan on qa (actual rows=1.00 loops=1)
+   Filter: (i = 5)
+   Rows Removed In Table AM by Filter: 999
+(3 rows)
+
+SELECT ii FROM qa WHERE i = SQRT(25)::INT;
+ ii 
+----
+ 25
+(1 row)
+
+EXPLAIN (ANALYZE, COSTS off, TIMING off, SUMMARY off, BUFFERS off) SELECT ii FROM qa WHERE i = (SELECT 100);
+                QUERY PLAN                 
+-------------------------------------------
+ Seq Scan on qa (actual rows=1.00 loops=1)
+   Filter: (i = (InitPlan expr_1).col1)
+   Rows Removed In Table AM by Filter: 999
+   InitPlan expr_1
+     ->  Result (actual rows=1.00 loops=1)
+(5 rows)
+
+SELECT ii FROM qa WHERE i = (SELECT 100);
+  ii   
+-------
+ 10000
+(1 row)
+
+EXPLAIN (ANALYZE, COSTS off, TIMING off, SUMMARY off, BUFFERS off) SELECT ii FROM qa WHERE i = (SELECT SQRT(j)::INT FROM qb WHERE j = 100);
+                    QUERY PLAN                     
+---------------------------------------------------
+ Seq Scan on qa (actual rows=1.00 loops=1)
+   Filter: (i = (InitPlan expr_1).col1)
+   Rows Removed In Table AM by Filter: 999
+   InitPlan expr_1
+     ->  Seq Scan on qb (actual rows=1.00 loops=1)
+           Filter: (j = 100)
+           Rows Removed In Table AM by Filter: 999
+(7 rows)
+
+SELECT ii FROM qa WHERE i = (SELECT SQRT(j)::INT FROM qb WHERE j = 100);
+ ii  
+-----
+ 100
+(1 row)
+
+EXPLAIN (ANALYZE, COSTS off, TIMING off, SUMMARY off, BUFFERS off) SELECT ii FROM qa JOIN qb ON (qa.i = qb.j) WHERE j = 100;
+                   QUERY PLAN                    
+-------------------------------------------------
+ Nested Loop (actual rows=1.00 loops=1)
+   ->  Seq Scan on qa (actual rows=1.00 loops=1)
+         Filter: (i = 100)
+         Rows Removed In Table AM by Filter: 999
+   ->  Seq Scan on qb (actual rows=1.00 loops=1)
+         Filter: (j = 100)
+         Rows Removed In Table AM by Filter: 999
+(7 rows)
+
+SELECT ii FROM qa JOIN qb ON (qa.i = qb.j) WHERE j = 100;
+  ii   
+-------
+ 10000
+(1 row)
+
+EXPLAIN (ANALYZE, COSTS off, TIMING off, SUMMARY off, BUFFERS off) SELECT ii FROM qa WHERE i = ii AND ii < 10;
+                QUERY PLAN                 
+-------------------------------------------
+ Seq Scan on qa (actual rows=1.00 loops=1)
+   Filter: ((ii < 10) AND (i = ii))
+   Rows Removed In Table AM by Filter: 997
+   Rows Removed In Executor by Filter: 2
+(4 rows)
+
+SELECT ii FROM qa WHERE i = ii AND ii < 10;
+ ii 
+----
+  1
+(1 row)
+
+DROP TABLE IF EXISTS qa;
+DROP TABLE IF EXISTS qb;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index cc6d799bcea..dc4703ec6b5 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -123,7 +123,7 @@ test: plancache limit plpgsql copy2 temp domain rangefuncs prepare conversion tr
 # The stats test resets stats, so nothing else needing stats access can be in
 # this group.
 # ----------
-test: partition_join partition_prune reloptions hash_part indexing partition_aggregate partition_info tuplesort explain compression compression_lz4 memoize stats predicate numa eager_aggregate
+test: partition_join partition_prune reloptions hash_part indexing partition_aggregate partition_info tuplesort explain compression compression_lz4 memoize stats predicate numa eager_aggregate qual_pushdown
 
 # event_trigger depends on create_am and cannot run concurrently with
 # any test that runs DDL
diff --git a/src/test/regress/sql/qual_pushdown.sql b/src/test/regress/sql/qual_pushdown.sql
new file mode 100644
index 00000000000..0f0410cd1d5
--- /dev/null
+++ b/src/test/regress/sql/qual_pushdown.sql
@@ -0,0 +1,48 @@
+DROP TABLE IF EXISTS qa;
+DROP TABLE IF EXISTS qb;
+
+CREATE TABLE qa (i INTEGER, ii INTEGER);
+CREATE TABLE qb (j INTEGER);
+INSERT INTO qa SELECT n, n * n  FROM generate_series(1, 1000) as n;
+INSERT INTO qb SELECT n FROM generate_series(1, 1000) as n;
+ANALYZE qa;
+ANALYZE qb;
+
+-- By default, the quals are not pushed down. The tuples are filtered out by
+-- the executor.
+EXPLAIN (ANALYZE, COSTS off, TIMING off, SUMMARY off, BUFFERS off) SELECT ii FROM qa WHERE i = 100;
+EXPLAIN (ANALYZE, COSTS off, TIMING off, SUMMARY off, BUFFERS off) SELECT ii FROM qa WHERE i < 10;
+EXPLAIN (ANALYZE, COSTS off, TIMING off, SUMMARY off, BUFFERS off) SELECT ii FROM qa WHERE 100 = i;
+EXPLAIN (ANALYZE, COSTS off, TIMING off, SUMMARY off, BUFFERS off) SELECT ii FROM qa WHERE 10 > i;
+EXPLAIN (ANALYZE, COSTS off, TIMING off, SUMMARY off, BUFFERS off) SELECT ii FROM qa WHERE i = SQRT(25)::INT;
+EXPLAIN (ANALYZE, COSTS off, TIMING off, SUMMARY off, BUFFERS off) SELECT ii FROM qa WHERE i = (SELECT 100);
+EXPLAIN (ANALYZE, COSTS off, TIMING off, SUMMARY off, BUFFERS off) SELECT ii FROM qa WHERE i = (SELECT SQRT(j)::INT FROM qb WHERE j = 100);
+EXPLAIN (ANALYZE, COSTS off, TIMING off, SUMMARY off, BUFFERS off) SELECT ii FROM qa JOIN qb ON (qa.i = qb.j) WHERE j = 100;
+EXPLAIN (ANALYZE, COSTS off, TIMING off, SUMMARY off, BUFFERS off) SELECT ii FROM qa WHERE i = ii AND ii < 10;
+
+-- Enable quals push down
+ALTER TABLE qa SET (quals_push_down=on);
+ALTER TABLE qb SET (quals_push_down=on);
+
+-- Now, we expect to see the tuples being filtered out by the table AM
+EXPLAIN (ANALYZE, COSTS off, TIMING off, SUMMARY off, BUFFERS off) SELECT ii FROM qa WHERE i = 100;
+SELECT ii FROM qa WHERE i = 100;
+EXPLAIN (ANALYZE, COSTS off, TIMING off, SUMMARY off, BUFFERS off) SELECT ii FROM qa WHERE i < 10;
+SELECT ii FROM qa WHERE i < 10;
+EXPLAIN (ANALYZE, COSTS off, TIMING off, SUMMARY off, BUFFERS off) SELECT ii FROM qa WHERE 100 = i;
+SELECT ii FROM qa WHERE 100 = i;
+EXPLAIN (ANALYZE, COSTS off, TIMING off, SUMMARY off, BUFFERS off) SELECT ii FROM qa WHERE 10 > i;
+SELECT ii FROM qa WHERE 10 > i;
+EXPLAIN (ANALYZE, COSTS off, TIMING off, SUMMARY off, BUFFERS off) SELECT ii FROM qa WHERE i = SQRT(25)::INT;
+SELECT ii FROM qa WHERE i = SQRT(25)::INT;
+EXPLAIN (ANALYZE, COSTS off, TIMING off, SUMMARY off, BUFFERS off) SELECT ii FROM qa WHERE i = (SELECT 100);
+SELECT ii FROM qa WHERE i = (SELECT 100);
+EXPLAIN (ANALYZE, COSTS off, TIMING off, SUMMARY off, BUFFERS off) SELECT ii FROM qa WHERE i = (SELECT SQRT(j)::INT FROM qb WHERE j = 100);
+SELECT ii FROM qa WHERE i = (SELECT SQRT(j)::INT FROM qb WHERE j = 100);
+EXPLAIN (ANALYZE, COSTS off, TIMING off, SUMMARY off, BUFFERS off) SELECT ii FROM qa JOIN qb ON (qa.i = qb.j) WHERE j = 100;
+SELECT ii FROM qa JOIN qb ON (qa.i = qb.j) WHERE j = 100;
+EXPLAIN (ANALYZE, COSTS off, TIMING off, SUMMARY off, BUFFERS off) SELECT ii FROM qa WHERE i = ii AND ii < 10;
+SELECT ii FROM qa WHERE i = ii AND ii < 10;
+
+DROP TABLE IF EXISTS qa;
+DROP TABLE IF EXISTS qb;
-- 
2.39.5


--lqhr7owlpsibhw3z
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
	filename="v4-0006-Push-down-IN-NOT-IN-array-quals-to-table-AMs.patch"