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 )
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 |