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$@lab.ntt.co.jp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Amit-san,
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:
>
> https://www.postgresql.org/message-id/a4968068-6401-7a9c-8bd4-6a3bc9164a86%40dalibo.com
>
> And, another reported at the following link to
> ignore_contradictory_where_clauses_at_partprune_step.patch:
>
> https://www.postgresql.org/message-id/bd03f475-30d4-c4d0-3d7f-d2fbde755971%40dalibo.com
>
> 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;
QUERY PLAN
--------------------------------------------------------------------
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 |
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 |