[PATCHES] doc/FAQ_DEV: about profile
Though there is a description for the profile in doc/FAQ_DEV: You can also compile with profiling to see what functions are taking execution time. The backend profile files will be deposited in the pgsql/data/base/dbname directory. The backend profile files is deposited in the pgsql/data directory on my linux now. I think that we should correct doc/FAQ_DEV. regards, --- Atsushi Ogawa -- [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] wchareq improvement
Bruce Momjian wrote: Patch applied with adjustment --- the second part of your patch that skips comparing the first byte seemed unnecessary. It seemed likely to cause a cpu stall, so just doing the loop seemed faster. Did you test if the second part of your patch actually caused a speedup? Well, I measured the performance today. As a result, I confirmed the second part of my patch is unnecessary as you pointed it out. Thanks for comment and applying patch. a_ogawa wrote: In SQL that uses 'like' operator, wchareq is used to compare characters. At the head of wchareq, length of (multibyte) character is compared by using pg_mblen. Therefore, pg_mblen is executed many times, and it becomes a bottleneck. regards, --- Atsushi Ogawa ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] AllocSetReset improvement
Tom Lane [EMAIL PROTECTED] writes: a_ogawa [EMAIL PROTECTED] writes: It is a reasonable idea. However, the majority part of MemSet was not able to be avoided by this idea. Because the per-tuple contexts are used at the early stage of executor. Drat. Well, what about changing that? We could introduce additional contexts or change the startup behavior so that the ones that are frequently reset don't have any data in them unless you are working with pass-by-ref values inside the inner loop. That might be possible. However, I think that we should change only aset.c about this article. I thought further: We can check whether context was used from the last reset even when blocks list is not empty. Please see attached patch. The effect of the patch that I measured is as follows: o Execution time that executed the SQL ten times. (1)Linux(CPU: Pentium III, Compiler option: -O2) - original: 24.960s - patched : 23.114s (2)Linux(CPU: Pentium 4, Compiler option: -O2) - original: 8.730s - patched : 7.962s (3)Solaris(CPU: Ultra SPARC III, Compiler option: -O2) - original: 37.0s - patched : 33.7s regards, --- Atsushi Ogawa aset.c.patch Description: Binary data ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PATCHES] AllocSetReset improvement
Tom Lane [EMAIL PROTECTED] writes: And I'm worried about adding even a small amount of overhead to palloc/pfree --- on the vast majority of the profiles I look at, those are more expensive than AllocSetReset. I don't worry about palloc. Because overhead increases only when malloc is executed in AllocSetAlloc. But I'm wooried about pfree, too. However, when palloc/pfree was executed many times, I did not see a bad influence. In most of the tests I've looked at, palloc/pfree are executed far more often than AllocSetReset, and so adding even one instruction there to sometimes save a little work in AllocSetReset is a bad tradeoff. You can't optimize to make just one test case look good. I agree. I give up adding instruction to palloc/pfree. I have another idea though: in the case you are looking at, I think that the context in question never gets any allocations at all, which means its blocks list stays null. We could move the MemSet inside the "if (blocks)" test --- if there are no blocks allocated to the context, it surely hasn't got any chunks either, so the MemSet is unnecessary. That should give us most of the speedup without any extra cost in palloc/pfree. It is a reasonable idea. However, the majority part of MemSet was not able to be avoided by this idea. Because the per-tuple contexts are used at the early stage of executor. function that calls numbercontextset-blocks MemoryContextReset of calls addressis null - execTuplesMatch(execGrouping.c:65) 450x836dd28 false agg_fill_hash_table(nodeAgg.c:924) 500x836dd28 false ExecHashJoin(nodeHashjoin.c:108) 510x836dec0 false ExecHashJoin(nodeHashjoin.c:217) 500x836dec0 false ExecHashGetHashValue(nodeHash.c:669) 550x836dec0 false ExecScanHashBucket(nodeHash.c:785) 500x836dec0 false ExecScan(execScan.c:86) 570x836df48 true I am considering another idea: I think that we can change behavior of the context by switching the method table of context. An simple version of AllocSetAlloc and AllocSetReset is made. These API can be accelerated instead of using neither a freelist nor the blocks (The keeper excludes it). When the context is initialized and reset, these new API is set to the method table. And, when a freelist or a new block is needed, the method table is switched to normal API. This can be executed by doing the pfree/repalloc hook. As a result, overhead of pfree becomes only once about one context. I think that this idea is effective in context that executes repeatedly reset after small allocations such as per-tuple context. And I think that overhead given to the context that executes a lot of palloc/pfree is a very few. An attached patch is a draft of that implementation. Test and comment on the source code are insufficient yet. regards, --- Atsushi Ogawa aset.c.patch Description: Binary data ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PATCHES] AllocSetReset improvement
Tom Lane [EMAIL PROTECTED] writes: a_ogawa [EMAIL PROTECTED] writes: In SQL that executes aggregation, AllocSetReset is called many times and spend a lot of cycles. This patch saves the cycles spent by AllocSetReset. Hmm. It doesn't seem like this could be a big win overall. It's not possible to save a whole lot of cycles inside AllocSetReset, because if there isn't anything for it to do, it should fall through pretty quickly anyway. I thought that I was able to avoid MemSet in AllocSetReset. MemSet(set-freelist, 0, sizeof(set-freelist)); My profile result in previous mail is as follows: % cumulative selfself total time seconds secondscalls s/call s/call name 9.20 3.063.06 38500155 0.00 0.00 AllocSetReset Therefore, sizeof(set-freelist) * (number of calls) = 44 bytes * 38500155 = 1615 Mbytes. And I'm worried about adding even a small amount of overhead to palloc/pfree --- on the vast majority of the profiles I look at, those are more expensive than AllocSetReset. I don't worry about palloc. Because overhead increases only when malloc is executed in AllocSetAlloc. But I'm wooried about pfree, too. However, when palloc/pfree was executed many times, I did not see a bad influence. It is a result of executing 'select * from accounts' 20 times as follows. original code: Each sample counts as 0.01 seconds. % cumulative self self total time seconds secondscalls s/call s/call name 6.79 4.03 4.03 9599 0.00 0.00 appendBinaryStringInfo 6.57 7.93 3.90 50005879 0.00 0.00 AllocSetAlloc 5.63 11.27 3.34 1000 0.00 0.00 printtup 5.61 14.60 3.33 1000 0.00 0.00 slot_deform_tuple 5.36 17.78 3.18 50001421 0.00 0.00 AllocSetFree patched code: Each sample counts as 0.01 seconds. % cumulative self self total time seconds secondscalls s/call s/call name 8.07 4.78 4.78 9599 0.00 0.00 appendBinaryStringInfo 7.23 9.06 4.28 50005879 0.00 0.00 AllocSetAlloc 5.40 12.26 3.20 1000 0.00 0.00 printtup 5.20 15.34 3.08 1000 0.00 0.00 slot_deform_tuple 5.13 18.38 3.04 50001421 0.00 0.00 AllocSetFree I think that it is difficult to measure the influence that this patch gives palloc/pfree. I duplicated your test case to see where the reset calls were coming from, and got this: (Does this match your profile? I only ran the query 5 times not 10.) I'm sorry. My profile in previous mail were 11 times not 10. And your profile and my profile are match. This shows that the majority of the resets are coming from the hashjoin code not the aggregation code. You are right. I measured where MemoryContextReset had been called. (The SQL was executed once) filename(line)function number of calls - execGrouping.c(65)execTuplesMatch 45 execScan.c(86)ExecScan 57 nodeAgg.c(924)agg_fill_hash_table 50 nodeAgg.c(979)agg_retrieve_hash_table5 nodeHash.c(669) ExecHashGetHashValue 55 nodeHash.c(785) ExecScanHashBucket50 nodeHashjoin.c(108) ExecHashJoin 51 nodeHashjoin.c(217) ExecHashJoin 50 - Total 3500013 Many are the one from hashjoin. Other is the one from grouping, table/index scan, and aggregation by hash. And I measured the number of times that was able to avoid MemSet in AllocSetReset. avoided MemSet 358 executed MemSet7 --- Total3500015 (The execution time of AllocSetReset is more twice than MemoryContextReset because there is MemoryContextResetAndDeleteChildren in PostgresMain) regards, --- Atsushi Ogawa ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[PATCHES] AllocSetReset improvement
In SQL that executes aggregation, AllocSetReset is called many times and spend a lot of cycles. This patch saves the cycles spent by AllocSetReset. An idea of the patch is to add a flag to AllocSetContext. This flag shows whether AllocSetReset should work. The effect of the patch that I measured is as follows: o Data for test was created by 'pgbench -i -s 5'. o Test SQL: select b.bid, sum(a.abalance), avg(a.abalance) from accounts a, branches b where a.bid = b.bid group by b.bid; o I measured time that executed the SQL ten times. (1)Linux(CPU: Pentium III, Compiler option: -O2) - original: 31.310s - patched : 28.812s (2)Linux(CPU: Pentium 4, Compiler option: -O2) - original: 8.953s - patched : 7.753s (3)Solaris(CPU: Ultra SPARC III, Compiler option: -O2) - original: 41.8s - patched : 38.6s o gprof result(Linux, Compiler option: -O2 -pg -DLINUX_PROFILE) - original Each sample counts as 0.01 seconds. % cumulative selfself total time seconds secondscalls s/call s/call name 9.20 3.063.06 38500155 0.00 0.00 AllocSetReset 8.99 6.052.99 27500055 0.00 0.00 slot_deform_tuple 7.40 8.512.46 4400 0.00 0.00 slot_getattr 4.81 10.111.60 27500110 0.00 0.00 ExecEvalVar 3.64 11.321.21 38500143 0.00 0.00 MemoryContextReset 3.64 12.531.21 6007086 0.00 0.00 LWLockRelease 3.31 13.631.10 5500079 0.00 0.00 heapgettup - patched Each sample counts as 0.01 seconds. % cumulative selfself total time seconds secondscalls s/call s/call name 8.76 2.822.82 27500055 0.00 0.00 slot_deform_tuple 7.73 5.312.49 4400 0.00 0.00 slot_getattr 4.72 6.831.52 27500110 0.00 0.00 ExecEvalVar 4.32 8.221.39 5500011 0.00 0.00 ExecHashJoin 4.28 9.601.38 6007086 0.00 0.00 LWLockRelease 4.04 10.901.30 38500143 0.00 0.00 MemoryContextReset 3.63 12.071.17 5500079 0.00 0.00 heapgettup 3.04 13.050.98 5499989 0.00 0.00 ExecMakeFunctionResultNoSets 2.67 13.910.86 5500110 0.00 0.00 ExecProject 2.61 14.750.84 1100 0.00 0.00 advance_transition_function 2.55 15.570.82 38500155 0.00 0.00 AllocSetReset regards, --- Atsushi Ogawa aset.c.patch Description: Binary data ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[PATCHES] wchareq improvement
In SQL that uses 'like' operator, wchareq is used to compare character. At the head of wchareq, length of character is compared by using pg_mblen. Therefore, pg_mblen is executed many times, and it becomes a bottleneck. This patch makes a short cut, and reduces execution frequency of pg_mblen. test.sql: select count(*) from accounts where aid like '%1'; ... (repeated 10 times) test command: $ psql -f test.sql result of original code(compile option "-O2 -pg"): --- Each sample counts as 0.01 seconds. % cumulative selfself total time seconds secondscalls s/call s/call name 7.82 0.32 0.32 17566930 0.00 0.00 pg_euc_mblen 7.09 0.61 0.29 17566930 0.00 0.00 pg_mblen 6.60 0.88 0.27 100 0.00 0.00 MBMatchText 5.38 1.10 0.22 100 0.00 0.00 HeapTupleSatisfiesSnapshot 5.13 1.31 0.21 90 0.00 0.00 ExecMakeFunctionResultNoSets 4.89 1.51 0.20 17566930 0.00 0.00 pg_eucjp_mblen result of patched code(compile option "-O2 -pg"): Each sample counts as 0.01 seconds. % cumulative self self total time seconds seconds calls s/call s/call name 8.56 0.320.32 100 0.00 0.00 MBMatchText 7.75 0.610.29 100 0.00 0.00 HeapTupleSatisfiesSnapshot 6.42 0.850.24 100 0.00 0.00 slot_deform_tuple 5.88 1.070.22 8789050 0.00 0.00 pg_euc_mblen 5.88 1.290.22 112 0.00 0.00 heapgettup 5.61 1.500.2190 0.00 0.00 ExecMakeFunctionResultNoSets execution time(compile option "-O2"): original code: 4.795sec patched code: 4.496sec regards, --- Atsushi Ogawa ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[PATCHES] wchareq improvement
I forgot to attach a patch. I do post once again. In SQL that uses 'like' operator, wchareq is used to compare characters. At the head of wchareq, length of (multibyte) character is compared by using pg_mblen. Therefore, pg_mblen is executed many times, and it becomes a bottleneck. This patch makes a short cut, and reduces execution frequency of pg_mblen. test.sql: select count(*) from accounts where aid like '%1'; ... (repeated 10 times) test command: $ psql -f test.sql result of original code(compile option "-O2 -pg"): --- Each sample counts as 0.01 seconds. % cumulative selfself total time seconds secondscalls s/call s/call name 7.82 0.32 0.32 17566930 0.00 0.00 pg_euc_mblen 7.09 0.61 0.29 17566930 0.00 0.00 pg_mblen 6.60 0.88 0.27 100 0.00 0.00 MBMatchText 5.38 1.10 0.22 100 0.00 0.00 HeapTupleSatisfiesSnapshot 5.13 1.31 0.21 90 0.00 0.00 ExecMakeFunctionResultNoSets 4.89 1.51 0.20 17566930 0.00 0.00 pg_eucjp_mblen result of patched code(compile option "-O2 -pg"): Each sample counts as 0.01 seconds. % cumulative self self total time seconds seconds calls s/call s/call name 8.56 0.320.32 100 0.00 0.00 MBMatchText 7.75 0.610.29 100 0.00 0.00 HeapTupleSatisfiesSnapshot 6.42 0.850.24 100 0.00 0.00 slot_deform_tuple 5.88 1.070.22 8789050 0.00 0.00 pg_euc_mblen 5.88 1.290.22 112 0.00 0.00 heapgettup 5.61 1.500.2190 0.00 0.00 ExecMakeFunctionResultNoSets execution time(compile option "-O2"): original code: 4.795sec patched code: 4.496sec regards, --- Atsushi Ogawa wchareq.patch Description: Binary data ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[PATCHES] improves ExecMakeFunctionResultNoSets
Attached patch improves ExecMakeFunctionResultNoSets, etc. This patch uses InitFunctionCallInfoData macro instead of MemSet to initialize FunctionCallInfoData. An idea of this patch discussed in the "FunctionCallN improvement" thread. (http://archives.postgresql.org/pgsql-hackers/2005-01/msg01054.php) To achieve this, InitFunctionCallInfoData macro was moved from fmgr.c to fmgr.h. test sql: select substr(c.relname, 1, 10) from pg_class c, pg_am, pg_amop; (There are pg_am and pg_amop only to increase the number of the records.) result of original code: --- Each sample counts as 0.01 seconds. % cumulative self self total time seconds secondscalls s/call s/call name 21.43 0.36 0.36 219911 0.00 0.00 ExecMakeFunctionResultNoSets 7.14 0.48 0.12 219912 0.00 0.00 pg_mbstrlen_with_len 6.25 0.58 0.10 1102916 0.00 0.00 AllocSetAlloc 5.36 0.68 0.09 5936448 0.00 0.00 pg_euc_mblen 5.36 0.77 0.09 5936448 0.00 0.00 pg_mblen result of after patch: --- Each sample counts as 0.01 seconds. % cumulative self self total time seconds secondscalls s/call s/call name 7.52 0.10 0.10 5936448 0.00 0.00 pg_mblen 7.14 0.20 0.10 1104587 0.00 0.00 AllocSetAlloc 6.77 0.28 0.09 219912 0.00 0.00 text_substring 6.39 0.37 0.09 1547723 0.00 0.00 AllocSetFreeIndex 6.02 0.45 0.08 219912 0.00 0.00 pg_mbstrlen_with_len 4.51 0.51 0.06 5936448 0.00 0.00 pg_euc_mblen 4.51 0.57 0.06 442745 0.00 0.00 ExecProcNode 4.51 0.63 0.06 219911 0.00 0.00 ExecMakeFunctionResultNoSets regards, --- Atsushi Ogawa ExecMakeFunctionResultNoSets.patch Description: Binary data ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] WIP: avoiding tuple construction/deconstruction overhead
Tom Lane wrote: a_ogawa [EMAIL PROTECTED] writes: (1)We can improve compare_heap() by using TableTupleSlot instead of HeapTuple. Please see attached patch. Did you measure any performance improvement from that? I considered it but thought it would likely be a wash or a loss, because in most cases only one attribute will be pulled from a tuple during comparetup_heap. slot_getattr cannot improve on heap_getattr in that case, and is quite likely to be slower. I measured performance of heap_getattr and slot_getattr in comparetup_heap. I made the table which had ten varchar attributes, and registered data for tests. (Attached file includes SQL doing this.) I carried out the following tests. (case 1) test1: select * from sort_test order by v1 limit 100; test2: select * from sort_test order by v1, v2 limit 100; test3: select * from sort_test order by v1, v2, v3 limit 100; test4: select * from sort_test order by v1, v2, v3, v4 limit 100; test5: select * from sort_test order by v1, v2, v3, v4, v5 limit 100; result:test1test2test3test4test5 --- heap_getattr 2.149s 2.602s 3.204s 3.830s 4.159s slot_getattr 2.523s 3.422s 3.977s 4.453s 4.721s (case 2) test1: select * from sort_test order by v10 limit 100; test2: select * from sort_test order by v10, v9 limit 100; test3: select * from sort_test order by v10, v9, v8 limit 100; test4: select * from sort_test order by v10, v9, v8, v7 limit 100; test5: select * from sort_test order by v10, v9, v8, v7, v6 limit 100; result:test1test2test3test4test5 --- heap_getattr 3.654s 5.549s 6.575s 7.367s 7.870s slot_getattr 4.027s 4.930s 5.249s 5.555s 5.756s (case 3) test1: select * from sort_test order by v5 limit 100; test2: select * from sort_test order by v5, v6 limit 100; test3: select * from sort_test order by v5, v6, v7 limit 100; test4: select * from sort_test order by v5, v6, v7, v8 limit 100; test5: select * from sort_test order by v5, v6, v7, v8, v9 limit 100; result:test1test2test3test4test5 --- heap_getattr 2.657s 4.207s 5.194s 6.179s 6.662s slot_getattr 3.126s 4.233s 4.806s 5.271s 5.557s In most cases, heap_getattr is fast. When the following conditions occurred, slot_getattr is fast. (1)Tuple have varlen attributes. (2)Sort key have more than two attributes. (3)A position of a sort key is far from the head of tuple. (4)As for the data of a sort key, there be many repetition. Actually it will be rare that these conditions are occurred. Thinking from a result, I think that we had better continue using heap_getattr in comparetup_heap. regards, --- Atsushi Ogawa make_test_data.sql Description: Binary data ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] WIP: avoiding tuple construction/deconstruction overhead
Tom Lane wrote: Attached is the current state of a patch to reduce the overhead of passing tuple data up through many levels of plan nodes. It is a good idea. I think that this patch improves performance of the whole executor. I have three comments. (1)We can improve compare_heap() by using TableTupleSlot instead of HeapTuple. Please see attached patch. (2)In ExecStoreTuple(), we can omit initialization of slot-tts_nvalid. If slot-tts_isempty is false, tts_nvalid is initialized by ExecClearTuple(). If it is true, tts_nvalid is always zero. (3)There is a description of slot-val in comment of execTuple.c. This had better revise it. Finally, I have made some progress towards making the tuple access routines consistently use "bool isNull" arrays as null markers, instead of the char 'n' or ' ' convention that was previously used in some but not all contexts. I agree. I think that this progress improves readability. regards, --- Atsushi Ogawa compare_heap.patch Description: Binary data ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] Cache last known per-tuple offsets to speed long tuple
I remaked patch for "Cache last known per-tuple offsets to speed long tuple access" that is in TODO list. The point of this patch is as follows: (1)Add fields to TupleTableSlot and TupleTableData. This fields are for holding the tuple disassembly information. (2)Add the codes which initializes/cleans new fields. These codes are added to execTuples.c. (3)Add two functions to execQual.c. This function name is slot_deformtuple and this is just like heap_deformtuple. This function can be resumed from the previous execution using the information encapsulated in the TupleTableSlot. Another function is just like heap_getattr and fast_getattr. This function name is slot_getattr. This is just like heap_getattr and fast_getattr macro, except it is given a TupleTableSlot, and this function uses slot_deformtuple insetead of nocachegetattr. (4)ExecEvalVar uses new function slot_getattr instead of heap_getattr. I executed the test below. --- Table has 16,384tuples, 200columns. All data type is text. Table name is aaa. Column name is t001...t200. Executed SQL is, select t100, t110, t120, t130, t140, t150, t160, t170, t180, t190, t200 from aaa; The profile result of original code is as follows. --- Each sample counts as 0.01 seconds. % cumulative self self total time seconds secondscalls s/call s/call name 74.19 1.38 1.38 163846 0.00 0.00 nocachegetattr 4.30 1.46 0.08 163840 0.00 0.00 FunctionCall3 1.61 1.49 0.03 397750 0.00 0.00 AllocSetFreeIndex 1.61 1.52 0.0316384 0.00 0.00 ExecTargetList 1.08 1.54 0.02 344152 0.00 0.00 appendBinaryStringInfo The profile result after the patch applying is as follows. --- Each sample counts as 0.01 seconds. % cumulative self self total time seconds secondscalls ms/call ms/call name 30.38 0.24 0.24 163840 0.00 0.00 slot_deformtuple 10.13 0.32 0.08 163840 0.00 0.00 FunctionCall3 5.06 0.36 0.04 163840 0.00 0.00 slot_getattr 5.06 0.40 0.0416384 0.00 0.00 heap_deformtuple 3.80 0.43 0.0349159 0.00 0.00 ExecClearTuple regards, --- Atsushi Ogawa [EMAIL PROTECTED] ExecEvalVar.patch Description: Binary data ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PATCHES] Cache last known per-tuple offsets to speed long tuple
Thank you for advice. I am going to remake a patch, in order to make it simple. The plan of a new patch is as follows. (1)Add fields to TupleTableSlot and TupleTableData. This fields are for holding the tuple disassembly information. (2)Add the codes which initializes/cleans new fields. These codes are added to execTuples.c. (3)Add two functions to execQual.c. One function is just like heap_deformtuple. It is given a TupleTableSlot. And it extracts the field of tuple incrementary using the new fields of TupleTableSlot. The meaning of incrementary is as the following example. Example: The tupple has 100 columns. - first call to get col5 will fill first 5 positions in the array. - next call to get col75 will fill starts from 5 and up to 75. - next call to get col60 will only refer to array, because already extracted. Another function is just like heap_getattr and fast_getattr. It is given a TupleTableSlot. And this function uses new function(like a heap_deformtuple), instead of nocachegetattr. (4)ExecEvalVar uses new function(like a heap_getattr) instead of heap_getattr. With a new patch, only three files of tuptable.h, and execTuple.c and execQual.c are due to be changed. BTW, why is it that your profile shows *more* calls to heap_deformtuple_incr after the patch than there were nocachegetattr calls before? Many one is for printtup. (printtup - heap_deformtuple - heap_deformtuple_incr) Since the code of heap_deformtuple and heap_deformtuple_incr has been share, heap_deformtuple_incr looks many calls than nocachegetattr. If a part for the number of calls of heap_deformtuple_incr by printtup is removed, heap_deformtuple_incr and nocachegetattr will be the same number of calls. With my test being to access the column in ascending order (select t100, t110 ...), heap_deformtuple_incr and nocachegetattr is the same calls. If the column is accessed in descending order(select t200, t190...), number of calls of heap_deformtuple_incr will decrease sharply. It is because a result is cached by the first call to get t200. regards, --- Atsushi Ogawa [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
[PATCHES] Cache last known per-tuple offsets to speed long tuple access
I made a patch for "Cache last known per-tuple offsets to speed long tuple access" that is in TODO list. This problem was discussed on hackers-list as "Terrible performance on wide selects". The point of this problem is nocachegetattr() used from ExecEvalVar(). If tuple has many columns, and it has varlen column or null data, time spent in nocachegetattr() is O(N^2) in the number of fields. I referred URL below for implementation. http://archives.postgresql.org/pgsql-performance/2003-01/msg00262.php The point of this patch is as follows: (1)heap_deformtuple_incr() is added. This function can extract attributes of tupple, incrementally. (2)The cache which keeps the result of heap_deformtuple_incr(), is added inside TupleTableSlot. (3)In ExecEvalVar(), heap_deformtuple_incr() is used in place of nocachegetattr(). This would reduce the time from O(N^2) to O(N). In order to measure the effect, I executed the test below. --- Table has 15,000tuples, 200columns. All data type is text. Table name is aaa. Column name is t001...t200. Executed SQL is, select t100, t110, t120, t130, t140, t150, t160, t170, t180, t190, t200 from aaa; The profile result of original code is as follows. --- Each sample counts as 0.01 seconds. % cumulative self self total time seconds secondscalls s/call s/call name 70.05 1.31 1.31 163846 0.00 0.00 nocachegetattr 8.02 1.46 0.15 163840 0.00 0.00 FunctionCall3 1.87 1.50 0.04 397763 0.00 0.00 AllocSetFreeIndex 1.60 1.52 0.03 163840 0.00 0.00 ExecEvalVar 1.34 1.55 0.03 200414 0.00 0.00 AllocSetAlloc The profile result after the patch applying is as follows. --- Each sample counts as 0.01 seconds. % cumulative self self total time seconds secondscalls ms/call ms/call name 39.73 0.29 0.29 180224 0.00 0.00 heap_deformtuple_incr 9.59 0.36 0.07 163840 0.00 0.00 FunctionCall3 6.85 0.41 0.0516384 0.00 0.02 ExecTargetList 5.48 0.45 0.0423477 0.00 0.00 hash_any 4.11 0.48 0.03 200414 0.00 0.00 AllocSetAlloc Regards, --- Atsushi Ogawa ([EMAIL PROTECTED]) deformtuple_cache.patch Description: Binary data ---(end of broadcast)--- TIP 8: explain analyze is your friend
