From: | PG Bug reporting form <noreply(at)postgresql(dot)org> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Cc: | digoal(at)126(dot)com |
Subject: | BUG #15001: planner cann't distinguish composite index? |
Date: | 2018-01-09 11:25:16 |
Message-ID: | 20180109112516.21776.93511@wrigleys.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 15001
Logged by: Zhou Digoal
Email address: digoal(at)126(dot)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)
```
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Haas | 2018-01-09 15:23:12 | Re: BUG #14941: Vacuum crashes |
Previous Message | Michael Paquier | 2018-01-09 06:31:04 | Re: BUG #14983: ERROR: duplicate key value violates unique constraint "oid_tbl_oid_key" |