Re: Strange (and good) side effect of partitioning ?

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,