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

From: Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>
To: Damir Ciganović-Janković <damir(dot)ciganovic(dot)jankovic(at)gmail(dot)com>
Cc: pgsql-bugs(at)lists(dot)postgresql(dot)org, noreply(at)postgresql(dot)org
Subject: Re: BUG #15334: Partition elimination not working as expected when using enum as partition key
Date: 2018-08-20 01:00:46
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-bugs

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
> :-)
> (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;
> example=# explain analyze execute typed_stmt('A');
> --------------------------------------------------------------------------------------------------------
> 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.


In response to


Browse pgsql-bugs by date

  From Date Subject
Next Message Jeff Frost 2018-08-20 01:48:14 Re: BUG #15299: relation does not exist errors
Previous Message Michael Paquier 2018-08-19 22:11:57 Re: BUG #15333: pg_dump error on large table -- "pg_dump: could not stat file...Unknown error"