Search Postgresql Archives

Strange (and good) side effect of partitioning ?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux