BUG #15001: planner cann't distinguish composite index?
От | PG Bug reporting form |
---|---|
Тема | BUG #15001: planner cann't distinguish composite index? |
Дата | |
Msg-id | 20180109112516.21776.93511@wrigleys.postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #15001: planner cann't distinguish composite index?
|
Список | pgsql-bugs |
The following bug has been logged on the website: Bug reference: 15001 Logged by: Zhou Digoal Email address: digoal@126.com PostgreSQL version: 10.1 Operating system: centos 7.x x64 Description: ``` postgres=# create table tbl(c1 int, c2 int, c3 int); CREATE TABLE postgres=# create index idx_tbl on tbl (c1,c2); CREATE INDEX postgres=# insert into tbl select random()*100, random()*10 from generate_series(1,10000000); INSERT 0 10000000 postgres=# explain select c1,c2 , count(*) from tbl group by c2,c1; QUERY PLAN -------------------------------------------------------------------------------------------------------------------- Finalize GroupAggregate (cost=176259.41..176337.18 rows=1111 width=16) Group Key: c2, c1 -> Sort (cost=176259.41..176276.08 rows=6666 width=16) Sort Key: c2, c1 -> Gather (cost=175158.32..175836.03 rows=6666 width=16) Workers Planned: 6 -> Partial HashAggregate (cost=174158.32..174169.43 rows=1111 width=16) Group Key: c2, c1 -> Parallel Index Only Scan using idx_tbl on tbl (cost=0.43..161658.26 rows=1666675 width=8) (9 rows) postgres=# explain select c1,c2 , count(*) from tbl group by c1,c2; QUERY PLAN -------------------------------------------------------------------------------------------------------------- Finalize GroupAggregate (cost=1000.53..176040.80 rows=1111 width=16) Group Key: c1, c2 -> Gather Merge (cost=1000.53..175979.69 rows=6666 width=16) Workers Planned: 6 -> Partial GroupAggregate (cost=0.43..174169.43 rows=1111 width=16) Group Key: c1, c2 -> Parallel Index Only Scan using idx_tbl on tbl (cost=0.43..161658.26 rows=1666675 width=8) (7 rows) ``` i need to set enable_sort=off, so planner can choose the same planner with c1,c2 and c2,c1 group by. ``` postgres=# set enable_sort=off; SET postgres=# explain select c1,c2 , count(*) from tbl group by c2,c1; QUERY PLAN -------------------------------------------------------------------------------------------------------------- Finalize HashAggregate (cost=175886.03..175897.14 rows=1111 width=16) Group Key: c2, c1 -> Gather (cost=175158.32..175836.03 rows=6666 width=16) Workers Planned: 6 -> Partial HashAggregate (cost=174158.32..174169.43 rows=1111 width=16) Group Key: c2, c1 -> Parallel Index Only Scan using idx_tbl on tbl (cost=0.43..161658.26 rows=1666675 width=8) (7 rows) ```
В списке pgsql-bugs по дате отправления: