Re: speeding up planning with partitions

On 30 August 2018 at 00:06, Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> wrote:
> With various overheads gone thanks to 0001 and 0002, locking of all
> partitions via find_all_inheritos can be seen as the single largest
> bottleneck, which 0003 tries to address. I've kept it a separate patch,
> because I'll need to think a bit more to say that it's actually to safe to
> defer locking to late planning, due mainly to the concern about the change
> in the order of locking from the current method. I'm attaching it here,
> because I also want to show the performance improvement we can expect with it.

For now, find_all_inheritors() locks the tables in ascending Oid
order. This makes sense with inheritance parent tables as it's much
cheaper to sort on this rather than on something like the table's
namespace and name. I see no reason why what we sort on really
matters, as long as we always sort on the same thing and the key we
sort on is always unique so that the locking order is well defined.

For partitioned tables, there's really not much sense in sticking to
the same lock by Oid order. The order of the PartitionDesc is already
well defined so I don't see any reason why we can't just perform the
locking in PartitionDesc order. This would mean you could perform the
locking of the partitions once pruning is complete somewhere around
add_rel_partitions_to_query(). Also, doing this would remove the need
for scanning pg_inherits during find_all_inheritors() and would likely
further speed up the planning of queries to partitioned tables with
many partitions. I wrote a function named
get_partition_descendants_worker() to do this in patch 0002 in [1] (it
may have been a bad choice to have made this a separate function
rather than just part of find_all_inheritors() as it meant I had to
change a bit too much code in tablecmds.c). There might be something
you can salvage from that patch to help here.

[1] https://www.postgresql.org/message-id/CAKJS1f9QjUwQrio20Pi%3DyCHmnouf4z3SfN8sqXaAcwREG6k0zQ%40mail.gmail.com

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services