Re: When to use PARTITION BY HASH?

On Tue, Jun 2, 2020 at 7:33 PM Justin Pryzby <pryzby(at)telsasoft(dot)com> wrote:

> > To: pgsql-general(at)lists(dot)postgresql(dot)org,
> pgsql-performance(at)lists(dot)postgresql(dot)org
>
> Please don't cross post to multiple lists.
>
> On Tue, Jun 02, 2020 at 07:17:11PM +0200, Oleksandr Shulgin wrote:
> > I was reading up on declarative partitioning[1] and I'm not sure what
> could
> > be a possible application of Hash partitioning.
>
> It's a good question. See Tom's complaint here.
> https://www.postgresql.org/message-id/31605.1586112900%40sss.pgh.pa.us
>
> It *does* provide the benefit of smaller indexes and smaller tables, which
> might allow seq scans to outpeform index scans.
>
> It's maybe only useful for equality conditions on the partition key, and
> not
> for ranges. Here, it scans a single partition:
>
> postgres=# CREATE TABLE t(i int) PARTITION BY HASH(i); CREATE TABLE t1
> PARTITION OF t FOR VALUES WITH (REMAINDER 0, MODULUS 3);
> postgres=# CREATE TABLE t2 PARTITION OF t FOR VALUES WITH (MODULUS 3,
> REMAINDER 1);
> postgres=# CREATE TABLE t3 PARTITION OF t FOR VALUES WITH (MODULUS 3,
> REMAINDER 2);
> postgres=# INSERT INTO t SELECT i%9 FROM generate_series(1,9999)i; ANALYZE
> t;
> postgres=# explain analyze SELECT * FROM t WHERE i=3;
> Seq Scan on t2 (cost=0.00..75.55 rows=2222 width=4) (actual
> time=0.021..0.518 rows=2222 loops=1)
> Filter: (i = 3)
> Rows Removed by Filter: 2222
>

I see. So it works with low cardinality in the partitioned column. With
high cardinality an index scan on an unpartitioned table would be
preferable I guess.

The documentation page I've linked only contains examples around
partitioning BY RANGE. I believe it'd be helpful to extend it with some
meaningful examples for LIST and HASH partitioning.

Regards,
--
Alex