Performance problem in PLPgSQL
От | Marc Cousin |
---|---|
Тема | Performance problem in PLPgSQL |
Дата | |
Msg-id | 51EE54BE.3090606@gmail.com обсуждение исходный текст |
Ответы |
Re: Performance problem in PLPgSQL
(Tom Lane <tgl@sss.pgh.pa.us>)
Re: Performance problem in PLPgSQL (Fábio Telles Rodriguez <fabio.telles@gmail.com>) |
Список | pgsql-hackers |
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
В списке pgsql-hackers по дате отправления: