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 <t...@sss.pgh.pa.us> wrote: > Benjamin Tingle <b...@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 > -- Ben(t).
