Hi, The code in convert_tuples_by_name_map() could be made a bit smarter to speed up the search for the column with the same name by first looking at the same attnum in the other relation rather than starting the search from the first attnum each time.
I imagine most people are going to create partitions with columns in the same order as they are in the partitioned table. This will happen automatically if the CREATE TABLE .. PARTITION OF syntax is used, so it makes sense to exploit that knowledge. I've attached a patch which modifies convert_tuples_by_name_map() to have it just start the inner loop search in the same position as we are currently in the outer loop. Best case, this takes the function from being O(N^2) to O(N). It quite possible that the partition or, more likely, the partitioned table has had some columns dropped (likely this lives longer than a partition), in which case we don't want to miss the target column by 1, so I've coded the patch to count the non-dropped columns and start the search after having ignored dropped columns. This patch is just a few lines of code. I do think there's much more room to improve this whole area. For example, build child->parent map from the parent->child map instead of searching again later with the inputs reversed. Although, that's more than a handful of lines of code. The change I'm proposing here seems worthwhile to me. FWIW, something similar is done in make_inh_translation_list(), although I think my way might have a slightly better chance of not hitting the worst cases search. Benchmark: (Uses tables with 1000 columns, each with a name 11 chars in length.) Setup: select 'create table listp (' || string_agg('c' || left(md5(x::Text),10) || ' int',',') || ') partition by list (c' || left(md5('1'),10) || ');' from generate_Series(1,1000) x; \gexec create table listp0 partition of listp for values in(0); create table listp1 partition of listp for values in(1); select 'create table listpd (' || string_agg('c' || left(md5(x::Text),10) || ' int',',') || ') partition by list (c' || left(md5('1'),10) || ');' from generate_Series(0,1000) x; \gexec select 'alter table listpd drop column c' || left(md5(0::text),10) || ';'; \gexec create table listpd0 partition of listpd for values in(0); create table listpd1 partition of listpd for values in(1); select 'create table list (' || string_agg('c' || left(md5(x::Text),10) || ' int',',') || ');' from generate_Series(1,1000) x; \gexec \q psql -t -c "select 'insert into listp values(' || string_Agg('1',',') || ');' from generate_Series(1,1000) x;" postgres > insertp.sql psql -t -c "select 'insert into listpd values(' || string_Agg('1',',') || ');' from generate_Series(1,1000) x;" postgres > insertpd.sql psql -t -c "select 'insert into list values(' || string_Agg('1',',') || ');' from generate_Series(1,1000) x;" postgres > insert.sql psql -t -c "select 'update list set c' || left(md5('1'),10) || ' = (c' || left(md5('1'),10) || ' + 1) & 1;'" postgres > update.sql psql -t -c "select 'update listp set c' || left(md5('1'),10) || ' = (c' || left(md5('1'),10) || ' + 1) & 1;'" postgres > updatep.sql psql -t -c "select 'update listpd set c' || left(md5('1'),10) || ' = (c' || left(md5('1'),10) || ' + 1) & 1;'" postgres > updatepd.sql Tests: # Test 1: INSERT control test for non-partitioned table. pgbench -n -T 60 -f insert.sql postgres # Test 2: INSERT perfect match pgbench -n -T 60 -f insertp.sql postgres # Test 3: INSERT dropped first column from parent pgbench -n -T 60 -f insertpd.sql postgres # Test 4: UPDATE control test for non-partitioned table. psql -c "truncate list;" postgres psql -f insert.sql postgres pgbench -n -T 60 -f update.sql postgres # Test 5: UPDATE perfect match psql -c "truncate listp;" postgres psql -f insertp.sql postgres pgbench -n -T 60 -f updatep.sql postgres # Test 6: UPDATE dropped first column from parent psql -c "truncate listpd;" postgres psql -f insertpd.sql postgres pgbench -n -T 60 -f updatepd.sql postgres Results: AWS m5d (fsync off) Unpatched: Test 1: tps = 1055.527253 (excluding connections establishing) Test 2: tps = 355.308869 (excluding connections establishing) Test 3: tps = 361.465022 (excluding connections establishing) Test 4: tps = 1107.483236 (excluding connections establishing) Test 5: tps = 132.805775 (excluding connections establishing) Test 6: tps = 86.303093 (excluding connections establishing) Patched: Test 1: tps = 1055.831144 (excluding connections establishing) Test 2: tps = 1014.282634 (excluding connections establishing) Test 3: tps = 982.258274 (excluding connections establishing) Test 4: tps = 625.429778 (excluding connections establishing) Test 5: tps = 525.667826 (excluding connections establishing) Test 6: tps = 173.529296 (excluding connections establishing) I'd have expected Test 6 to do about 480-500tps. Adding debug to check why it's not revealed that the search is doing what's expected. I'm unsure why it has not improved more. Given the small size of this patch. I think it's a good candidate for the July 'fest. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
v1-0001-Improve-best-case-speed-of-tuple-conversion-map-g.patch
Description: Binary data