From: | Benjamin Tingle <ben(at)tingle(dot)org> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Query Planner not taking advantage of HASH PARTITION |
Date: | 2022-04-17 17:07:50 |
Message-ID: | CABTcpyvPwQekzDfFbDAV-=B00ks76YrtbZLE-Jcb2Q5h1+JdyQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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).
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2022-04-17 18:20:24 | Re: Query Planner not taking advantage of HASH PARTITION |
Previous Message | David G. Johnston | 2022-04-17 16:47:51 | Re: Query Tunning related to function |