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

> 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)

I'm sorry, I didn't included this part for 10.2:
alter table test_a add constraint test_a_check check(key='A');
alter table test_b add constraint test_b_check check(key='B');

Same workaround as I did in #15042
https://www.postgresql.org/message-id/2b20c220-50cb-3fae-da57-92e8cb3675dd%40lab.ntt.co.jp
:-)
(we will update soon :-) )

The results were same. But we managed to find out how to reproduced this in
psql.
This is the output from version 10.5 to eliminate possible fixes in release
10.3-10.5

+++++++++++++++++++++++
+++++++++++++++++++++++
+++++++++++++++++++++++
example=# select version();
version
------------------------------------------------------------
PostgreSQL 10.5, compiled by Visual C++ build 1800, 64-bit
(1 row)

example=# -- Prepare schema.
example=# create type ab as enum ('A', 'B');
CREATE TYPE
example=# create table test (key ab not null) partition by list (key);
CREATE TABLE
example=# create table test_a partition of test for values in ('A');
CREATE TABLE
example=# create table test_b partition of test for values in ('B');
CREATE TABLE
example=# -- Insert data.
example=# insert into test (key) values ('A'), ('B');
INSERT 0 2
example=# -- Inline value into query.
example=# explain analyze select * from test where key = 'A'::ab;
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Append (cost=0.00..41.88 rows=13 width=4) (actual time=0.013..0.013
rows=1 loops=1)
-> Seq Scan on test_a (cost=0.00..41.88 rows=13 width=4) (actual
time=0.012..0.013 rows=1 loops=1)
Filter: (key = 'A'::ab)
Planning time: 0.153 ms
Execution time: 0.025 ms
(5 rows)

example=# -- Use untyped prepared statment.
example=# prepare untyped_stmt as select * from test where key = $1::ab;
PREPARE
example=# explain analyze execute untyped_stmt('A');
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Append (cost=0.00..41.88 rows=13 width=4) (actual time=0.011..0.012
rows=1 loops=1)
-> Seq Scan on test_a (cost=0.00..41.88 rows=13 width=4) (actual
time=0.011..0.011 rows=1 loops=1)
Filter: (key = 'A'::ab)
Planning time: 0.201 ms
Execution time: 0.024 ms
(5 rows)

example=# -- Use typed prepared statment.
example=# prepare typed_stmt(text) as select * from test where key = $1::ab;
PREPARE
example=# explain analyze execute typed_stmt('A');
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Append (cost=0.00..109.25 rows=26 width=4) (actual time=0.015..0.022
rows=1 loops=1)
-> Seq Scan on test_a (cost=0.00..54.63 rows=13 width=4) (actual
time=0.015..0.015 rows=1 loops=1)
Filter: (key = ('A'::cstring)::ab)
-> Seq Scan on test_b (cost=0.00..54.63 rows=13 width=4) (actual
time=0.006..0.006 rows=0 loops=1)
Filter: (key = ('A'::cstring)::ab)
Rows Removed by Filter: 1
Planning time: 0.155 ms
Execution time: 0.042 ms
(8 rows)
+++++++++++++++++++++++
+++++++++++++++++++++++
+++++++++++++++++++++++

When we prepared statement with parameter not defined, everything seems
fine,
but when we prepared statement with type of parameter defined it happened
again.
We can also see from the plan that rows were actually filtered while
executing the second statement.

Hope this will be easier to track it down now,
Damir