On Fri, Jun 25, 2021 at 10:26 AM David Rowley <dgrowleyml@gmail.com> wrote:
> On Thu, 24 Jun 2021 at 12:32, David Rowley <dgrowleyml@gmail.com> wrote:
> > 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.
>
> I wonder, since we can't delay taking locks until after run-time
> pruning due to being unable to invalidate cached plans, maybe instead
> we could tag on any PartitionPruneInfo onto the PlannedStmt itself and
> do the init plan run-time prune run during AcquireExecutorLocks().
This is exactly what I was mulling doing when working on [1] some last
year, after an off-list discussion with Robert (he suggested the idea
IIRC), though I never quite finished writing a patch. I have planned
to revisit this topic ("locking overhead in generic plans") for v15,
now that we have *some* proposals mentioned in [1] committed to v14,
so can look into this.
> A lock would need to be taken on each partitioned table before we
> prune for it. So if there was multi-level partitioning, we'd need to
> lock the partitioned table, do pruning for that partitioned table,
> then lock any sub-partitioned tables before doing pruning on those.
>
> I don't immediately see why it couldn't be made to work, it's just
> that it adds quite a lot of complexity to what's being done in
> AcquireExecutorLocks(), which today is a very simple function.
Yeah, AcquireExecutorLocks()'s current method of finding the set of
relations to lock is very simple -- just scan the range table
(PlannedStmt.rtable). If we're to remove prunable leaf partitions
from that set, maybe we'd have to find a way to remove them from
PlannedStmt.rtable as part of running the "init" pruning, which we'd
have to do anyway, because perhaps the executor proper (mainly
InitPlan) should also see the shrunken version of the range table.
Not to mention the complexity of getting the "init" pruning itself to
run outside a full-blown executor context.
Anyway, do you agree with starting a thread to discuss possible
approaches to attack this?
--
Amit Langote
EDB: http://www.enterprisedb.com
[1] https://www.postgresql.org/message-id/CA+HiwqG7ZruBmmih3wPsBZ4s0H2EhywrnXEduckY5Hr3fWzPWA@mail.gmail.com