PostgreSQL Bugs

Collected from the PG bugs email list.

Bug ID16089
PG Version11.5
OSCentOS Linux release 7.3.1611 (Core)
Opened2019-10-30 12:54:31+00
Reported byStepan Yankevych
StatusNew

Body of first available message related to this bug follows.

The following bug has been logged on the website:

Bug reference:      16089
Logged by:          Stepan Yankevych
Email address:      (redacted)
PostgreSQL version: 11.5
Operating system:   CentOS Linux release 7.3.1611 (Core)
Description:        

Not a real issue but rather performance leak.
The issue is reproducible on the version 11.5 and 12.0 as well. 
See script of partitioned table. Please notice PK and index contains
start_date_id as second field.
 
CREATE TABLE if not exists dwh.l1_snapshot_tail2 (
	l1_snapshot_id int8 NOT NULL,
	start_date_id int4 NOT NULL,
	transaction_id int8 NOT NULL,
	exchange_id varchar(6) NULL,
	instrument_id int4 NULL,
	bid_price numeric(12,4) NULL,
	ask_price numeric(12,4) NULL,
	bid_quantity int8 NULL,
	ask_quantity int8 NULL,
	dataset_id int4 NULL,
	transaction_time timestamp NULL,
	CONSTRAINT pk_l1_snapshot PRIMARY KEY (l1_snapshot_id, start_date_id)
)
PARTITION BY RANGE (start_date_id);
CREATE INDEX if not exists l1_snapshot_transact_date_idx ON 
dwh.l1_snapshot_tail2 USING btree (transaction_id, start_date_id);


CREATE TABLE partitions.l1_snapshot_201805 PARTITION OF
dwh.l1_snapshot_tail2 FOR VALUES FROM (20180501) TO (20180601);
CREATE TABLE partitions.l1_snapshot_201806 PARTITION OF
dwh.l1_snapshot_tail2 FOR VALUES FROM (20180601) TO (20180701);
CREATE TABLE partitions.l1_snapshot_201807 PARTITION OF
dwh.l1_snapshot_tail2 FOR VALUES FROM (20180701) TO (20180801);
CREATE TABLE partitions.l1_snapshot_201808 PARTITION OF
dwh.l1_snapshot_tail2 FOR VALUES FROM (20180801) TO (20180901);
CREATE TABLE partitions.l1_snapshot_201809 PARTITION OF
dwh.l1_snapshot_tail2 FOR VALUES FROM (20180901) TO (20181001);
CREATE TABLE partitions.l1_snapshot_201810 PARTITION OF
dwh.l1_snapshot_tail2 FOR VALUES FROM (20181001) TO (20181101);
CREATE TABLE partitions.l1_snapshot_201811 PARTITION OF
dwh.l1_snapshot_tail2 FOR VALUES FROM (20181101) TO (20181201);
CREATE TABLE partitions.l1_snapshot_201812 PARTITION OF
dwh.l1_snapshot_tail2 FOR VALUES FROM (20181201) TO (20190101);

i have loaded 500000 rows with date_id = 20181112
Real table has 20-40 millions records a day.

Examine query 
select start_date_id, count(start_date_id) as cn
from dwh.l1_snapshot_tail2
where start_date_id between 20181112 and 20181112
group by start_date_id

The execution plan shows reading full partitions.l1_snapshot_201811
Why do we need to read data from table. 
We have all needed information in the index that is smaller. 
I would expect index only scan (something like Oracle version of index fast
full scan )

Messages

DateAuthorSubject
2019-10-30 12:54:31+00PG Bug reporting formBUG #16089: Index only scan does not happen but expected
2019-10-30 14:13:16+00Dmitry DolgovRe: BUG #16089: Index only scan does not happen but expected
2019-10-30 14:18:09+00Stepan YankevychRE: BUG #16089: Index only scan does not happen but expected
2019-10-30 15:10:06+00Dmitry DolgovRe: BUG #16089: Index only scan does not happen but expected
2019-10-30 15:14:39+00Dmitry DolgovRe: BUG #16089: Index only scan does not happen but expected
2019-10-30 15:27:42+00Stepan YankevychRE: BUG #16089: Index only scan does not happen but expected