Postgres 11

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 tbl (grp text, the_date date, color text);
INSERT INTO tbl VALUES
  ('A', '1-1-2019', 'R')
, ('A', '1-2-2019', 'Y')
, ('B', '1-1-2019', 'R')
, ('B', '1-2-2019', 'Y')
, ('B', '1-3-2019', 'Y')
, ('B', '1-4-2019', 'R')
, ('B', '1-5-2019', 'R')
, ('B', '1-6-2019', 'R')
, ('B', '1-7-2019', 'Y')  -- added some more
, ('B', '1-8-2019', 'R')
, ('B', '1-9-2019', 'R');
11 rows affected

SELECT grp, the_date, color
     , row_number() OVER (PARTITION BY grp, color, part
                          ORDER BY the_date) AS rank
FROM  (
   SELECT *
        , row_number() OVER (PARTITION BY grp ORDER BY the_date, color)
        - row_number() OVER (PARTITION BY grp, color ORDER BY the_date) AS part
   FROM   tbl
   ) sub
ORDER BY grp, the_date, color;


grp
the_date
color
rank


A
2019-01-01
R
1

A
2019-02-01
Y
1

B
2019-01-01
R
1

B
2019-02-01
Y
1

B
2019-03-01
Y
2

B
2019-04-01
R
1

B
2019-05-01
R
2

B
2019-06-01
R
3

B
2019-07-01
Y
1

B
2019-08-01
R
1

B
2019-09-01
R
2


SELECT grp, the_date, color, CASE WHEN color = 'R' THEN rank END AS rank
FROM  (
   SELECT *
         , row_number() OVER (PARTITION BY grp, color, island ORDER BY the_date) AS rank
   FROM  (
      SELECT *
            , row_number() OVER (PARTITION BY grp ORDER BY the_date, color)
            - row_number() OVER (PARTITION BY grp, color ORDER BY the_date) AS island
      FROM   tbl
      ) sub1
   ) sub2
ORDER  BY grp, the_date, color;


grp
the_date
color
rank


A
2019-01-01
R
1

A
2019-02-01
Y
null

B
2019-01-01
R
1

B
2019-02-01
Y
null

B
2019-03-01
Y
null

B
2019-04-01
R
1

B
2019-05-01
R
2

B
2019-06-01
R
3

B
2019-07-01
Y
null

B
2019-08-01
R
1

B
2019-09-01
R
2


CREATE OR REPLACE FUNCTION rank_color(_color text = 'R')
  RETURNS TABLE (grp text, the_date date, color text, rnk int) AS
$func$
DECLARE
   _last_grp text;
BEGIN
   FOR grp, the_date, color IN
      SELECT t.grp, t.the_date, t.color FROM tbl t ORDER BY 1,2
   LOOP
      IF color = $1 THEN
         IF _last_grp = grp THEN
            rnk := COALESCE(rnk + 1, 1);
         ELSE
            rnk := 1;
         END IF;
      ELSIF rnk > 0 THEN
         rnk := NULL;
      END IF;

      RETURN NEXT;
      _last_grp := grp;
   END LOOP;
END
$func$ LANGUAGE plpgsql;


SELECT * FROM rank_color();


grp
the_date
color
rnk


A
2019-01-01
R
1

A
2019-02-01
Y
null

B
2019-01-01
R
1

B
2019-02-01
Y
null

B
2019-03-01
Y
null

B
2019-04-01
R
1

B
2019-05-01
R
2

B
2019-06-01
R
3

B
2019-07-01
Y
null

B
2019-08-01
R
1

B
2019-09-01
R
2

grp	the_date	color	rank
A	2019-01-01	R	1
A	2019-02-01	Y	1
B	2019-01-01	R	1
B	2019-02-01	Y	1
B	2019-03-01	Y	2
B	2019-04-01	R	1
B	2019-05-01	R	2
B	2019-06-01	R	3
B	2019-07-01	Y	1
B	2019-08-01	R	1
B	2019-09-01	R	2

grp	the_date	color	rank
A	2019-01-01	R	1
A	2019-02-01	Y	null
B	2019-01-01	R	1
B	2019-02-01	Y	null
B	2019-03-01	Y	null
B	2019-04-01	R	1
B	2019-05-01	R	2
B	2019-06-01	R	3
B	2019-07-01	Y	null
B	2019-08-01	R	1
B	2019-09-01	R	2