Re: massive speedup on temp table creation/destruction?

On 8/31/06, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> I was just trying (unsuccessfully so far) to replicate Csaba Nagy's
> report of a strange failure with temp table creation. I made use
> of pgbench's recent improvements to be able to push random scripts
> at a collection of backends:
>
> $ cat ttscript.sql
> create temp table foo (f1 int);
> drop table foo;
> $ pgbench -n -c 98 -t 1000 -f ttscript.sql bench
>
> This is just pushing a long string of create temp table/drop table
> commands at a whole lot of backends concurrently.
>
> What I found surprising is that I get numbers like 430 tps from HEAD
> and 220 tps from REL8_1_STABLE. This test case is of course not about
> performance, but I'm not quite sure what we changed that would produce
> a 2X speedup from 8.1. Can anyone else replicate this?

humm, you had me curious, so i ran the test.

postgresql. 8.2 ~ 2 weeks recent
number of clients: 98
number of transactions per client: 100
number of transactions actually processed: 9800/9800
tps = 216.424848 (including connections establishing)
tps = 217.558450 (excluding connections establishing)

8.1:
number of clients: 98
number of transactions per client: 100
number of transactions actually processed: 9800/9800
tps = 262.430496 (including connections establishing)
tps = 263.977714 (excluding connections establishing)

I did not confirm your results unfortunately. this is on quad opteron,
totally stock freshly minted database cluster :-)

I then did a cvs update on 8.2 just in case, and got:
number of clients: 98
number of transactions per client: 100
number of transactions actually processed: 9800/9800
tps = 223.026519 (including connections establishing)
tps = 224.233746 (excluding connections establishing)

note i only did a 100 transactions, so the results are expected to be
a bit more wild.

merlin