On Sat, 22 Dec 2018 at 09:28, David Rowley <david.row...@2ndquadrant.com> wrote: > Going by my profiler this drops match_eclasses_to_foreign_key_col() > down to just 10% of total planner time for this query. The new > bms_is_member() call is pretty hot inside that function though.
I should have said 28% instead of 10%. 10% is the time spent exclusively just in that function (not in a function called by that function). The bms_is_member() call I mention above is about 20% of the total time, which likely includes some other call sites too. Back in [1], I mentioned that I'd like to start moving away from our linked list based implementation of List and start using an array based version instead. If we had this we could easily further improve this code fkey matching code to not even look near equivalence classes that don't contain members for the relations in question with a design something like: 1. Make PlannerInfo->eq_classes an array list instead of an array, this will significantly improve the performance of list_nth(). 2. Have a Bitmapset per relation that indexes which items in eq_classes have ec_members for the given relation. 3. In match_eclasses_to_foreign_key_col() perform a bms_overlap() on the eq_classes index bitmapsets for the relations at either side of the foreign key then perform a bms_next_member() type loop over the result of that in order to skip over the eq_classes items that can't match. Since match_foreign_keys_to_quals() calls match_eclasses_to_foreign_key_col() once for each item in PlannerInfo->fkey_list (167815 items in this case) and again for each foreign key column in those keys, then this should significantly reduce the effort required since we make a pass over *every* equivalence class each time match_eclasses_to_foreign_key_col() gets called. This array list implementation is something I did want to get one for PG12. The height of the bar to do this is pretty high given what was mentioned in [2]. [1] https://www.postgresql.org/message-id/CAKJS1f_2SnXhPVa6eWjzy2O9A%3Docwgd0Cj-LQeWpGtrWqbUSDA%40mail.gmail.com [2] https://www.postgresql.org/message-id/21592.1509632225%40sss.pgh.pa.us -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
