cancel
Showing results for 
Search instead for 
Did you mean: 

table partition by hash no pruning

SOLVED
Highlighted
Gold Adventurer

table partition by hash no pruning

Hi all, i am testing hash partition in Postgres Advanced Server, and hash partition no pruning, iam missing something ?

 

table partition definitions:

 

 

CREATE TABLE test_hash
(
nom number,
md5_char varchar2

)
PARTITION BY HASH (nom)
(
PARTITION p1,
PARTITION p2,
PARTITION p3
);

 

data: 

 

insert into test_hash 
select i, md5(i::text) from generate_series(1,10000) as i ;

 

data distribution: 

 

SELECT  tableoid::regclass,count (tableoid)  FROM test_hash group by 1;

explain analyze:

 

explain analyze select * from test_hash where nom =10;

"Append  (cost=0.00..210.00 rows=4 width=45) (actual time=0.017..2.034 rows=1 loops=1)"
"  ->  Seq Scan on test_hash  (cost=0.00..0.00 rows=1 width=64) (actual time=0.003..0.003 rows=0 loops=1)"
"        Filter: (nom = '10'::numeric)"
"  ->  Seq Scan on test_hash_p1  (cost=0.00..69.33 rows=1 width=38) (actual time=0.013..0.722 rows=1 loops=1)"
"        Filter: (nom = '10'::numeric)"
"        Rows Removed by Filter: 3305"
"  ->  Seq Scan on test_hash_p2  (cost=0.00..69.41 rows=1 width=38) (actual time=0.670..0.670 rows=0 loops=1)"
"        Filter: (nom = '10'::numeric)"
"        Rows Removed by Filter: 3313"
"  ->  Seq Scan on test_hash_p3  (cost=0.00..71.26 rows=1 width=38) (actual time=0.636..0.636 rows=0 loops=1)"
"        Filter: (nom = '10'::numeric)"
"        Rows Removed by Filter: 3381"
"Planning time: 0.137 ms"
"Execution time: 2.063 ms"

the execution plan goes through all the partitions, I do not see the pruning,  i am missing something or  the hash partitioning could not pruning? 

 

partitions parameters:

 

show constraint_exclusion; 
 partition
show edb_enable_pruning;
 on

PPAS version:

 

 

select version();
"EnterpriseDB 9.6.2.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-16), 64-bit"

 

Regards

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator

Re: table partition by hash no pruning

Hi Asotolongo,


It seems that the number data type is getting handled differently at the time of pruning.

If you try passing the literal value as single quoted/string then pruning will work as expected and will get expected result.

enterprisedb@edb  explain analyze select * from test_hash where nom ='10';
                                                  QUERY PLAN                                                  
--------------------------------------------------------------------------------------------------------------
 Append  (cost=0.00..69.33 rows=2 width=50) (actual time=0.014..0.497 rows=1 loops=1)
   ->  Seq Scan on test_hash  (cost=0.00..0.00 rows=1 width=64) (actual time=0.002..0.002 rows=0 loops=1)
         Filter: (nom = '10'::numeric)
   ->  Seq Scan on test_hash_p1  (cost=0.00..69.33 rows=1 width=38) (actual time=0.011..0.494 rows=1 loops=1)
         Filter: (nom = '10'::numeric)
         Rows Removed by Filter: 3305
 Planning time: 0.070 ms
 Execution time: 0.514 ms
(8 rows)


I tried creating the table with integer type as a partition key as follows and pruning is working.

Please check below test case,

enterprisedb@edb  CREATE TABLE test_hash1
(                                                          
nom integer,
md5_char varchar2
)
PARTITION BY HASH (nom)
(
PARTITION p1,
PARTITION p2,
PARTITION p3
);
CREATE TABLE
enterprisedb@edb  insert into test_hash1
select i, md5(i::text) from generate_series(1,10000) as i ;
INSERT 0 10000
enterprisedb@edb  explain analyze select * from test_hash1 where nom =10;
                                                  QUERY PLAN                                                   
---------------------------------------------------------------------------------------------------------------
 Append  (cost=0.00..71.38 rows=2 width=36) (actual time=0.011..0.425 rows=1 loops=1)
   ->  Seq Scan on test_hash1  (cost=0.00..0.00 rows=1 width=36) (actual time=0.004..0.004 rows=0 loops=1)
         Filter: (nom = 10)
   ->  Seq Scan on test_hash1_p3  (cost=0.00..71.38 rows=1 width=37) (actual time=0.007..0.420 rows=1 loops=1)
         Filter: (nom = 10)
         Rows Removed by Filter: 3389
 Planning time: 0.213 ms
 Execution time: 0.444 ms
(8 rows)

View solution in original post

2 REPLIES 2
Moderator

Re: table partition by hash no pruning

Hi Asotolongo,


It seems that the number data type is getting handled differently at the time of pruning.

If you try passing the literal value as single quoted/string then pruning will work as expected and will get expected result.

enterprisedb@edb  explain analyze select * from test_hash where nom ='10';
                                                  QUERY PLAN                                                  
--------------------------------------------------------------------------------------------------------------
 Append  (cost=0.00..69.33 rows=2 width=50) (actual time=0.014..0.497 rows=1 loops=1)
   ->  Seq Scan on test_hash  (cost=0.00..0.00 rows=1 width=64) (actual time=0.002..0.002 rows=0 loops=1)
         Filter: (nom = '10'::numeric)
   ->  Seq Scan on test_hash_p1  (cost=0.00..69.33 rows=1 width=38) (actual time=0.011..0.494 rows=1 loops=1)
         Filter: (nom = '10'::numeric)
         Rows Removed by Filter: 3305
 Planning time: 0.070 ms
 Execution time: 0.514 ms
(8 rows)


I tried creating the table with integer type as a partition key as follows and pruning is working.

Please check below test case,

enterprisedb@edb  CREATE TABLE test_hash1
(                                                          
nom integer,
md5_char varchar2
)
PARTITION BY HASH (nom)
(
PARTITION p1,
PARTITION p2,
PARTITION p3
);
CREATE TABLE
enterprisedb@edb  insert into test_hash1
select i, md5(i::text) from generate_series(1,10000) as i ;
INSERT 0 10000
enterprisedb@edb  explain analyze select * from test_hash1 where nom =10;
                                                  QUERY PLAN                                                   
---------------------------------------------------------------------------------------------------------------
 Append  (cost=0.00..71.38 rows=2 width=36) (actual time=0.011..0.425 rows=1 loops=1)
   ->  Seq Scan on test_hash1  (cost=0.00..0.00 rows=1 width=36) (actual time=0.004..0.004 rows=0 loops=1)
         Filter: (nom = 10)
   ->  Seq Scan on test_hash1_p3  (cost=0.00..71.38 rows=1 width=37) (actual time=0.007..0.420 rows=1 loops=1)
         Filter: (nom = 10)
         Rows Removed by Filter: 3389
 Planning time: 0.213 ms
 Execution time: 0.444 ms
(8 rows)

View solution in original post

Gold Adventurer

Re: table partition by hash no pruning

Thanks Ranjeet, effectively number data type is handled differently,

i test with text, numeric, date and timestamp and work fine

 

 

Regards