Re: 27 second plan times

"Gregory Stark" <stark(at)enterprisedb(dot)com> writes:

> "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> 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.
>
> stark(at)oxford:/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