Re: Query Planner not taking advantage of HASH PARTITION

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Benjamin Tingle <ben(at)tingle(dot)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
>
--

Ben(t).