Re: HASH partitioning not working properly

On Fri, Jun 19, 2020, 5:45 PM Amul Sul <sulamul(at)gmail(dot)com> wrote:

> 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
>

OK.. Thanks.

BTW, is it possible to have a custom hash function instead of predefined
hash function?

Regards,
Seenu.

>