Interesting. Why is it impossible to prune hash partitions? Maybe prune isn’t the best word, more so use to advantage. At the very least, it should be possible to utilize a parallel insert against a table partitioned by hash. (Partition query rows, then distribute these rows to parallel workers)
On Sun, Apr 17, 2022 at 9:09 AM Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
Benjamin Tingle <ben@xxxxxxxxxx> 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
Ben(t).
