Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

select @@version;

ERROR:  column "version" does not exist
LINE 1: select @@version;
                 ^


CREATE TABLE deals_table(
   company_id        INTEGER  NOT NULL 
  ,deal_id           INTEGER  NOT NULL
  ,deal_date         DATE  NOT NULL
  ,deal_date_5y_back DATE  NOT NULL
);
INSERT INTO deals_table(company_id,deal_id,deal_date,deal_date_5y_back) VALUES (86,1,'2006-02-27','2001-02-27');
INSERT INTO deals_table(company_id,deal_id,deal_date,deal_date_5y_back) VALUES (86,2,'2012-04-01','2007-04-01');
INSERT INTO deals_table(company_id,deal_id,deal_date,deal_date_5y_back) VALUES (86,3,'2015-03-23','2010-03-23');
INSERT INTO deals_table(company_id,deal_id,deal_date,deal_date_5y_back) VALUES (86,4,'2018-09-19','2013-09-19');

CREATE TABLE



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1


select d.*,
    count(*) over(
        partition by company_id
        order by deal_date
        range between interval '5' year preceding and current row
    ) - 1 num_deals_5y_back
from deals_table d


company_id
deal_id
deal_date
deal_date_5y_back
num_deals_5y_back


86
1
2006-02-27
2001-02-27
0

86
2
2012-04-01
2007-04-01
0

86
3
2015-03-23
2010-03-23
1

86
4
2018-09-19
2013-09-19
1



SELECT 4

company_id	deal_id	deal_date	deal_date_5y_back	num_deals_5y_back
86	1	2006-02-27	2001-02-27	0
86	2	2012-04-01	2007-04-01	0
86	3	2015-03-23	2010-03-23	1
86	4	2018-09-19	2013-09-19	1