Hi all,
it seems to me a bug. i have a partitioned table:
test=*# select version();
version
---------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 13.0 (Ubuntu 13.0-1.pgdg18.04+1) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit
(1 row)
test=*# \d+ kunden
Partitioned table
"public.kunden"
Column | Type | Collation | Nullable | Default |
Storage | Stats target | Description
------------+---------+-----------+----------+------------------------------------+----------+--------------+-------------
id | integer | | not null |
nextval('kunden_id_seq'::regclass) | plain | |
kundenname | text | | not null
| | extended | |
datum | date | | not null
| | plain | |
Partition key: HASH (kundenname)
Indexes:
"kunden_pkey" PRIMARY KEY, btree (id, kundenname, datum)
Partitions: kunden_0 FOR VALUES WITH (modulus 4, remainder 0), PARTITIONED,
kunden_1 FOR VALUES WITH (modulus 4, remainder 1), PARTITIONED,
kunden_2 FOR VALUES WITH (modulus 4, remainder 2), PARTITIONED,
kunden_3 FOR VALUES WITH (modulus 4, remainder 3), PARTITIONED
test=*# \d+ kunden_0
Partitioned table
"public.kunden_0"
Column | Type | Collation | Nullable | Default |
Storage | Stats target | Description
------------+---------+-----------+----------+------------------------------------+----------+--------------+-------------
id | integer | | not null |
nextval('kunden_id_seq'::regclass) | plain | |
kundenname | text | | not null
| | extended | |
datum | date | | not null
| | plain | |
Partition of: kunden FOR VALUES WITH (modulus 4, remainder 0)
Partition constraint: satisfies_hash_partition('16574'::oid, 4, 0,
kundenname)
Partition key: RANGE (datum)
Indexes:
"kunden_0_pkey" PRIMARY KEY, btree (id, kundenname, datum)
Partitions: kunden_0_2015 FOR VALUES FROM ('2015-01-01') TO ('2016-01-01'),
kunden_0_2016 FOR VALUES FROM ('2016-01-01') TO ('2017-01-01'),
kunden_0_2017 FOR VALUES FROM ('2017-01-01') TO ('2018-01-01'),
kunden_0_2018 FOR VALUES FROM ('2018-01-01') TO ('2019-01-01'),
kunden_0_2019 FOR VALUES FROM ('2019-01-01') TO ('2020-01-01'),
kunden_0_default DEFAULT
test=*#
this plan seems okay, partition pruning is working as expected:
test=*# explain analyse select * from kunden where kundenname = 'Kunde
11' and datum = current_date;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.15..5030.91 rows=6 width=40) (actual
time=10.068..14.326 rows=0 loops=1)
Workers Planned: 1
Workers Launched: 1
-> Parallel Append (cost=0.15..4030.31 rows=6 width=40) (actual
time=0.004..0.005 rows=0 loops=2)
Subplans Removed: 5
-> Parallel Index Only Scan using kunden_0_default_pkey on
kunden_0_default kunden_1 (cost=0.15..20.16 rows=1 width=40) (actual
time=0.008..0.008 rows=0 loops=1)
Index Cond: ((kundenname = 'Kunde 11'::text) AND (datum
= CURRENT_DATE))
Heap Fetches: 0
Planning Time: 0.303 ms
Execution Time: 14.364 ms
(10 rows)
but, if i switch ``parallel_leader_participation`` to off, the plan
changed in a bad way:
test=*# set parallel_leader_participation to off;
SET
test=*# explain analyse select * from kunden where kundenname = 'Kunde
11' and datum = current_date;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..4833.46 rows=6 width=21) (actual
time=37.188..40.386 rows=0 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Append (cost=0.00..3832.86 rows=2 width=21) (actual
time=23.559..23.562 rows=0 loops=2)
-> Seq Scan on kunden_0_2016 kunden_2 (cost=0.00..1446.92
rows=1 width=17) (actual time=12.094..12.094 rows=0 loops=1)
Filter: ((kundenname = 'Kunde 11'::text) AND (datum =
CURRENT_DATE))
Rows Removed by Filter: 60624
-> Seq Scan on kunden_0_2015 kunden_1 (cost=0.00..1445.22
rows=1 width=17) (actual time=10.313..10.313 rows=0 loops=1)
Filter: ((kundenname = 'Kunde 11'::text) AND (datum =
CURRENT_DATE))
Rows Removed by Filter: 60527
-> Seq Scan on kunden_0_2017 kunden_3 (cost=0.00..1442.67
rows=1 width=17) (actual time=10.051..10.051 rows=0 loops=1)
Filter: ((kundenname = 'Kunde 11'::text) AND (datum =
CURRENT_DATE))
Rows Removed by Filter: 60438
-> Seq Scan on kunden_0_2018 kunden_4 (cost=0.00..1442.23
rows=1 width=17) (actual time=9.234..9.234 rows=0 loops=1)
Filter: ((kundenname = 'Kunde 11'::text) AND (datum =
CURRENT_DATE))
Rows Removed by Filter: 60413
-> Seq Scan on kunden_0_2019 kunden_5 (cost=0.00..944.97
rows=1 width=17) (actual time=5.393..5.393 rows=0 loops=1)
Filter: ((kundenname = 'Kunde 11'::text) AND (datum =
CURRENT_DATE))
Rows Removed by Filter: 39598
-> Index Only Scan using kunden_0_default_pkey on
kunden_0_default kunden_6 (cost=0.15..20.17 rows=1 width=40) (actual
time=0.025..0.025 rows=0 loops=1)
Index Cond: ((kundenname = 'Kunde 11'::text) AND (datum
= CURRENT_DATE))
Heap Fetches: 0
Planning Time: 0.724 ms
Execution Time: 40.425 ms
(24 rows)
Regards, Andreas
--
EDB & 2ndQuadrant
