Re: patch: plpgsql - remove unnecessary ccache search when a array variable is updated

Hello

2011/6/20 Simon Riggs <simon(at)2ndquadrant(dot)com>:
> On Mon, Jun 20, 2011 at 10:49 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>
>> this patch significantly reduce a ccache searching. On my test - bubble sort
>
> It sounds good, but also somewhat worrying.
>
> The first cache is slow, so we add another cache to avoid searching
> the first cache.
>
> What is making the first cache so slow?

a using of general cache should be slower than direct access to
memory. The slow down is based on catalog operations - hash
calculations, hash searching and cache validations. I don't know if it
is possible to optimize general cache.

you can compare profile of original pg

3008 13.0493 SearchCatCache
1306 5.6657 ExecEvalParamExtern
1143 4.9586 GetSnapshotData
1122 4.8675 AllocSetAlloc
1058 4.5898 MemoryContextAllocZero
1002 4.3469 ExecMakeFunctionResultNoSets
986 4.2775 ExecEvalArrayRef
851 3.6918 LWLockAcquire
783 3.3968 LWLockRelease
664 2.8806 RevalidateCachedPlan
646 2.8025 AllocSetFree
568 2.4641 array_ref
551 2.3904 CopySnapshot
519 2.2515 AllocSetReset
510 2.2125 array_set
492 2.1344 PopActiveSnapshot
381 1.6529 ArrayGetOffset
369 1.6008 AcquireExecutorLocks
348 1.5097 pfree
347 1.5054 MemoryContextAlloc
313 1.3579 bms_is_member
285 1.2364 CatalogCacheComputeHashValue
267 1.1583 PushActiveSnapshot
266 1.1540 hash_uint32
253 1.0976 pgstat_init_function_usage
233 1.0108 array_seek.clone.0

and patched postgresql's profile

3151 7.2135 AllocSetAlloc
2887 6.6091 ExecEvalParamExtern
2844 6.5107 list_member_ptr
2353 5.3867 AllocSetFree
2318 5.3065 GetSnapshotData
2201 5.0387 ExecMakeFunctionResultNoSets
2153 4.9288 LWLockAcquire
2055 4.7045 ExecEvalArrayRef
1879 4.3015 LWLockRelease
1675 3.8345 MemoryContextAllocZero
1463 3.3492 AcquireExecutorLocks
1375 3.1477 pfree
1356 3.1043 RevalidateCachedPlan
1261 2.8868 AllocSetCheck
1257 2.8776 PopActiveSnapshot
1115 2.5525 array_set
1102 2.5228 AllocSetReset
966 2.2114 CopySnapshot
938 2.1473 MemoryContextAlloc
875 2.0031 array_ref
772 1.7673 ResourceOwnerForgetPlanCacheRef
632 1.4468 array_seek.clone.0
554 1.2683 PushActiveSnapshot
499 1.1423 check_list_invariants
475 1.0874 ExecEvalConst
473 1.0828 bms_is_member
444 1.0164 ArrayGetNItems

so the most slow operation is SearchCatCache - but I am not a man who
can optimize this routine :)

Regards

Pavel Stehule

>
> --
>  Simon Riggs                   http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services
>