Re: BUG #15334: Partition elimination not working as expected when using enum as partition key

On 2018/08/17 16:17, Damir Ciganović-Janković wrote:
> I know about the issue and the fix, but this is a different bug, it seems
> to me. I will try to explain the issue to be more clear.
> This is the enum, table and its partitions:
> +++++++++++++++++++++++++++++
> create type ab as enum ('A', 'B');
> create table test (key ab not null) partition by list (key);
> create table test_a partition of test for values in ('A');
> create table test_b partition of test for values in ('B');
> +++++++++++++++++++++++++++++
>
> We are using Java, so this is the code:
> +++++++++++++++++++++++++++++
> try (Connection connection = DriverManager.getConnection(...);
> PreparedStatement stmt = connection.prepareStatement("EXPLAIN SELECT *
> FROM test WHERE key = ?::ab")) {
>
> stmt.setString(1, "A");
> try (ResultSet rs = stmt.executeQuery()) {
> while (rs.next()) {
> System.out.println(rs.getString(1));
> }
> }
> }
> +++++++++++++++++++++++++++++
> Simple select with enum value as a parameter. Note that we didn't put
> "::cstring" in query. This is the result of the EXPLAIN SELECT from the
> code:
> +++++++++++++++++++++++++++++
> Append (cost=0.00..109.25 rows=26 width=4)
> -> Seq Scan on test_a (cost=0.00..54.63 rows=13 width=4)
> Filter: (key = ('A'::cstring)::ab)
> -> Seq Scan on test_b (cost=0.00..54.63 rows=13 width=4)
> Filter: (key = ('A'::cstring)::ab)
> +++++++++++++++++++++++++++++
> I have put wireshark snoop in attachment where we can see that our java
> client is not the one adding the "::cstring" part into the code.

It's the Postgres EXPLAIN's code for expression deparsing that puts the
'::cstring' there. I have to wonder why it couldn't just skip adding that
and print it as simply 'A'::ab. However, I'm not sure if answer to that
question is related to why partition pruning doesn't occur. Partition
pruning not occurring may however have to do with the fact that
PreparedStatement is being used meaning the planner doesn't get a chance
to do perform the pruning, but then one would see "Filter: (key = $1)" in
the EXPLAIN output if that's the case. Sorry, I'm out of clues.

By the way, just to reconfirm if your Java application is connecting to
the same server as psql, what plan do you get when you try the same query
via psql connecting to the same server as the Java application?

I get this:

$ psql
Timing is on.
Line style is unicode.
psql (10.2)
Type "help" for help.

create type ab as enum ('A', 'B');
create table test (key ab not null) partition by list (key);
create table test_a partition of test for values in ('A');
create table test_b partition of test for values in ('B');

EXPLAIN SELECT * FROM test WHERE key = 'A'::ab;
QUERY PLAN
──────────────────────────────────────────────────────────────
Append (cost=0.00..83.75 rows=26 width=4)
-> Seq Scan on test_a (cost=0.00..41.88 rows=13 width=4)
Filter: (key = 'A'::ab)
-> Seq Scan on test_b (cost=0.00..41.88 rows=13 width=4)
Filter: (key = 'A'::ab)
(5 rows)

With 10.5, partition for 'B' is pruned.

$ psql
Timing is on.
Line style is unicode.
psql (10.5)
Type "help" for help.

EXPLAIN SELECT * FROM test WHERE key = 'A'::ab;
QUERY PLAN
──────────────────────────────────────────────────────────────
Append (cost=0.00..41.88 rows=13 width=4)
-> Seq Scan on test_a (cost=0.00..41.88 rows=13 width=4)
Filter: (key = 'A'::ab)
(3 rows)

Thanks,
Amit