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
|
Список | 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 по дате отправления: