"Gregory Stark" <[EMAIL PROTECTED]> writes: > "Tom Lane" <[EMAIL PROTECTED]> writes: > >> Applied along with some other hacking to reduce the costs of the >> lower-level functions that this example shows to be inefficient. >> They'd still be slow in large queries, whether CE applies or not. > > BIG difference. The case that caused swapping and took almost 15m to plan is > now down to 2.5s. The profile still looks a bit odd but I can't argue with > the results. > > [EMAIL PROTECTED]:/var/tmp/db$ gprof /usr/local/pgsql/bin/postgres gmon.out > Flat profile: > > Each sample counts as 0.01 seconds. > % cumulative self self total > time seconds seconds calls s/call s/call name > 24.36 2.46 2.46 418517 0.00 0.00 SearchCatCache > 7.33 3.20 0.74 2564235 0.00 0.00 hash_any > 6.34 3.84 0.64 4283964 0.00 0.00 > hash_search_with_hash_value > 4.36 4.28 0.44 216316 0.00 0.00 list_nth_cell > 3.96 4.68 0.40 6535943 0.00 0.00 AllocSetAlloc > 3.37 5.02 0.34 4165664 0.00 0.00 _bt_compare > 2.67 5.29 0.27 2266696 0.00 0.00 > MemoryContextAllocZeroAligned
For what it's worth if I defeat the "same column position" optimization SearchCatCache and list_nth_cell pop back to the top. I had a version that kept the col_mappings and translated_vars lists in arrays to fix the n^2 list_nth_cell behaviour. But the SearchCatCache is something else. 53.77 87.36 87.36 4813018 0.00 0.00 SearchCatCache 11.29 105.71 18.35 1610810 0.00 0.00 list_nth_cell 3.09 110.72 5.01 17738640 0.00 0.00 hash_any 2.50 114.78 4.05 30612499 0.00 0.00 hash_search_with_hash_value 2.16 118.29 3.51 54588745 0.00 0.00 AllocSetAlloc -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend