Hi there! Recently I noticed a performance issue on temporary relation. The issue will happened on ON COMMIT DELETE temporary relations. If one session only create a few temporary relations, well, it's fine. But if one session creates plenty of ON COMMIT DELETE kind temporary relations, say 3,000, it will face a significant performance degradation issue. Check below:
One temporary relation
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
psql (19devel)
Type "help" for help.
postgres=# CREATE LOCAL TEMP TABLE a_lttk1(n INT) ON COMMIT DELETE ROWS;
CREATE TABLE
postgres=# \timing
Timing is on.
postgres=# SELECT count(*) FROM a_lttk1;
count
-------
0
(1 row)
Time: 3.004 ms
postgres=# SELECT count(*) FROM a_lttk1;
count
-------
0
(1 row)
Time: 3.884 ms
postgres=# SELECT count(*) FROM a_lttk1;
count
-------
0
(1 row)
Time: 4.041 ms
postgres=# SELECT count(*) FROM a_lttk1;
count
-------
0
(1 row)
Time: 3.827 ms
postgres=# SELECT count(*) FROM a_lttk1;
count
-------
0
(1 row)
Time: 3.783 ms
3,000 temporary relation
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
psql (19devel)
Type "help" for help.
postgres=# DO $$
postgres$# DECLARE
postgres$# v_sql VARCHAR(100);
postgres$# BEGIN
postgres$# FOR i IN 1..3000 LOOP
postgres$# v_sql := 'CREATE LOCAL TEMP TABLE a_lttk'||i||'(n INT) ON COMMIT
DELETE ROWS';
postgres$# EXECUTE v_sql;
postgres$# END LOOP;
postgres$# END;
postgres$# $$ LANGUAGE plpgsql;
DO
postgres=# \timing
Timing is on.
postgres=# SELECT count(*) FROM a_lttk1;
count
-------
0
(1 row)
Time: 45.471 ms
postgres=# SELECT count(*) FROM a_lttk1;
count
-------
0
(1 row)
Time: 27.320 ms
postgres=# SELECT count(*) FROM a_lttk1;
count
-------
0
(1 row)
Time: 27.482 ms
postgres=# SELECT count(*) FROM a_lttk1;
count
-------
0
(1 row)
Time: 26.907 ms
postgres=# SELECT count(*) FROM a_lttk1;
count
-------
0
(1 row)
Time: 31.055 ms
postgres=# SELECT count(*) FROM a_lttk1;
count
-------
0
(1 row)
Time: 28.624 ms
postgres=# SELECT count(*) FROM a_lttk1;
count
-------
0
(1 row)
Time: 25.277 ms
The performance has decreased by nearly 10 times. The reason is we just check
if there is
operation on any ON COMMIT DELETE kind temporary relations. Regardless of how
many temporary
tables are actually accessed, even if only one is accessed, it will do the
truncate on all
the temporary tables.
To overcome this issue, A new list named in_use has been introduced to record
the actually
accessed temporary relations, and then will do the truncate only on the
actually accessed
temporary relations. And it seems works well.
After patch:
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
psql (19devel)
Type "help" for help.
postgres=# DO $$
postgres$# DECLARE
postgres$# v_sql VARCHAR(100);
postgres$# BEGIN
postgres$# FOR i IN 1..3000 LOOP
postgres$# v_sql := 'CREATE LOCAL TEMP TABLE a_lttk'||i||'(n INT) ON COMMIT
DELETE ROWS';
postgres$# EXECUTE v_sql;
postgres$# END LOOP;
postgres$# END;
postgres$# $$ LANGUAGE plpgsql;
DO
postgres=# \timing
Timing is on.
postgres=# SELECT count(*) FROM a_lttk1;
count
-------
0
(1 row)
Time: 5.253 ms
postgres=# SELECT count(*) FROM a_lttk1;
count
-------
0
(1 row)
Time: 5.512 ms
postgres=# SELECT count(*) FROM a_lttk1;
count
-------
0
(1 row)
Time: 5.095 ms
postgres=# SELECT count(*) FROM a_lttk1;
count
-------
0
(1 row)
Time: 5.119 ms
postgres=# SELECT count(*) FROM a_lttk1;
count
-------
0
(1 row)
Time: 5.057 ms
postgres=# SELECT count(*) FROM a_lttk1;
count
-------
0
(1 row)
Time: 5.006 ms
Regards,
Jet
Halo Tech (www.halodbtech.com)
openHalo (www.openhalo.org)
improve_temporary_rel_performance_v01.patch
Description: Binary data
