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

On Tue, Dec 11, 2012 at 2:50 AM, Patryk Sidzina
<patryk(dot)sidzina(at)gmail(dot)com> wrote:

> 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);

The culprit is the commit below. I don't know exactly why this slows
down your case. A preliminary oprofile analysis suggests that it most
of the slowdown is that it calls AllocSetAlloc more often. I suspect
that this slow-down will be considered acceptable trade-off for
getting good parameterized plans.

commit e6faf910d75027bdce7cd0f2033db4e912592bcc
Author: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Date: Fri Sep 16 00:42:53 2011 -0400

Redesign the plancache mechanism for more flexibility and efficiency.

Cheers,

Jeff