Queries in plpgsql are 6 times slower on partitioned tables

[Marcin Barczyński <mba.ogolny@xxxxxxxxx>]

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)