After migrating to a partitioned table, I noticed that a
performance-critical plpgsql function is a few times slower.
Basically, the function takes a key as an argument, and performs SELECT,
UPDATE and DELETE operations on tables partitioned by the key.
I narrowed down the problem to the following: let's have an empty table
"demo" with column "key", and two plpgsql functions that run "DELETE FROM
demo WHERE key = XYZ" 10000 times in two flavours: one takes the key by
argument, and in the other the key hardcoded.
Here are the running times:
- delete by hardcoded value from non-partitioned table: 39.807 ms
- delete by argument from non-partitioned table: 45.734 ms
- delete by hardcoded value from partitioned table: 47.101 ms
- delete by argument from partitioned table: 295.748 ms
Deleting by argument from an empty partitioned table is 6 times slower!
Why is it so? The number of partitions doesn't seem to be important. And
deleting is just an example, SELECT behaves in the same way.
Sample code:
-- partioned table
DROP TABLE IF EXISTS demo_partitioned;
CREATE TABLE demo_partitioned(key BIGINT, val BIGINT) PARTITION BY LIST
(key);
DO $$
DECLARE
i BIGINT;
BEGIN
FOR i IN SELECT * FROM generate_series(1, 15)
LOOP
EXECUTE 'CREATE TABLE demo_partitioned_key_'|| i ||' PARTITION OF
demo_partitioned FOR VALUES IN (' || i || ');';
END LOOP;
END$$;
CREATE OR REPLACE FUNCTION del_from_partitioned_by_arg(k BIGINT)
RETURNS VOID AS $$
DECLARE
i BIGINT;
BEGIN
FOR i IN SELECT * FROM generate_series(1, 10000)
LOOP
DELETE FROM demo_partitioned WHERE key = k;
END LOOP;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION del_from_partitioned_hardcoded()
RETURNS VOID AS $$
DECLARE
i BIGINT;
BEGIN
FOR i IN SELECT * FROM generate_series(1, 10000)
LOOP
DELETE FROM demo_partitioned WHERE key = 3;
END LOOP;
END;
$$ LANGUAGE plpgsql;
ANALYZE demo_partitioned;
EXPLAIN ANALYZE DELETE FROM demo_partitioned WHERE key = 3;
EXPLAIN ANALYZE SELECT * FROM del_from_partitioned_hardcoded();
EXPLAIN ANALYZE SELECT * FROM del_from_partitioned_by_arg(3);
-- non-partitioned table
DROP TABLE IF EXISTS demo_non_partitioned;
CREATE TABLE demo_non_partitioned(key BIGINT, val BIGINT);
ANALYZE demo_non_partitioned;
CREATE OR REPLACE FUNCTION del_from_non_partitioned_by_arg(k BIGINT)
RETURNS VOID AS $$
DECLARE
i BIGINT;
BEGIN
FOR i IN SELECT * FROM generate_series(1, 10000)
LOOP
DELETE FROM demo_non_partitioned WHERE key = k;
END LOOP;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION del_from_non_partitioned_hardcoded()
RETURNS VOID AS $$
DECLARE
i BIGINT;
BEGIN
FOR i IN SELECT * FROM generate_series(1, 10000)
LOOP
DELETE FROM demo_non_partitioned WHERE key = 3;
END LOOP;
END;
$$ LANGUAGE plpgsql;
EXPLAIN ANALYZE DELETE FROM demo_non_partitioned WHERE key = 3;
EXPLAIN ANALYZE SELECT * FROM del_from_non_partitioned_hardcoded();
EXPLAIN ANALYZE SELECT * FROM del_from_non_partitioned_by_arg(3);
Output:
DROP TABLE
CREATE TABLE
DO
CREATE FUNCTION
CREATE FUNCTION
ANALYZE
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Delete on demo_partitioned (cost=0.00..29.43 rows=9 width=6) (actual
time=0.002..0.002 rows=0 loops=1)
Delete on demo_partitioned_key_3
-> Seq Scan on demo_partitioned_key_3 (cost=0.00..29.43 rows=9
width=6) (actual time=0.001..0.001 rows=0 loops=1)
Filter: (key = 3)
Planning Time: 0.180 ms
Execution Time: 0.069 ms
(6 rows)
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Function Scan on del_from_partitioned_hardcoded (cost=0.05..0.06 rows=1
width=4) (actual time=47.030..47.030 rows=1 loops=1)
Planning Time: 0.020 ms
Execution Time: 47.101 ms
(3 rows)
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Function Scan on del_from_partitioned_by_arg (cost=0.05..0.06 rows=1
width=4) (actual time=295.737..295.737 rows=1 loops=1)
Planning Time: 0.023 ms
Execution Time: 295.748 ms
(3 rows)
DROP TABLE
CREATE TABLE
ANALYZE
CREATE FUNCTION
CREATE FUNCTION
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Delete on demo_non_partitioned (cost=0.00..29.43 rows=9 width=6) (actual
time=0.002..0.003 rows=0 loops=1)
-> Seq Scan on demo_non_partitioned (cost=0.00..29.43 rows=9 width=6)
(actual time=0.002..0.002 rows=0 loops=1)
Filter: (key = 3)
Planning Time: 0.046 ms
Execution Time: 0.028 ms
(5 rows)
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Function Scan on del_from_non_partitioned_hardcoded (cost=0.05..0.06
rows=1 width=4) (actual time=39.796..39.796 rows=1 loops=1)
Planning Time: 0.010 ms
Execution Time: 39.807 ms
(3 rows)
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Function Scan on del_from_non_partitioned_by_arg (cost=0.05..0.06 rows=1
width=4) (actual time=45.723..45.723 rows=1 loops=1)
Planning Time: 0.024 ms
Execution Time: 45.734 ms
(3 rows)
