Re: [PATCHES] Avg performance for int8/numeric - Mailing list pgsql-hackers

(Blast - meant to send this to -hackers not -patches....)

Neil Conway wrote:

>
>> (it is still slower than doing sum/count - possibly due to the
>> construct/deconstruct overhead of the numeric transition array).
>
> This would indeed be worth profiling. If it turns out that array
> overhead is significant, I wonder if we could use a composite type for
> the transition variable instead. That might also make it easier to
> represent the "N" value as an int8 rather than a numeric.
>

I've profiled the 2nd patch using the setup indicated below. The first
64 lines of the flat graph are attached. The complete profile is here:

http://homepages.paradise.net.nz/markir/download/postgres/postgres-avg.gprof.gz

Setup:

avg=# \d avgtest
         Table "public.avgtest"
   Column |     Type      | Modifiers
--------+---------------+-----------
   id     | integer       |
   val0   | bigint        |
   val1   | numeric(12,2) |
   val2   | numeric(10,0) |

avg=# analyze verbose avgtest;
INFO:  analyzing "public.avgtest"
INFO:  "avgtest": scanned 3000 of 87689 pages, containing 342138 live
rows and 0 dead rows; 3000 rows in sample, 10000580 estimated total rows
ANALYZE
Time: 252.033 ms
avg=# select avg(val2) from avgtest;
           avg
---------------------
   714285.214285800000
(1 row)

Time: 35196.028 ms
avg=# \q

regards

Mark

Flat profile:

Each sample counts as 0.01 seconds.
  %   cumulative   self              self     total
 time   seconds   seconds    calls   s/call   s/call  name
 14.42      2.16     2.16 100002977     0.00     0.00  AllocSetAlloc
  9.08      3.52     1.36 20000000     0.00     0.00  add_abs
  5.54      4.35     0.83 10000000     0.00     0.00  slot_deform_tuple
  5.41      5.16     0.81 60001673     0.00     0.00  AllocSetFree
  4.34      5.81     0.65 10000000     0.00     0.00  construct_md_array
  4.21      6.44     0.63 20000003     0.00     0.00  make_result
  3.54      6.97     0.53 10000000     0.00     0.00  numeric_add
  3.27      7.46     0.49 30000003     0.00     0.00  set_var_from_num
  3.00      7.91     0.45 100002652     0.00     0.00  MemoryContextAlloc
  2.74      8.32     0.41 10000001     0.00     0.00  heapgettup_pagemode
  2.54      8.70     0.38 10000000     0.00     0.00  advance_transition_function
  2.40      9.06     0.36 30000006     0.00     0.00  alloc_var
  2.27      9.40     0.34 10000000     0.00     0.00  do_numeric_avg_accum
  2.00      9.70     0.30 10000001     0.00     0.00  CopyArrayEls
  2.00     10.00     0.30 10000000     0.00     0.00  numeric_inc
  1.94     10.29     0.29 20000002     0.00     0.00  ArrayGetNItems
  1.94     10.58     0.29 10000001     0.00     0.00  deconstruct_array
  1.87     10.86     0.28 20000002     0.00     0.00  ArrayCastAndSet
  1.74     11.12     0.26 60001672     0.00     0.00  pfree
  1.67     11.37     0.25 10000001     0.00     0.00  slot_getattr
  1.60     11.61     0.24 10000000     0.00     0.00  advance_aggregates
  1.54     11.84     0.23 40000006     0.00     0.00  free_var
  1.54     12.07     0.23 10000001     0.00     0.00  datumCopy
  1.47     12.29     0.22 10000001     0.00     0.00  SeqNext
  1.40     12.50     0.21 20000000     0.00     0.00  add_var
  1.34     12.70     0.20 20000003     0.00     0.00  strip_var
  1.34     12.90     0.20 10000001     0.00     0.00  ExecScan
  1.27     13.09     0.19 10000003     0.00     0.00  AllocSetReset
  1.20     13.27     0.18 10000003     0.00     0.00  ExecProcNode
  1.13     13.44     0.17 70000010     0.00     0.00  pg_detoast_datum
  0.93     13.58     0.14 10000000     0.00     0.00  numeric_avg_accum
  0.93     13.72     0.14        2     0.07     6.61  ExecAgg
  0.87     13.85     0.13 10000001     0.00     0.00  datumGetSize
  0.87     13.98     0.13    87860     0.00     0.00  heapgetpage
  0.73     14.09     0.11 10000001     0.00     0.00  DirectFunctionCall2
  0.73     14.20     0.11 10000000     0.00     0.00  construct_array
  0.60     14.29     0.09 10000148     0.00     0.00  DirectFunctionCall1
  0.53     14.37     0.08 10000001     0.00     0.00  ExecStoreTuple
  0.53     14.45     0.08 10000000     0.00     0.00  HeapTupleSatisfiesSnapshot
  0.40     14.51     0.06 10000103     0.00     0.00  heap_getnext
  0.33     14.56     0.05   254419     0.00     0.00  hash_search_with_hash_value
  0.27     14.60     0.04 10000001     0.00     0.00  MemoryContextReset
  0.27     14.64     0.04 10000000     0.00     0.00  ExecEvalVar
  0.27     14.68     0.04 10000000     0.00     0.00  XidInSnapshot
  0.27     14.72     0.04   511482     0.00     0.00  LWLockRelease
  0.27     14.76     0.04   164939     0.00     0.00  hash_any
  0.27     14.80     0.04    87760     0.00     0.00  StrategyGetBuffer
  0.20     14.83     0.03 10000009     0.00     0.00  TransactionIdPrecedes
  0.20     14.86     0.03    87760     0.00     0.00  FileRead
  0.13     14.88     0.02 10000001     0.00     0.00  ExecSeqScan
  0.13     14.90     0.02   511481     0.00     0.00  LWLockAcquire
  0.13     14.92     0.02    88217     0.00     0.00  ReadBuffer
  0.13     14.94     0.02    87760     0.00     0.00  TerminateBufferIO
  0.07     14.95     0.01   175906     0.00     0.00  ResourceOwnerForgetBuffer
  0.07     14.96     0.01   163587     0.00     0.00  get_hash_value
  0.07     14.97     0.01    88019     0.00     0.00  ReleaseBuffer
  0.07     14.98     0.01    87760     0.00     0.00  PinBuffer_Locked
  0.00     14.98     0.00   176868     0.00     0.00  LockBuffer