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.sidzina@gmail.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@sss.pgh.pa.us>
Date:   Fri Sep 16 00:42:53 2011 -0400

    Redesign the plancache mechanism for more flexibility and efficiency.


Cheers,

Jeff