From: Justin Pryzby <pryzby@telsasoft.com>
To: Oleksandr Shulgin <oleksandr.shulgin@zalando.de>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: When to use PARTITION BY HASH?
Date: Tue, 2 Jun 2020 12:33:54 -0500
Message-ID: <20200602173353.GB30144@telsasoft.com> (raw)
In-Reply-To: <CACACo5StuUuFox+65KQHB8u+51kMUcEeQuV=XmsUv7d8VnaO7A@mail.gmail.com>
References: <CACACo5StuUuFox+65KQHB8u+51kMUcEeQuV=XmsUv7d8VnaO7A@mail.gmail.com>
> To: pgsql-general@lists.postgresql.org, pgsql-performance@lists.postgresql.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
--
Justin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pryzby@telsasoft.com, oleksandr.shulgin@zalando.de, pgsql-performance@lists.postgresql.org
Subject: Re: When to use PARTITION BY HASH?
In-Reply-To: <20200602173353.GB30144@telsasoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox