From: | Patryk Sidzina <patryk(dot)sidzina(at)gmail(dot)com> |
---|---|
To: | Jeff Janes <jeff(dot)janes(at)gmail(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Why is PostgreSQL 9.2 slower than 9.1 in my tests? |
Date: | 2012-12-11 10:50:59 |
Message-ID: | CAD4+=qXu14wW7qkgkCiGRaeQ9SSLXBGMra07RMLCm_4oJE51=A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Mon, Dec 10, 2012 at 4:53 AM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
> On Wed, Dec 5, 2012 at 4:09 AM, Patryk Sidzina <patryk(dot)sidzina(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Craig Ringer | 2012-12-11 13:29:00 | Re: Do I have a hardware or a software problem? |
Previous Message | Niels Kristian Schjødt | 2012-12-11 10:04:46 | Re: Do I have a hardware or a software problem? |