Postgres 13

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?.

CREATE TABLE orders (order_action text, ts timestamp);
   
INSERT INTO orders VALUES
   ('buy',  '2013-10-03 13:03:02'),
   ('buy',  '2013-10-08 13:03:02'),
   ('sell', '2013-10-10 15:58:02'),
   ('buy',  '2013-11-01 09:30:02'),
   ('buy',  '2013-11-01 14:03:02'),
   ('sell', '2013-11-07 10:34:02'),
   ('buy',  '2013-12-03 15:46:02'),
   ('sell', '2013-12-09 16:00:03'),
   ('buy',  '2013-12-11 13:02:02'),
   ('sell', '2013-12-18 15:59:03');
10 rows affected

-- Postgres 9.4 or newer:
SELECT *, count(*) FILTER (WHERE order_action = 'sell') OVER (ORDER BY ts DESC) AS grp
FROM   orders
ORDER  BY ts;


order_action
ts
grp


buy
2013-10-03 13:03:02
4

buy
2013-10-08 13:03:02
4

sell
2013-10-10 15:58:02
4

buy
2013-11-01 09:30:02
3

buy
2013-11-01 14:03:02
3

sell
2013-11-07 10:34:02
3

buy
2013-12-03 15:46:02
2

sell
2013-12-09 16:00:03
2

buy
2013-12-11 13:02:02
1

sell
2013-12-18 15:59:03
1


-- ... or with ascending grp numbers
SELECT *, count(*) FILTER (WHERE order_action = 'sell')
          OVER (ORDER BY ts ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS grp
FROM   orders
ORDER  BY ts;


order_action
ts
grp


buy
2013-10-03 13:03:02
0

buy
2013-10-08 13:03:02
0

sell
2013-10-10 15:58:02
0

buy
2013-11-01 09:30:02
1

buy
2013-11-01 14:03:02
1

sell
2013-11-07 10:34:02
1

buy
2013-12-03 15:46:02
2

sell
2013-12-09 16:00:03
2

buy
2013-12-11 13:02:02
3

sell
2013-12-18 15:59:03
3


-- the same for any Postgres version:
SELECT *, count(order_action = 'sell' OR NULL) OVER (ORDER BY ts DESC) AS grp
FROM   orders
ORDER  BY ts;


order_action
ts
grp


buy
2013-10-03 13:03:02
4

buy
2013-10-08 13:03:02
4

sell
2013-10-10 15:58:02
4

buy
2013-11-01 09:30:02
3

buy
2013-11-01 14:03:02
3

sell
2013-11-07 10:34:02
3

buy
2013-12-03 15:46:02
2

sell
2013-12-09 16:00:03
2

buy
2013-12-11 13:02:02
1

sell
2013-12-18 15:59:03
1


-- ... or with ascending grp numbers
SELECT *, count(order_action = 'sell' OR NULL)
          OVER (ORDER BY ts ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS grp
FROM   orders
ORDER  BY ts;


order_action
ts
grp


buy
2013-10-03 13:03:02
0

buy
2013-10-08 13:03:02
0

sell
2013-10-10 15:58:02
0

buy
2013-11-01 09:30:02
1

buy
2013-11-01 14:03:02
1

sell
2013-11-07 10:34:02
1

buy
2013-12-03 15:46:02
2

sell
2013-12-09 16:00:03
2

buy
2013-12-11 13:02:02
3

sell
2013-12-18 15:59:03
3

order_action	ts	grp
buy	2013-10-03 13:03:02	4
buy	2013-10-08 13:03:02	4
sell	2013-10-10 15:58:02	4
buy	2013-11-01 09:30:02	3
buy	2013-11-01 14:03:02	3
sell	2013-11-07 10:34:02	3
buy	2013-12-03 15:46:02	2
sell	2013-12-09 16:00:03	2
buy	2013-12-11 13:02:02	1
sell	2013-12-18 15:59:03	1