Queries in plpgsql are 6 times slower on partitioned tables

From: Marcin Barczyński <mba(dot)ogolny(at)gmail(dot)com>
To: pgsql-performance(at)lists(dot)postgresql(dot)org
Subject: Queries in plpgsql are 6 times slower on partitioned tables
Date: 2020-01-16 13:21:57
Message-ID: CAP3o3Pd6K5c-N_PNk2tax0RqFyOEYbLb2raG-_4_H7--mgqHUg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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)

Browse pgsql-performance by date

  From Date Subject
Next Message Cosmin Prund 2020-01-16 14:06:06 Bad query plan decision when using multiple column index - postgresql uses only first column then filters
Previous Message Michael Paquier 2020-01-15 02:48:00 Re: shared buffers and startup process