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
