Tom Lane <tgl@sss.pgh.pa.us> writes:
> a_ogawa <a_ogawa@hi-ho.ne.jp> writes:
> > In SQL that executes aggregation, AllocSetReset is called many times and
> > spend a lot of cycles.
> > This patch saves the cycles spent by AllocSetReset.
>
> Hmm. It doesn't seem like this could be a big win overall. It's not
> possible to save a whole lot of cycles inside AllocSetReset, because if
> there isn't anything for it to do, it should fall through pretty quickly
> anyway.
I thought that I was able to avoid MemSet in AllocSetReset.
MemSet(set->freelist, 0, sizeof(set->freelist));
My profile result in previous mail is as follows:
% cumulative self self total
time seconds seconds calls s/call s/call name
9.20 3.06 3.06 38500155 0.00 0.00 AllocSetReset
Therefore, sizeof(set->freelist) * (number of calls) =
44 bytes * 38500155 = 1615 Mbytes.
> And I'm worried about adding even a small amount of overhead to
> palloc/pfree --- on the vast majority of the profiles I look at, those
> are more expensive than AllocSetReset.
I don't worry about palloc. Because overhead increases only when malloc
is executed in AllocSetAlloc. But I'm wooried about pfree, too. However,
when palloc/pfree was executed many times, I did not see a bad influence.
It is a result of executing 'select * from accounts' 20 times as follows.
original code:
Each sample counts as 0.01 seconds.
% cumulative self self total
time seconds seconds calls s/call s/call name
6.79 4.03 4.03 90000599 0.00 0.00 appendBinaryStringInfo
6.57 7.93 3.90 50005879 0.00 0.00 AllocSetAlloc
5.63 11.27 3.34 10000000 0.00 0.00 printtup
5.61 14.60 3.33 10000000 0.00 0.00 slot_deform_tuple
5.36 17.78 3.18 50001421 0.00 0.00 AllocSetFree
patched code:
Each sample counts as 0.01 seconds.
% cumulative self self total
time seconds seconds calls s/call s/call name
8.07 4.78 4.78 90000599 0.00 0.00 appendBinaryStringInfo
7.23 9.06 4.28 50005879 0.00 0.00 AllocSetAlloc
5.40 12.26 3.20 10000000 0.00 0.00 printtup
5.20 15.34 3.08 10000000 0.00 0.00 slot_deform_tuple
5.13 18.38 3.04 50001421 0.00 0.00 AllocSetFree
I think that it is difficult to measure the influence that this patch
gives palloc/pfree.
> I duplicated your test case to see where the reset calls were coming
> from, and got this:
>
> (Does this match your profile? I only ran the query 5 times not 10.)
I'm sorry. My profile in previous mail were 11 times not 10. And your
profile and my profile are match.
> This shows that the majority of the resets are coming from the hashjoin
> code not the aggregation code.
You are right. I measured where MemoryContextReset had been called.
(The SQL was executed once)
filename(line) function number of calls
-------------------------------------------------------------
execGrouping.c(65) execTuplesMatch 499995
execScan.c(86) ExecScan 500007
nodeAgg.c(924) agg_fill_hash_table 500000
nodeAgg.c(979) agg_retrieve_hash_table 5
nodeHash.c(669) ExecHashGetHashValue 500005
nodeHash.c(785) ExecScanHashBucket 500000
nodeHashjoin.c(108) ExecHashJoin 500001
nodeHashjoin.c(217) ExecHashJoin 500000
-------------------------------------------------------------
Total 3500013
Many are the one from hashjoin. Other is the one from grouping,
table/index scan, and aggregation by hash.
And I measured the number of times that was able to avoid MemSet in
AllocSetReset.
avoided MemSet 3500008
executed MemSet 7
---------------------------------------
Total 3500015
(The execution time of AllocSetReset is more twice than MemoryContextReset
because there is MemoryContextResetAndDeleteChildren in PostgresMain)
regards,
---
Atsushi Ogawa
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to
majordomo@postgresql.org)