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
In-reply-to: 
 <CABTcpyvoSYrn-2S4p8hRwAsCe3PkOUS4dFxmr6gUy4x9u5PDKA@mail.gmail.com>
References: 
 <CABTcpyvoSYrn-2S4p8hRwAsCe3PkOUS4dFxmr6gUy4x9u5PDKA@mail.gmail.com>
Comments: In-reply-to Benjamin Tingle <ben@tingle.org>
	message dated "Thu, 14 Apr 2022 16:36:42 -0700"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2132750.1650211749.1@sss.pgh.pa.us>
Date: Sun, 17 Apr 2022 12:09:09 -0400
Message-ID: <2132751.1650211749@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2132751.1650211749%40sss.pgh.pa.us>
Precedence: bulk

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