On Thu, 24 Jun 2021 at 12:14, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> David Rowley <dgrowleyml@gmail.com> writes:
> > But... maybe there are some cases where a user can be certain that all
> > interesting records are contained in a single partition but
> > partitioning pruning cannot prove it...So maybe what you say is right.
> > The workaround there would be to add a qual that allows pruning to
> > work.
>
> [ shrug... ] It's about as easy to just name the partition you want.
> When planning overhead is considered, maybe it's a lot easier.
I'm not suggesting that we go and make it impossible for users to
directly reference partitions today. What I mean is that as we add
more and more fixes to improve performance of partitioning, that there
comes a point where the ability to directly reference partitions is a
hindrance rather than something that's useful. Right now that
hindrance is the fact that we must lock every single partition in the
plan. We only need to do that in case some other backend is doing
something that bypasses taking a lock on the parent partitioned table.
The overhead of taking these locks is pretty significant for
partitioned tables with lots of partitions where only 1 of them
survives run-time partition pruning. That's really terrible for
people that want to PREPARE queries and just look up a single row from
a single partition. That seems like a pretty big use case that we're
just terrible at today.
David