Improve performance of tuple conversion map generation

Enterprise / PostgreSQL - Heikki Linnakangas [] - 13 July 2018 16:54 EDT

Previously convert_tuples_by_name_map naively performed a search of each outdesc column starting at the first column in indesc and searched each indesc column until a match was found. When partitioned tables had many columns this could result in slow generation of the tuple conversion maps. For INSERT and UPDATE statements that touched few rows, this could mean a
very large overhead indeed.

We can do a bit better with this loop. It's quite likely that the columns in partitioned tables and their partitions are in the same order, so it makes sense to start searching for each column outer column at the inner column position 1 after where the previous match was found (per idea from Alexander Kuzmenkov). This makes the best case search O(N) instead of O(N^2). The worst case is still O(N^2), but it seems unlikely that would happen.

Likewise, in the planner, make_inh_translation_list's search for the matching column could often end up falling back on an O(N^2) type search. This commit also improves that by first checking the column that follows the previous match, instead of the column with the same attnum. If we fail to match here we fallback on the syscache's hashtable lookup.

Author: David Rowley

42f70cd9c3 Improve performance of tuple conversion map generation
src/backend/access/common/tupconvert.c | 36 ++++++++++++++++++++++++-------
src/backend/optimizer/prep/prepunion.c | 39 +++++++++++++++++-----------------
2 files changed, 47 insertions(+), 28 deletions(-)


  • Share