Hi,
I've been trying to diagnose a severe performance regression we've been
having in one of our plpgsql procedures.
The example below is of course extremely simplified, and obviously not
what we are really doing in the database, but it exhibits the slowdown
between 9.1.9 and 9.2.4.
So here is the example:
create table table_test_int (col_01 int);
create table table_test_numeric (col_01 numeric);
CREATE OR REPLACE FUNCTION public.test_insert(nb integer)RETURNS textLANGUAGE plpgsql
AS $function$
DECLARE
time_start timestamp;
time_stop timestamp;
tmp_numeric numeric;
BEGIN
time_start :=clock_timestamp();
FOR i IN 1..nb LOOP INSERT INTO table_test_int(col_01) VALUES (i);
END LOOP;
time_stop :=clock_timestamp();
RAISE NOTICE 'time for int:%',time_stop-time_start;
time_start :=clock_timestamp();
FOR i IN 1..nb LOOP INSERT INTO table_test_numeric(col_01) VALUES (i);
END LOOP;
time_stop :=clock_timestamp();
RAISE NOTICE 'time for numeric:%',time_stop-time_start;
time_start :=clock_timestamp();
FOR i IN 1..nb LOOP INSERT INTO table_test_numeric(col_01) VALUES (i::numeric);
END LOOP;
time_stop :=clock_timestamp();
RAISE NOTICE 'time for numeric, casted:%',time_stop-time_start;
time_start :=clock_timestamp();
FOR i IN 1..nb LOOP tmp_numeric:=cast(i as numeric); INSERT INTO table_test_numeric(col_01) VALUES
(tmp_numeric);
END LOOP;
time_stop :=clock_timestamp();
RAISE NOTICE 'time for numeric with tmp variable:%',time_stop-time_start;
RETURN 1;
END;
$function$
;
It just inserts nb records in a loop in 4 different maneers:
- Directly in an int field
- Then in a numeric field (that's where we're having problems)
- Then in the same numeric field, but trying a cast (it doesn't change a
thing)
- Then tries with an intermediary temp variable of numeric type (which
solves the problem).
Here are the runtimes (tables were truncated beforehand):
9.1.9:
select test_insert(1000000);
NOTICE: time for int:00:00:09.526009
NOTICE: time for numeric:00:00:10.557126
NOTICE: time for numeric, casted:00:00:10.821369
NOTICE: time for numeric with tmp variable:00:00:10.850847
9.2.4:
select test_insert(1000000);
NOTICE: time for int:00:00:09.477044
NOTICE: time for numeric:00:00:24.757032 <----
NOTICE: time for numeric, casted:00:00:24.791016 <----
NOTICE: time for numeric with tmp variable:00:00:10.89332
I really don't know exactly where the problem comes from… but it's been
hurting a function very badly (there are several of these static queries
with types mismatch). And of course, the problem is not limited to
numeric… text has the exact same problem.
Regards,
Marc
