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

Поиск
Список
Период
Сортировка
От PG Bug reporting form
Тема BUG #16089: Index only scan does not happen but expected
Дата
Msg-id 16089-89312196238e2c78@postgresql.org
обсуждение исходный текст
Ответы Re: BUG #16089: Index only scan does not happen but expected  (Dmitry Dolgov <9erthalion6@gmail.com>)
Список pgsql-bugs
The following bug has been logged on the website:

Bug reference:      16089
Logged by:          Stepan Yankevych
Email address:      stepya@ukr.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 )


В списке pgsql-bugs по дате отправления:

Предыдущее
От: Jeremy Finzel
Дата:
Сообщение: insert into inet from text automatically adding subnet
Следующее
От: Tom Lane
Дата:
Сообщение: Re: insert into inet from text automatically adding subnet