Strange (and good) side effect of partitioning ?

Hi,

I read that on Jonathan Lewis' blog :

(I believe that there may be some RDBMS which will treat (e.g.) “X between 20 and 10” as being identical to “X between 10 and 20” )

I am puzzled. PostgreSQL seems NOT to treat X between 20 and 10” as being identical to “X between 10 and 20" but it's complicated.

Here is my test case:

select version();
version
----------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 13.1 (Ubuntu 13.1-1.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit

create table t1 (rn integer , object_name text) partition by range(rn);

create table t1a partition of t1 for values from (1) to (50001);

\d+ t1
Table partitionnée « public.t1 »
Colonne | Type | Collationnement | NULL-able | Par défaut | Stockage | Cible de statistiques | Description
-------------+---------+-----------------+-----------+------------+----------+-----------------------+-------------
rn | integer | | | | plain | |
object_name | text | | | | extended | |
Clé de partition : RANGE (rn)
Partitions: t1a FOR VALUES FROM (1) TO (50001)

insert into t1 select
rownum rn,
upper(md5(random()::text)) object_name
from
(select generate_series(1,50000) rownum) serie
;

explain analyze select object_name
from t1
where
rn between 20 and 10
;
QUERY PLAN
------------------------------------------------------------------------------------
Result (cost=0.00..0.00 rows=0 width=0) (actual time=0.002..0.002 rows=0 loops=1)
One-Time Filter: false
Planning Time: 0.116 ms
Execution Time: 0.020 ms

It's OK but:

explain analyze select object_name
from t1a
where
rn between 20 and 10
;
QUERY PLAN
-------------------------------------------------------------------------------------------------
Seq Scan on t1a (cost=0.00..1167.00 rows=1 width=33) (actual time=6.553..6.553 rows=0 loops=1)
Filter: ((rn >= 20) AND (rn <= 10))
Rows Removed by Filter: 50000
Planning Time: 0.092 ms
Execution Time: 6.573 ms

At first I thought it was related to partition pruning but:

set enable_partition_pruning = false;

explain analyze select object_name
from t1
where
rn between 20 and 10
;

QUERY PLAN
------------------------------------------------------------------------------------
Result (cost=0.00..0.00 rows=0 width=0) (actual time=0.002..0.003 rows=0 loops=1)
One-Time Filter: false
Planning Time: 0.104 ms
Execution Time: 0.021 ms

Confirmation since I still obtain "One-Time Filter: false" if I don't filter on the partition key:

create table t2 (rn integer , rn2 integer, object_name text) partition by range(rn);

create table t2a partition of t2 for values from (1) to (50001);

d+ t2
Table partitionnée « public.t2 »
Colonne | Type | Collationnement | NULL-able | Par défaut | Stockage | Cible de statistiques | Description
-------------+---------+-----------------+-----------+------------+----------+-----------------------+-------------
rn | integer | | | | plain | |
rn2 | integer | | | | plain | |
object_name | text | | | | extended | |
Clé de partition : RANGE (rn)
Partitions: t2a FOR VALUES FROM (1) TO (50001)

insert into t2 select
rownum rn, rownum rn2,
upper(md5(random()::text)) object_name
from
(select generate_series(1,50000) rownum) serie
;

explain analyze select object_name
from t2
where
rn2 between 20 and 10
;

QUERY PLAN
------------------------------------------------------------------------------------
Result (cost=0.00..0.00 rows=0 width=0) (actual time=0.002..0.003 rows=0 loops=1)
One-Time Filter: false
Planning Time: 0.185 ms
Execution Time: 0.019 ms

I don't understand why I don't obtain " One-Time Filter: false" with a classic table or a partition ?

Best regards,

Phil