Hi, On Thu, 2021-01-14 at 20:48 +0000, Phil Florent wrote: > 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_namefrom 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_namefrom 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_namefrom 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_namefrom 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 See table 9.2 in the documentation. BETWEEN 10 AND 20 returns TRUE. BETWEEN 20 AND 10 returns FALSE. BETWEEN SYMMETRIC 20 AND 10 returns TRUE. HTH,
