On 2018/08/17 19:10, Damir Ciganović-Janković wrote:
> 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 :-) )
Ah, there it is. :-)
> 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=# -- 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,
Thanks for that explanation. I was wrong in my previous email in saying
that having the extra cast (::cstring) isn't related to partition pruning
failing. It IS related, as Andrew pointed out in his reply. Having the
"::cstring" in the expression ('MONTH'::cstring::resolution) makes the
expression non-immutable, which means the planner won't use it to perform
pruning. But, as you might be able to see, that's not really a bug of
partition pruning code, it's just how things work.
Andrew also mentioned a workaround in his reply to use setString such that
JDBC doesn't pass 'MONTH' as of type 'text' and instead as of unknown
type. I could't find the documentation of Postgres JDBC's setString to
see how one can do that, but maybe you can try it out.
Thanks,
Amit
