On 30 August 2018 at 00:06, Amit Langote <langote_amit...@lab.ntt.co.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