Problem with default partition pruning

From: "Yuzuko Hosoya" <hosoya(dot)yuzuko(at)lab(dot)ntt(dot)co(dot)jp>
To: "'PostgreSQL Hackers'" <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Cc: "'Amit Langote'" <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>
Subject: Problem with default partition pruning
Date: 2019-02-22 08:14:04
Message-ID: 00e601d4ca86$932b8bc0$b982a340$
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers


I found the bug of default partition pruning when executing a range query.

postgres=# create table test1(id int, val text) partition by range (id);
postgres=# create table test1_1 partition of test1 for values from (0) to (100);
postgres=# create table test1_2 partition of test1 for values from (150) to (200);
postgres=# create table test1_def partition of test1 default;

postgres=# explain select * from test1 where id > 0 and id < 30;
Append (cost=0.00..11.83 rows=59 width=11)
-> Seq Scan on test1_1 (cost=0.00..5.00 rows=58 width=11)
Filter: ((id > 0) AND (id < 30))
-> Seq Scan on test1_def (cost=0.00..6.53 rows=1 width=12)
Filter: ((id > 0) AND (id < 30))
(5 rows)

There is no need to scan the default partition, but it's scanned.

In the current implement, whether the default partition is scanned
or not is determined according to each condition of given WHERE
clause at get_matching_range_bounds(). In this example, scan_default
is set true according to id > 0 because id >= 200 matches the default
partition. Similarly, according to id < 30, scan_default is set true.
Then, these results are combined according to AND/OR at perform_pruning_combine_step().
In this case, final result's scan_default is set true.

The modifications I made are as follows:
- get_matching_range_bounds() determines only offsets of range bounds
according to each condition
- These results are combined at perform_pruning_combine_step()
- Whether the default partition is scanned or not is determined at

Attached the patch. Any feedback is greatly appreciated.

Best regards,
Yuzuko Hosoya
NTT Open Source Software Center

Attachment Content-Type Size
default_partition_pruning.patch application/octet-stream 12.5 KB


Browse pgsql-hackers by date

  From Date Subject
Next Message Etsuro Fujita 2019-02-22 08:17:24 Re: Problems with plan estimates in postgres_fdw
Previous Message Haribabu Kommi 2019-02-22 08:10:49 Re: pg_basebackup ignores the existing data directory permissions