Strange (and good) side effect of partitioning ?

Поиск
Список
Период
Сортировка
От Phil Florent
Тема Strange (and good) side effect of partitioning ?
Дата
Msg-id DBAP195MB08742B862E2473EFD0D969A2BAA80@DBAP195MB0874.EURP195.PROD.OUTLOOK.COM
обсуждение исходный текст
Ответы Re: Strange (and good) side effect of partitioning ?  (rob stone <floriparob@gmail.com>)
Re: Strange (and good) side effect of partitioning ?  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-general
Дерево обсуждения
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

В списке pgsql-general по дате отправления:

Предыдущее
От: hubert depesz lubaczewski
Дата:
Сообщение: Re: postgres optimization
Следующее
От: Bret Stern
Дата:
Сообщение: Like Query help