Re: HASH partitioning not working properly

On Fri, Jun 19, 2020 at 3:50 PM Srinivasa T N <seenutn(at)gmail(dot)com> wrote:
>
>
>
> On Fri, Jun 19, 2020 at 3:09 PM Amul Sul <sulamul(at)gmail(dot)com> wrote:
>>
>> On Fri, Jun 19, 2020 at 1:28 PM Srinivasa T N <seenutn(at)gmail(dot)com> wrote:
>> >
>> > On Fri, Jun 19, 2020 at 12:34 PM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> wrote:
>> >>
>> >> On Fri, 2020-06-19 at 12:12 +0530, Srinivasa T N wrote:
>> >> > On Fri, Jun 19, 2020 at 11:44 AM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
>> >> > > On Fri, 19 Jun 2020 at 17:42, Srinivasa T N <seenutn(at)gmail(dot)com> wrote:
>> >> > > > After seeing the below, I feel partitioning is not working properly or it maybe case that my understanding is wrong. Can somebody explain me what is happening?
>> >> > >
>> >> > > It's your understanding that's not correct. The value of is passed
>> >> > > through a hash function and the partition is selected based partition
>> >> > > matching the remainder value after dividing the return value of the
>> >> > > hash function by the largest modulus of any partition.
>> >> > >
>> >> > > That might surprise you, but how would you select which partition a
>> >> > > varchar value should go into if you didn't use a hash function.
>> >> > >
>> >> > > David
>> >> >
>> >> > How can I see the output of hash function that is used internally?
>> >>
>> >> In the case of "integer", the hash function is "pg_catalog"."hashint4".
>> >>
>> >> Yours,
>> >> Laurenz Albe
>> >> --
>> >> Cybertec | https://www.cybertec-postgresql.com
>> >>
>> > I guess output formatting is wrong, any help?
>> >
>> > postgres=# select pg_catalog.hashint4(7);
>> > hashint4
>> > ------------
>> > -978793473
>> > (1 row)
>> >
>> Instead of direct hash function, the easiest way to use
>> satisfies_hash_partition() what is used in defining hash
>> partitioning constraint.
>>
>> You can see the partition constraint by description partition table i.e.
>> use \d+ busbar_version5.
>>
>> Regards,
>> Amul
>
>
> Sorry, I did not get you.
>
> My current \d+ is
>
> postgres=# \d+ busbar_version6;
> Table "test.busbar_version6"
> Column | Type | Collation | Nullable | Default | Storage | Stats target |
> Description
> ----------+---------+-----------+----------+---------+---------+--------------+-
> ------------
> objectid | integer | | | | plain | |
> ver_id | integer | | | | plain | |
> Partition of: busbar_version FOR VALUES WITH (modulus 10, remainder 6)
> Partition constraint: satisfies_hash_partition('16397'::oid, 10, 6, ver_id)
> Access method: heap
>
By executing "SELECT satisfies_hash_partition('16397'::oid, 10, 6, <VALUE>) "
will tell you whether <VALUE> fits in the partition having modulus 10 and
remainder 6 or not.

Regards,
Amul