bug in PG13? - Mailing list pgsql-general

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