BUG #16089: Index only scan does not happen but expected

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: stepya(at)ukr(dot)net
Subject: BUG #16089: Index only scan does not happen but expected
Date: 2019-10-30 12:54:31
Message-ID: 16089-89312196238e2c78@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: 16089
Logged by: Stepan Yankevych
Email address: stepya(at)ukr(dot)net
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 )

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2019-10-30 14:05:34 Re: insert into inet from text automatically adding subnet
Previous Message Jeremy Finzel 2019-10-30 11:36:00 insert into inet from text automatically adding subnet