RE: Problem with default partition pruning

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