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

Hello

2011/6/20 Simon Riggs <simon@2ndquadrant.com>:
> On Mon, Jun 20, 2011 at 10:49 AM, Pavel Stehule <pavel.stehule@gmail.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
>