Re: [PERFORM] Why is PostgreSQL 9.2 slower than 9.1 in my tests?

[Patryk Sidzina]

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