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
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Reply via email to