Re: generic plans and "initial" pruning

On Tue, Jan 18, 2022 at 7:28 PM Simon Riggs
<simon(dot)riggs(at)enterprisedb(dot)com> wrote:
> On Tue, 18 Jan 2022 at 08:10, Amit Langote <amitlangote09(at)gmail(dot)com> wrote:
> > On Tue, Jan 18, 2022 at 4:44 PM Simon Riggs
> > <simon(dot)riggs(at)enterprisedb(dot)com> wrote:
> > > I haven't followed this in any detail, but this patch and its goal of
> > > reducing the O(N) drag effect on partition execution time is very
> > > important. Locking a long list of objects that then get pruned is very
> > > wasteful, as the results show.
> > >
> > > Ideally, we want an O(1) algorithm for single partition access and DDL
> > > is rare. So perhaps that is the starting point for a safe design -
> > > invent a single lock or cache that allows us to check if the partition
> > > hierarchy has changed in any way, and if so, replan, if not, skip
> > > locks.
> >
> > Rearchitecting partition locking to be O(1) seems like a project of
> > non-trivial complexity as Robert mentioned in a related email thread
> > couple of years ago:
> >
> > https://www.postgresql.org/message-id/CA%2BTgmoYbtm1uuDne3rRp_uNA2RFiBwXX1ngj3RSLxOfc3oS7cQ%40mail.gmail.com
>
> I agree, completely redesigning locking is a major project. But that
> isn't what I suggested, which was to find an O(1) algorithm to solve
> the safety issue. I'm sure there is an easy way to check one lock,
> maybe a new one/new kind, rather than N.

I misread your email then, sorry.

> Why does the safety issue exist? Why is it important to be able to
> concurrently access parts of the hierarchy with DDL? Those are not
> critical points.
>
> If we asked them, most users would trade a 10x performance gain for
> some restrictions on DDL. If anyone cares, make it an option, but most
> people will use it.
>
> Maybe force all DDL, or just DDL that would cause safety issues, to
> update a hierarchy version number, so queries can tell whether they
> need to replan. Don't know, just looking for an O(1) solution.

Yeah, it would be great if it would suffice to take a single lock on
the partitioned table mentioned in the query, rather than on all
elements of the partition tree added to the plan. AFAICS, ways to get
that are 1) Prevent modifying non-root partition tree elements, 2)
Make it so that locking a partitioned table becomes a proxy for having
locked all of its descendents, 3) Invent a Plan representation for
scanning partitioned tables such that adding the descendent tables
that survive plan-time pruning to the plan doesn't require locking
them too. IIUC, you've mentioned 1 and 2. I think I've seen 3
mentioned in the past discussions on this topic, but I guess the
research on whether that's doable has never been done.

--
Amit Langote
EDB: http://www.enterprisedb.com