RE: Problem with default partition pruning

From: "Yuzuko Hosoya" <hosoya(dot)yuzuko(at)lab(dot)ntt(dot)co(dot)jp>
To: "'Amit Langote'" <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>, "'Thibaut'" <thibaut(dot)madelaine(at)dalibo(dot)com>, "'Imai, Yoshikazu'" <imai(dot)yoshikazu(at)jp(dot)fujitsu(dot)com>
Cc: "'PostgreSQL Hackers'" <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: RE: Problem with default partition pruning
Date: 2019-04-04 04:00:55
Message-ID: 00a301d4ea9b$01296eb0$037c4c10$
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers


Thanks for the comments.

> Thanks for dividing patches that way.
> Would it be a good idea to add some new test cases to these patches, just so it's easily apparent what
> we're changing?
Yes, I agree with you.

> So, we could add the test case presented by Thibaut at the following link to the
> default_partition_pruning.patch:
> And, another reported at the following link to
> ignore_contradictory_where_clauses_at_partprune_step.patch:
> Actually, it might be possible/better to construct the test queries in partition_prune.sql using the
> existing tables in that script, that is, without defining new tables just for adding the new test cases.
> If not, maybe it's OK to create the new tables too.
I see. I added some test cases to each patch according to tests
discussed in this thread.

However, I found another problem as follows. This query should
output "One-Time Filter: false" because rlp3's constraints
contradict WHERE clause.

postgres=# \d+ rlp3
Partitioned table "public.rlp3"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
b | character varying | | | | extended | |
a | integer | | | | plain | |
Partition of: rlp FOR VALUES FROM (15) TO (20)
Partition constraint: ((a IS NOT NULL) AND (a >= 15) AND (a < 20))
Partition key: LIST (b varchar_ops)
Partitions: rlp3abcd FOR VALUES IN ('ab', 'cd'),
rlp3efgh FOR VALUES IN ('ef', 'gh'),
rlp3nullxy FOR VALUES IN (NULL, 'xy'),
rlp3_default DEFAULT

postgres=# explain select * from rlp3 where a = 2;
Append (cost=0.00..103.62 rows=24 width=36)
-> Seq Scan on rlp3abcd (cost=0.00..25.88 rows=6 width=36)
Filter: (a = 2)
-> Seq Scan on rlp3efgh (cost=0.00..25.88 rows=6 width=36)
Filter: (a = 2)
-> Seq Scan on rlp3nullxy (cost=0.00..25.88 rows=6 width=36)
Filter: (a = 2)
-> Seq Scan on rlp3_default (cost=0.00..25.88 rows=6 width=36)
Filter: (a = 2)
(9 rows)

I think that the place of check contradiction process was wrong
At ignore_contradictory_where_clauses_at_partprune_step.patch.
So I fixed it.

Attached the latest patches. Please check it again.

Best regards,
Yuzuko Hosoya

Attachment Content-Type Size
v2_ignore_contradictory_where_clauses_at_partprune_step.patch application/octet-stream 4.3 KB
v4_default_partition_pruning.patch application/octet-stream 13.4 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Kyotaro HORIGUCHI 2019-04-04 04:01:40 message when starting standby without setting restore_command
Previous Message Michael Paquier 2019-04-04 03:48:00 Re: [PATCH v20] GSSAPI encryption support