On Mon, Dec 10, 2012 at 4:53 AM, Jeff Janes <jeff.ja...@gmail.com> wrote:
> On Wed, Dec 5, 2012 at 4:09 AM, Patryk Sidzina <patryk.sidz...@gmail.com> > wrote: > > > > CREATE TEMP TABLE test_table_md_speed(id serial primary key, n integer); > > > > CREATE OR REPLACE FUNCTION TEST_DB_SPEED(cnt integer) RETURNS text AS $$ > > DECLARE > > time_start timestamp; > > time_stop timestamp; > > time_total interval; > > BEGIN > > time_start := cast(timeofday() AS TIMESTAMP); > > FOR i IN 1..cnt LOOP > > INSERT INTO test_table_md_speed(n) VALUES (i); > > END LOOP; > > time_stop := cast(timeofday() AS TIMESTAMP); > > time_total := time_stop-time_start; > > > > RETURN extract (milliseconds from time_total); > > END; > > $$ LANGUAGE plpgsql; > > > > > > SELECT test_db_speed(1000000); > > > > I see strange results. For PostgreSQL 9.1.5 I get "8254.769", and for > 9.2.1 > > I get: "9022.219". This means that new version is slower. I cannot find > why. > > > > Any ideas why those results differ? > > Did you just run it once each? > > The run-to-run variability in timing can be substantial. > > I put the above into a custom file for "pgbench -f sidzina.sql -t 1 -p > $port" and run it on both versions in random order for several hundred > iterations. There was no detectable difference in timing. > > Sorry for the mix up. The above results are from one of our test machines. I wanted to simplify the function as much as possible. Unfortunately, I didn't test this on a different machine. I did that after your post and like you said, there isn't much difference in the results. The differences come up when you change the "INSERT" to "EXECUTE 'INSERT'" ( and i checked this time on 3 machines, one of which was Windows): CREATE TEMP TABLE test_table_md_speed(id serial primary key, n integer); CREATE OR REPLACE FUNCTION test_db_speed(cnt integer) RETURNS text LANGUAGE plpgsql AS $function$ DECLARE time_start timestamp; time_stop timestamp; time_total interval; BEGIN time_start := cast(timeofday() AS TIMESTAMP); FOR i IN 1..cnt LOOP EXECUTE 'INSERT INTO test_table_md_speed(n) VALUES (' || i || ')'; END LOOP; time_stop := cast(timeofday() AS TIMESTAMP); time_total := time_stop-time_start; RETURN extract (milliseconds from time_total); END; $function$; SELECT test_db_speed(100000); I run the above several times and get "4029.356" on PGSQL 9.1.6 and "5015.073" on PGSQL 9.2.1. Again, sorry for not double checking my results. -- Patryk Sidzina