Re: BUG #15334: Partition elimination not working as expected whenusing enum as partition key
| От | Damir Ciganović-Janković |
|---|---|
| Тема | Re: BUG #15334: Partition elimination not working as expected whenusing enum as partition key |
| Дата | |
| Msg-id | CA+-2sJ5m0QHQcBDXiNduUmHDv-fkzAR61LJg2W+_dUo9bUortw@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: BUG #15334: Partition elimination not working as expected whenusing enum as partition key (Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>) |
| Ответы |
Re: BUG #15334: Partition elimination not working as expected when using enum as partition key
(Andrew Gierth <andrew@tao11.riddles.org.uk>)
Re: BUG #15334: Partition elimination not working as expected whenusing enum as partition key (Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>) |
| Список | pgsql-bugs |
> 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
В списке pgsql-bugs по дате отправления:
Следующее
От: Andrew GierthДата:
Сообщение: Re: BUG #15336: Wrong cursor's bacward fetch results in select with ALL(subquery)