From: Tom Lane <tgl@sss.pgh.pa.us>
To: Benjamin Tingle <ben@tingle.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query Planner not taking advantage of HASH PARTITION
Date: Sun, 17 Apr 2022 12:09:09 -0400
Message-ID: <2132751.1650211749@sss.pgh.pa.us> (raw)
In-Reply-To: <CABTcpyvoSYrn-2S4p8hRwAsCe3PkOUS4dFxmr6gUy4x9u5PDKA@mail.gmail.com>
References: <CABTcpyvoSYrn-2S4p8hRwAsCe3PkOUS4dFxmr6gUy4x9u5PDKA@mail.gmail.com>
Benjamin Tingle <ben@tingle.org> writes:
> I've recently started taking advantage of the PARTITION BY HASH feature for
> my database system. It's a really great fit since my tables can get quite
> large (900M+ rows for some) and splitting them up into manageable chunks
> should let me upload to them without having to update an enormous index
> every time. What's more, since each partition has a write lock independent
> of the parent table, it should theoretically be possible to perform a
> parallelized insert operation, provided the data to be added is partitioned
> beforehand.
> What has been disappointing is that the query planner doesn't seem to
> recognize this potential.
That's because there isn't any. The hash partitioning rule has
basically nothing to do with any plausible WHERE condition. If you're
hoping to see partition pruning happen, you need to be using list or
range partitions, with operators compatible with your likely WHERE
conditions.
(I'm of the opinion that the hash partitioning option is more in the
category of a dangerous nuisance than a useful feature. There are some
around here who will argue otherwise, but they're wrong for exactly the
reason that it's impossible to prune hash partitions.)
regards, tom lane
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: tgl@sss.pgh.pa.us, ben@tingle.org
Subject: Re: Query Planner not taking advantage of HASH PARTITION
In-Reply-To: <2132751.1650211749@sss.pgh.pa.us>
* 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