Re: AllocSetReset improvement - Mailing list pgsql-patches

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