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