How does multi column hash partition work in postgresql

Viewed 21
create or replace function part_hashint4_noop(value int4, seed int8)
returns int8 as $$
select value + seed;
$$ language sql immutable;

create operator class part_test_int4_ops
for type int4
using hash as
operator 1 =,
function 2 part_hashint4_noop(int4, int8);

create or replace function part_hashtext_length(value text, seed int8)
RETURNS int8 AS $$
select length(coalesce(value, ''))::int8
$$ language sql immutable;

create operator class part_test_text_ops
for type text
using hash as
operator 1 =,
function 2 part_hashtext_length(text, int8);

begin;
create table hp(a int,b text, c int)
      partition by hash(a part_test_int4_ops, b part_test_text_ops);
create table hp0 partition of hp for values with (modulus 4, remainder 0);      
create table hp1 partition of hp for values with (modulus 4, remainder 1);      
create table hp2 partition of hp for values with (modulus 4, remainder 2);      
create table hp3 partition of hp for values with (modulus 4, remainder 3);      
commit;

Then trying to understand partition pruning of following query plan.

explain (costs off) select * from hp where a = 1 and b = 'xxx';
                QUERY PLAN                 
-------------------------------------------
 Seq Scan on hp0 hp
   Filter: ((a = 1) AND (b = 'xxx'::text))
(2 rows)

 explain (costs off) select * from hp where a = 2 and b = 'xxx';
                QUERY PLAN                 
-------------------------------------------
 Seq Scan on hp3 hp
   Filter: ((a = 2) AND (b = 'xxx'::text))
(2 rows)

function part_hashtext_length is to get the string length. null value will return 0.
Only found this related post
code Source

0 Answers
Related