Re: Add min and max execute statement time in pg_stat_statement

On Wed, Jan 29, 2014 at 8:48 PM, KONDO Mitsumasa
<kondo(dot)mitsumasa(at)lab(dot)ntt(dot)co(dot)jp> wrote:
> I'd like to know the truth and the fact in your patch, rather than your
> argument:-)

I see.

> [part of sample sqls file, they are executed in pgbench]
> SELECT
> 1-1/9+5*8*6+5+9-6-3-4/9%7-2%7/5-9/7+2+9/7-1%5%9/3-4/4-9/1+5+5/6/5%2+1*2*3-8/8+5-3-8-4/8+5%2*2-2-5%6+4
> SELECT
> 1%9%7-8/5%6-1%2*2-7+9*6-2*6-9+1-2*9+6+7*8-4-2*1%3/7-1%4%9+4+7/5+4/2-3-5%8/3/7*6-1%8*6*1/7/2%5*6/2-3-9
> SELECT
> 1%3*2/8%6%5-3%1+1/6*6*5/9-2*5+6/6*5-1/2-6%4%8/7%2*7%5%9%5/9%1%1-3-9/2*1+1*6%8/2*4/1+6*7*1+5%6+9*1-9*6

> [methods]
> method 1: with old pgss, pg_stat_statements.max=1000(default)
> method 2: with old pgss with my patch, pg_stat_statements.max=1000(default)
> peter 1 : with new pgss(Peter's patch), pg_stat_statements.max=5000(default)
> peter 2 : with new pgss(Peter's patch), pg_stat_statements.max=1000
>
> [for reference]
> method 5:with old pgss, pg_stat_statements.max=5000
> method 6:with old pgss with my patch, pg_stat_statements.max=5000
>
> [results]
> method | try1 | try2 | try3 | degrade performance ratio
> -----------------------------------------------------------------
> method 1 | 6.546 | 6.558 | 6.638 | 0% (reference score)
> method 2 | 6.527 | 6.556 | 6.574 | 1%
> peter 1 | 5.204 | 5.203 | 5.216 |20%
> peter 2 | 4.241 | 4.236 | 4.262 |35%
>
> method 5 | 5.931 | 5.848 | 5.872 |11%
> method 6 | 5.794 | 5.792 | 5.776 |12%

So, if we compare the old pg_stat_statements and old default with the
new pg_stat_statements and the new default (why not? The latter still
uses less shared memory than the former), by the standard of this
benchmark there is a regression of about 20%. But you also note that
there is an 11% "regression" in the old pg_stat_statements against
*itself* when used with a higher pg_stat_statements.max setting of
5,000. This is completely contrary to everyone's prior understanding
that increasing the size of the hash table had a very *positive*
effect on performance by relieving cache pressure and thereby causing
less exclusive locking for an entry_dealloc().

Do you suppose that this very surprising result might just be because
this isn't a very representative benchmark? Nothing ever has the
benefit of *not* having to exclusive lock.

--
Peter Geoghegan