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

[Patryk Sidzina <patryk.sidzina@xxxxxxxxx>]

On Mon, Dec 10, 2012 at 4:53 AM, Jeff Janes <jeff.janes@xxxxxxxxx> wrote:

On Wed, Dec 5, 2012 at 4:09 AM, Patryk Sidzina <patryk.sidzina@xxxxxxxxx> 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