Constant folding and expression handling

PostgreSQL's cost-based optimizer rewrites queries before they run, and constant folding is the simplest of those rewrites. A filter such as x = 7 + 1 becomes x = 8, which matters because when x is indexed, the value 8 can be looked up directly in the index.

PgSQL

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

demo=# SELECT *

           FROM generate_series(1, 10) AS x

           WHERE x = 7 + 1;

x

---

8

(1 row)

demo=# explain SELECT *

           FROM generate_series(1, 10) AS x

           WHERE x = 7 + 1;

                            QUERY PLAN

----------------------------------------------------------------------

Function Scan on generate_series x (cost=0.00..0.13 rows=1 width=4)

    Filter: (x = 8)

(2 rows)

The rewrite is not universal. In the following case the expression is left alone:

PgSQL

1

2

3

4

5

6

7

8

demo=# explain SELECT *

           FROM  generate_series(1, 10) AS x

           WHERE x - 1 = 7 ;

                            QUERY PLAN

----------------------------------------------------------------------

Function Scan on generate_series x (cost=0.00..0.15 rows=1 width=4)

    Filter: ((x - 1) = 7)

(2 rows)

The optimizer does not turn that into x = 8. Keep the computed expression on the right side of the comparison, not on the indexed column.

Function inlining

Inlining removes function calls from the execution path. A SQL function that computes a logarithm:

PgSQL

1

2

3

4

5

6

7

8

9

10

11

12

demo=# CREATE OR REPLACE FUNCTION ld(int)

           RETURNS numeric AS

$

      SELECT log(2, $1);

$ LANGUAGE 'sql';

CREATE FUNCTION

demo=# SELECT ld(1024);

         ld

---------------------

10.0000000000000000

(1 row)

That returns the expected value for 2^10 = 1024. When the function is used in a query:

PgSQL

1

2

3

4

5

6

7

8

demo=# explain SELECT *

           FROM generate_series(1, 10) AS x

           WHERE ld(x) = 1000;

                            QUERY PLAN

----------------------------------------------------------------------

Function Scan on generate_series x (cost=0.00..0.18 rows=1 width=4)

   Filter: (log('2'::numeric, (x)::numeric) = '1000'::numeric)

(2 rows)

The WHERE clause shows ld replaced by the underlying log call. This only works for SQL functions — PL/pgSQL and other procedural languages are opaque to the optimizer.

PgSQL

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

demo=# CREATE OR REPLACE FUNCTION pl_ld(int)

           RETURNS numeric AS

$

     BEGIN

                 RETURN log(2, $1);

     END;

$ LANGUAGE 'plpgsql';

CREATE FUNCTION

demo=# explain SELECT *

           FROM generate_series(1, 10) AS x

           WHERE pl_ld(x) = 1000;

                            QUERY PLAN

----------------------------------------------------------------------

  Function Scan on generate_series x (cost=0.00..2.63 rows=1 width=4)

   Filter: (pl_ld(x) = '1000'::numeric)

(2 rows)

The PL/pgSQL version is logically identical but cannot be inlined. The language choice alone decides whether the rewrite is available.

Function stability and index use

Whether a function is declared VOLATILE (the default), STABLE, or IMMUTABLE can change performance dramatically, particularly where indexes are involved. VOLATILE gives no guarantee that the same inputs return the same result within a transaction, so the planner cannot treat the call as a constant and must invoke it once per row.

Sample data:

PgSQL

1

2

3

4

5

6

7

demo=# CREATE TABLE t_date AS

           SELECT *

           FROM   generate_series('1900-01-01'::timestamptz,

                    '2021-12-31'::timestamptz, '1 minute') AS x;

SELECT 64164961

demo=# CREATE INDEX idx_date ON t_date (x);

CREATE INDEX

The table holds one row per minute since January 1900 and therefore 64 million entries. Running against a VOLATILE function:

PgSQL

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

demo=# explain analyze SELECT *

           FROM t_date

           WHERE x = clock_timestamp();

                          QUERY PLAN

-------------------------------------------------------------------

Gather (cost=1000.00..685947.45 rows=1 width=8)

        (actual time=2656.961..2658.547 rows=0 loops=1)

  Workers Planned: 2

  Workers Launched: 2

  -> Parallel Seq Scan on t_date

         (cost=0.00..684947.35 rows=1 width=8)

         (actual time=2653.009..2653.009 rows=0 loops=3)

     Filter: (x = clock_timestamp())

     Rows Removed by Filter: 21388320

Planning Time: 0.056 ms

Execution Time: 2658.562 ms

(8 rows)

That query takes 2.6 seconds and consumes substantial resources, because clock_timestamp() is VOLATILE. With a STABLE function instead:

PgSQL

1

2

3

4

5

6

7

8

9

10

11

12

13

demo=# explain analyze SELECT *

           FROM t_date

           WHERE x = now();

                        QUERY PLAN

---------------------------------------------------------------

Index Only Scan using idx_date on t_date

      (cost=0.57..4.59 rows=1 width=8)

      (actual time=0.014..0.015 rows=0 loops=1)

    Index Cond: (x = now())

    Heap Fetches: 0

Planning Time: 0.060 ms

Execution Time: 0.026 ms

(5 rows)

The query is many thousands of times faster: PostgreSQL can fold the call into a constant and use the index.

Equality constraints

Here the optimizer derives facts the query never states explicitly. Sample data:

PgSQL

1

2

3

4

5

6

7

8

9

10

11

12

13

demo=# CREATE TABLE t_demo AS

           SELECT x, x AS y

           FROM generate_series(1, 1000) AS x;

SELECT 1000

demo=# SELECT * FROM t_demo LIMIT 5;

x  | y

---+---

1  | 1

2  | 2

3  | 3

4  | 4

5  | 5

(5 rows)

Against those 1000 rows:

PgSQL

1

2

3

4

5

6

7

8

9

demo=# explain SELECT *

               FROM t_demo

               WHERE x = y

                     AND y = 4;

                   QUERY PLAN

-------------------------------------------------------

Seq Scan on t_demo (cost=0.00..20.00 rows=1 width=8)

   Filter: ((x = 4) AND (y = 4))

(2 rows)

The execution plan shows that PostgreSQL has deduced x and y are both 4. That deduction enables further plans:

PgSQL

1

2

3

4

5

6

7

8

9

10

11

12

demo=# CREATE INDEX idx_x ON t_demo (x);

CREATE INDEX

demo=# explain SELECT *

                FROM t_demo

                WHERE x = y

                      AND y = 4;

                           QUERY PLAN

--------------------------------------------------------------------

Index Scan using idx_x on t_demo (cost=0.28..8.29 rows=1 width=8)

    Index Cond: (x = 4)

    Filter: (y = 4)

(3 rows)

Without it, the new index would be unusable. The optimizer works out on its own that an index scan applies.

Indexes are important!

Views and subselect flattening

A view is inlined as a subselect and then flattened. Create and query one:

PgSQL

1

2

3

4

5

6

7

8

9

10

11

12

13

14

demo=# CREATE VIEW v1 AS

           SELECT *

           FROM   generate_series(1, 10) AS x;

CREATE VIEW

demo=# explain SELECT *

           FROM v1

           ORDER BY x DESC;

                QUERY PLAN

----------------------------------------------

Sort (cost=0.27..0.29 rows=10 width=4)

    Sort Key: x.x DESC

    -> Function Scan on generate_series x

       (cost=0.00..0.10 rows=10 width=4)

(3 rows)

The execution plan contains no sign of the view. The sequence of rewrites looks like this:

PgSQL

1

2

3

SELECT *

          FROM v1

          ORDER BY x DESC;

That query becomes:

PgSQL

1

2

3

4

5

SELECT *

          FROM (SELECT *

                FROM generate_series(1, 10) AS x

               ) AS v1

         ORDER BY x DESC;

And flattening the subselect leaves:

PgSQL

1

2

3

SELECT *

FROM   generate_series(1, 10) AS x

ORDER BY x DESC;

The from_collapse_limit parameter governs how far this goes:

PgSQL

1

2

3

4

5

demo=# SHOW from_collapse_limit;

from_collapse_limit

---------------------

8

(1 row)

Only up to 8 subselects in the FROM clause are flattened; beyond that they are executed as written. Real-world queries rarely hit the limit — it takes very complex SQL to do so. Note that inlining is not always possible, something the developers are aware of.

Join order and join syntax

PostgreSQL does not have to join tables in the order the user wrote. The optimizer enumerates as many join orders as it can. Given sample data:

PgSQL

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

demo=# CREATE TABLE a AS

     SELECT x, x % 10 AS y

     FROM generate_series(1, 100000) AS x

     ORDER BY random();

SELECT 100000

demo=# CREATE TABLE b AS

     SELECT x, x % 10 AS y

     FROM generate_series(1, 1000000) AS x

     ORDER BY random();

SELECT 1000000

demo=# CREATE TABLE c AS

     SELECT x, x % 10 AS y

     FROM generate_series(1, 10000000) AS x

     ORDER BY random();

SELECT 10000000

and the indexes:

PgSQL

1

2

3

4

5

6

7

8

demo=# CREATE INDEX a_x ON a(x);

CREATE INDEX

demo=# CREATE INDEX b_x ON b(x);

CREATE INDEX

demo=# CREATE INDEX c_x ON c(x);

CREATE INDEX

demo=# ANALYZE;

ANALYZE

a three-table query runs as:

PgSQL

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

demo=# explain SELECT *

               FROM  a, b, c

               WHERE c.x = a.x

                     AND a.x = b.x

                     AND a.x = 10;

                        QUERY PLAN

--------------------------------------------------------------------

Nested Loop (cost=1.15..25.23 rows=1 width=24)

  -> Nested Loop (cost=0.72..16.76 rows=1 width=16)

    -> Index Scan using a_x on a

         (cost=0.29..8.31 rows=1 width=8)

         Index Cond: (x = 10)

    -> Index Scan using b_x on b

         (cost=0.42..8.44 rows=1 width=8)

         Index Cond: (x = 10)

    -> Index Scan using c_x on c

         (cost=0.43..8.45 rows=1 width=8)

     Index Cond: (x = 10)

(8 rows)

The statement joins c with a and then a with b, but the plan starts with index scans on a and b and joins the result with c. Three indexes are used, thanks to the equality constraints described above.

On syntax, implicit and explicit joins are the same thing:

PgSQL

1

2

3

SELECT * FROM a, b WHERE a.id = b.id;

vs.

SELECT * FROM a JOIN b ON a.id = b.id;

Both queries are identical and the planner treats them identically for the commonly seen cases. Explicit joins work with or without parentheses. One parameter matters here, join_collapse_limit:

PgSQL

1

2

3

4

5

demo=# SHOW join_collapse_limit;

join_collapse_limit

---------------------

8

(1 row)

It controls how many explicit joins are planned implicitly — an implicit join behaves like an explicit one only up to that number. For simplicity, it makes no difference for 95% of queries and most customers.

Outer join reordering

Outer joins (LEFT JOIN, RIGHT JOIN, and so on) give the planner fewer options than inner joins. The permitted transformations are:

PgSQL

1

2

(A leftjoin B on (Pab)) innerjoin C on (Pac)

     = (A innerjoin C on (Pac)) leftjoin B on (Pab)

where Pac is a predicate referencing A and C — Pac cannot reference B, or the transformation makes no sense.

PgSQL

1

2

3

4

5

(A leftjoin B on (Pab)) leftjoin C on (Pac)

     = (A leftjoin C on (Pac)) leftjoin B on (Pab)

(A leftjoin B on (Pab)) leftjoin C on (Pbc)

     = A leftjoin (B leftjoin C on (Pbc)) on (Pab)

In practice:

PgSQL

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

demo=# explain SELECT *

      FROM generate_series(1, 10) AS x

                 LEFT JOIN generate_series(1, 100) AS y

                       ON (x = y)

                 JOIN generate_series(1, 10000) AS z

                       ON (y = z)

;

                           QUERY PLAN

--------------------------------------------------------------------

Hash Join (cost=1.83..144.33 rows=500 width=12)

    Hash Cond: (z.z = x.x)

    -> Function Scan on generate_series z

          (cost=0.00..100.00 rows=10000 width=4)

    -> Hash (cost=1.71..1.71 rows=10 width=8)

      -> Hash Join (cost=0.23..1.71 rows=10 width=8)

           Hash Cond: (y.y = x.x)

           -> Function Scan on generate_series y

                (cost=0.00..1.00 rows=100 width=4)

           -> Hash (cost=0.10..0.10 rows=10 width=4)

                 -> Function Scan on generate_series x

                      (cost=0.00..0.10 rows=10 width=4)

(9 rows)

The optimizer joins x with y and then z, following the order in the SQL statement. Changing the parameters slightly:

PgSQL

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

demo=# explain SELECT *

     FROM generate_series(1, 10) AS x

                LEFT JOIN generate_series(1, 100000) AS y

                      ON (x = y)

                JOIN generate_series(1, 100) AS z

                      ON (y = z)

;

                           QUERY PLAN

--------------------------------------------------------------------

Hash Join (cost=1.83..1426.83 rows=5000 width=12)

   Hash Cond: (y.y = x.x)

   -> Function Scan on generate_series y

          (cost=0.00..1000.00 rows=100000 width=4)

   -> Hash (cost=1.71..1.71 rows=10 width=8)

     -> Hash Join (cost=0.23..1.71 rows=10 width=8)

               Hash Cond: (z.z = x.x)

               -> Function Scan on generate_series z

                    (cost=0.00..1.00 rows=100 width=4)

               -> Hash (cost=0.10..0.10 rows=10 width=4)

                    -> Function Scan on generate_series x

                         (cost=0.00..0.10 rows=10 width=4)

(9 rows)

Now PostgreSQL still starts with x but joins z before adding y. This happens automatically, and one reason it is possible is optimizer support functions, added to PostgreSQL some time ago: they let the planner estimate how many rows each part returns. Support functions are irrelevant when tables rather than set-returning functions are used. PostgreSQL v16 added support for anti-joins in RIGHT and OUTER queries.

Join pruning and anti-joins

The optimizer can discard joins that produce nothing. Sample data:

PgSQL

1

2

3

4

5

6

7

8

9

10

demo=# CREATE TABLE t_left AS SELECT *

           FROM generate_series(1, 1000) AS id;

SELECT 1000

demo=# CREATE UNIQUE INDEX idx_left ON t_left (id);

CREATE INDEX

demo=# CREATE TABLE t_right AS SELECT *

           FROM generate_series(1, 100) AS id;

SELECT 100

demo=# CREATE UNIQUE INDEX idx_right ON t_right (id);

CREATE INDEX

Both sides need primary keys or an equivalent unique constraint:

PgSQL

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

demo=# d t_left

Table 'public.t_left'

Column  | Type    | Collation | Nullable | Default

--------+---------+-----------+----------+---------

id      | integer |           |          |

Indexes:

     'idx_left' UNIQUE, btree (id)

demo=# d t_right

Table 'public.t_right'

Column  | Type    | Collation | Nullable | Default

--------+---------+-----------+----------+---------

id      | integer |           |          |

Indexes:

     'idx_right' UNIQUE, btree (id)

Two variations show the difference:

PgSQL

1

2

3

4

5

6

7

8

9

10

11

demo=# explain SELECT *

           FROM t_left AS a LEFT JOIN t_right AS b

                ON (a.id = b.id);

                          QUERY PLAN

--------------------------------------------------------------------

Hash Left Join (cost=3.25..20.89 rows=1000 width=8)

  Hash Cond: (a.id = b.id)

  -> Seq Scan on t_left a (cost=0.00..15.00 rows=1000 width=4)

  -> Hash (cost=2.00..2.00 rows=100 width=4)

    -> Seq Scan on t_right b (cost=0.00..2.00 rows=100 width=4)

(5 rows)

Here the join must run, and PostgreSQL picks a hash join. A small change to the query:

PgSQL

1

2

3

4

5

6

7

demo=# explain SELECT a.*

           FROM t_left AS a LEFT JOIN t_right AS b

                ON (a.id = b.id);

                      QUERY PLAN

------------------------------------------------------------

Seq Scan on t_left a (cost=0.00..15.00 rows=1000 width=4)

(1 row)

Pruning requires that no data is read from the right side and that the right side is unique — if it were not unique, the join could multiply rows. Trying it out:

PgSQL

1

2

3

4

5

6

7

8

9

10

11

12

13

14

demo=# DROP INDEX idx_right ;

DROP INDEX

demo=# explain SELECT a.*

           FROM t_left AS a LEFT JOIN t_right AS b

                ON (a.id = b.id);

                         QUERY PLAN

--------------------------------------------------------------------

Hash Left Join (cost=3.25..23.00 rows=1000 width=4)

   Hash Cond: (a.id = b.id)

   -> Seq Scan on t_left a (cost=0.00..15.00 rows=1000 width=4)

   -> Hash (cost=2.00..2.00 rows=100 width=4)

     -> Seq Scan on t_right b (cost=0.00..2.00 rows=100 width=4)

(5 rows)

Pruning is a useful safety net, but it is the planner cleaning up a join that should not have been written. Write efficient queries in the first place.

EXISTS

A common pattern:

PgSQL

1

2

3

4

5

6

7

8

9

10

11

12

13

14

demo=# explain SELECT * FROM a

           WHERE NOT EXISTS (SELECT 1 FROM b

                 WHERE a.x = b.x

                 AND b.x = 42);

                            QUERY PLAN

-------------------------------------------------------------------

Hash Anti Join (cost=4.46..2709.95 rows=100000 width=8)

Hash Cond: (a.x = b.x)

-> Seq Scan on a (cost=0.00..1443.00 rows=100000 width=8)

-> Hash (cost=4.44..4.44 rows=1 width=4)

      -> Index Only Scan using b_x on b

         (cost=0.42..4.44 rows=1 width=4)

            Index Cond: (x = 42)

(6 rows)

PostgreSQL turns this into a hash anti-join, which is considerably more efficient than a nested loop. The nested loop is replaced by a join, and the gain can be significant.

Sorted index output as a substitute for sorting

Sorting underpins a large share of query processing, and one of the notable optimizations PostgreSQL applies in this area is using an index to satisfy ORDER BY. To demonstrate, the listing below builds a table of 1 million entries stored on disk in random order; VACUUM then runs so that hint bit-related bookkeeping is settled before the test executes.

1

2

3

4

5

6

7

demo=# CREATE TABLE t_sample AS

SELECT *

FROM generate_series(1, 1000000) AS id

ORDER BY random();

SELECT 1000000

demo=# VACUUM ANALYZE t_sample ;

VACUUM

Running EXPLAIN ANALYZE on a plain ordered read shows the planner relying on more than one core to finish the query in 86 milliseconds — a substantial cost.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

demo=# explain analyze SELECT *

           FROM t_sample

           ORDER BY id DESC

           LIMIT 100;

                        QUERY PLAN

-------------------------------------------------------------------

Limit (cost=25516.40..25528.07 rows=100 width=4)

     (actual time=84.806..86.252 rows=100 loops=1)

   -> Gather Merge (cost=25516.40..122745.49 rows=833334 width=4)

          (actual time=84.805..86.232 rows=100 loops=1)

     Workers Planned: 2

     Workers Launched: 2

     -> Sort (cost=24516.38..25558.05 rows=416667 width=4)

              (actual time=65.576..65.586 rows=82 loops=3)

              Sort Key: id DESC

              Sort Method: top-N heapsort Memory: 33kB

              Worker 0: Sort Method: top-N heapsort Memory: 32kB

              Worker 1: Sort Method: top-N heapsort Memory: 33kB

              -> Parallel Seq Scan on t_sample

                   (cost=0.00..8591.67 rows=416667 width=4)

                   (actual time=0.428..33.305 rows=333333 loops=3)

Planning Time: 0.078 ms

Execution Time: 86.286 ms

(12 rows)

Add an index over the sort key and the picture changes.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

demo=# CREATE INDEX idx_sample ON t_sample (id);

CREATE INDEX

demo=# explain analyze SELECT *

           FROM t_sample

           ORDER BY id DESC

           LIMIT 100;

                           QUERY PLAN

-------------------------------------------------------------------

Limit (cost=0.42..3.02 rows=100 width=4)

       (actual time=0.071..0.125 rows=100 loops=1)

   -> Index Only Scan Backward using idx_sample on t_sample

          (cost=0.42..25980.42 rows=1000000 width=4)

          (actual time=0.070..0.113 rows=100 loops=1)

     Heap Fetches: 0

Planning Time: 0.183 ms

Execution Time: 0.142 ms

(5 rows)

Execution drops to a fraction of a millisecond, but the more interesting detail is the absence of a sort step in the plan. The optimizer knows the index delivers rows already ordered by id, so there is no reason to sort again: it reads from the index and streams rows to the client until enough have been produced. When the query only requests columns present in the index, an index-only scan becomes possible as well. Since trimming the volume of data that must be sorted directly affects performance and the user experience, this matters beyond the benchmark.

Aggregates that never sort

The same reasoning covers min and max. The minimum is the first non-NULL value in a sorted list; the maximum is the last non-NULL value in a sorted sequence. Recognizing this, the planner can discard the usual aggregate machinery and replace it with a subplan that reads the index directly.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

demo=# explain SELECT min(id), max(id) FROM t_sample;

                        QUERY PLAN

-------------------------------------------------------------------

Result (cost=0.91..0.92 rows=1 width=8)

   InitPlan 1 (returns $0)

      -> Limit (cost=0.42..0.45 rows=1 width=4)

           -> Index Only Scan using idx_sample on t_sample

               (cost=0.42..28480.42 rows=1000000 width=4)

               Index Cond: (id IS NOT NULL)

   InitPlan 2 (returns $1)

      -> Limit (cost=0.42..0.45 rows=1 width=4)

           -> Index Only Scan Backward using

               idx_sample on t_sample t_sample_1

                (cost=0.42..28480.42 rows=1000000 width=4)

                Index Cond: (id IS NOT NULL)

(9 rows)

In effect, asking for min resembles the query below — and PostgreSQL performs this rewrite on its own, optimizing the statement without being asked.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

demo=# SELECT id

           FROM t_sample

           WHERE id IS NOT NULL

           ORDER BY id

           LIMIT 1;

id

----

1

(1 row)

demo=# explain SELECT id

           FROM t_sample

           WHERE id IS NOT NULL

           ORDER BY id

           LIMIT 1;

                         QUERY PLAN

-------------------------------------------------------------------

Limit (cost=0.42..0.45 rows=1 width=4)

   -> Index Only Scan using idx_sample on t_sample

          (cost=0.42..28480.42 rows=1000000 width=4)

     Index Cond: (id IS NOT NULL)

(3 rows)

Partition pruning and constraint exclusion

Partitioning lets users shrink table sizes and divide data into smaller chunks that are easier to manage and, in many cases, faster to query. That benefit is not universal: planning time grows accordingly and can cancel out the gain, and partitioning has repeatedly been observed to slow workloads down when it was not a suitable fit.

The optimizer therefore has to reason about partitioning carefully and touch only those partitions that could hold matching data. The example below creates a table with two partitions.

1

2

3

4

5

6

7

8

9

demo=# CREATE TABLE mynumbers (id int)

           PARTITION BY RANGE (id);

CREATE TABLE

demo=# CREATE TABLE negatives

           PARTITION OF mynumbers FOR VALUES FROM (MINVALUE) TO (0);

CREATE TABLE

demo=# CREATE TABLE positives

           PARTITION OF mynumbers FOR VALUES FROM (1) TO (MAXVALUE);

CREATE TABLE

Querying for positive values only, the planner determines that one partition cannot contain the data and removes it from the execution plan.

1

2

3

4

5

6

7

demo=# explain SELECT * FROM mynumbers WHERE id > 1000;

             QUERY PLAN

------------------------------------------

Seq Scan on positives mynumbers

      (cost=0.00..41.88 rows=850 width=4)

   Filter: (id > 1000)

(2 rows)

A condition such as all values below 1000, by contrast, offers no such exclusion — both partitions are correctly scanned.

1

2

3

4

5

6

7

8

9

10

11

demo=# explain SELECT * FROM mynumbers WHERE id < 1000;

QUERY PLAN

--------------------------------------------------------

Append (cost=0.00..92.25 rows=1700 width=4)

   -> Seq Scan on negatives mynumbers_1

          (cost=0.00..41.88 rows=850 width=4)

     Filter: (id < 1000)

   > Seq Scan on positives mynumbers_2

          (cost=0.00..41.88 rows=850 width=4)

     Filter: (id < 1000)

(5 rows)