pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: stepya@ukr.net
Subject: BUG #16089: Index only scan does not happen but expected
Date: Wed, 30 Oct 2019 12:54:31 +0000
Message-ID: <16089-89312196238e2c78@postgresql.org> (raw)

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 )



view thread (6+ messages)  latest in thread

Message-ID: <16089-89312196238e2c78@postgresql.org>
Permalink:  ../16089-89312196238e2c78@postgresql.org/
Also on:    postgresql.org/message-id/16089-89312196238e2c78@postgresql.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-bugs@postgresql.org
  Cc: noreply@postgresql.org, stepya@ukr.net
  Subject: Re: BUG #16089: Index only scan does not happen but expected
  In-Reply-To: <16089-89312196238e2c78@postgresql.org>
  References: <16089-89312196238e2c78@postgresql.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox