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
