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 t1 (
type text NOT NULL
, value int NOT NULL
);
INSERT INTO t1
SELECT 'A', generate_series(1, 20)
UNION ALL
SELECT 'B', generate_series(1, 100)
UNION ALL
SELECT 'C', generate_series(1, 5)
;
125 rows affected
WITH pattern AS (
SELECT *, row_number() OVER (PARTITION BY type ORDER BY ord) AS pos
FROM unnest('{A,B,B,C,B,B}'::text[]) WITH ORDINALITY t(type, ord) -- provide pattern here
)
, type_frequency AS (
SELECT type, count(*) AS freq
FROM pattern
GROUP BY 1
)
SELECT type, t.value, t.epoch, p.ord, pos
FROM (
SELECT type, value
, ceil(rn / freq::float) AS epoch
, (rn - 1) % freq + 1 AS pos
FROM (
SELECT *, row_number() OVER (PARTITION BY type ORDER BY value) AS rn
FROM t1
JOIN type_frequency tf USING (type)
) sub
) t
JOIN pattern p USING (type, pos)
ORDER BY t.epoch, p.ord;
| type | value | epoch | ord | pos |
|---|---|---|---|---|
| A | 1 | 1 | 1 | 1 |
| B | 1 | 1 | 2 | 1 |
| B | 2 | 1 | 3 | 2 |
| C | 1 | 1 | 4 | 1 |
| B | 3 | 1 | 5 | 3 |
| B | 4 | 1 | 6 | 4 |
| A | 2 | 2 | 1 | 1 |
| B | 5 | 2 | 2 | 1 |
| B | 6 | 2 | 3 | 2 |
| C | 2 | 2 | 4 | 1 |
| B | 7 | 2 | 5 | 3 |
| B | 8 | 2 | 6 | 4 |
| A | 3 | 3 | 1 | 1 |
| B | 9 | 3 | 2 | 1 |
| B | 10 | 3 | 3 | 2 |
| C | 3 | 3 | 4 | 1 |
| B | 11 | 3 | 5 | 3 |
| B | 12 | 3 | 6 | 4 |
| A | 4 | 4 | 1 | 1 |
| B | 13 | 4 | 2 | 1 |
| B | 14 | 4 | 3 | 2 |
| C | 4 | 4 | 4 | 1 |
| B | 15 | 4 | 5 | 3 |
| B | 16 | 4 | 6 | 4 |
| A | 5 | 5 | 1 | 1 |
| B | 17 | 5 | 2 | 1 |
| B | 18 | 5 | 3 | 2 |
| C | 5 | 5 | 4 | 1 |
| B | 19 | 5 | 5 | 3 |
| B | 20 | 5 | 6 | 4 |
| A | 6 | 6 | 1 | 1 |
| B | 21 | 6 | 2 | 1 |
| B | 22 | 6 | 3 | 2 |
| B | 23 | 6 | 5 | 3 |
| B | 24 | 6 | 6 | 4 |
| A | 7 | 7 | 1 | 1 |
| B | 25 | 7 | 2 | 1 |
| B | 26 | 7 | 3 | 2 |
| B | 27 | 7 | 5 | 3 |
| B | 28 | 7 | 6 | 4 |
| A | 8 | 8 | 1 | 1 |
| B | 29 | 8 | 2 | 1 |
| B | 30 | 8 | 3 | 2 |
| B | 31 | 8 | 5 | 3 |
| B | 32 | 8 | 6 | 4 |
| A | 9 | 9 | 1 | 1 |
| B | 33 | 9 | 2 | 1 |
| B | 34 | 9 | 3 | 2 |
| B | 35 | 9 | 5 | 3 |
| B | 36 | 9 | 6 | 4 |
| A | 10 | 10 | 1 | 1 |
| B | 37 | 10 | 2 | 1 |
| B | 38 | 10 | 3 | 2 |
| B | 39 | 10 | 5 | 3 |
| B | 40 | 10 | 6 | 4 |
| A | 11 | 11 | 1 | 1 |
| B | 41 | 11 | 2 | 1 |
| B | 42 | 11 | 3 | 2 |
| B | 43 | 11 | 5 | 3 |
| B | 44 | 11 | 6 | 4 |
| A | 12 | 12 | 1 | 1 |
| B | 45 | 12 | 2 | 1 |
| B | 46 | 12 | 3 | 2 |
| B | 47 | 12 | 5 | 3 |
| B | 48 | 12 | 6 | 4 |
| A | 13 | 13 | 1 | 1 |
| B | 49 | 13 | 2 | 1 |
| B | 50 | 13 | 3 | 2 |
| B | 51 | 13 | 5 | 3 |
| B | 52 | 13 | 6 | 4 |
| A | 14 | 14 | 1 | 1 |
| B | 53 | 14 | 2 | 1 |
| B | 54 | 14 | 3 | 2 |
| B | 55 | 14 | 5 | 3 |
| B | 56 | 14 | 6 | 4 |
| A | 15 | 15 | 1 | 1 |
| B | 57 | 15 | 2 | 1 |
| B | 58 | 15 | 3 | 2 |
| B | 59 | 15 | 5 | 3 |
| B | 60 | 15 | 6 | 4 |
| A | 16 | 16 | 1 | 1 |
| B | 61 | 16 | 2 | 1 |
| B | 62 | 16 | 3 | 2 |
| B | 63 | 16 | 5 | 3 |
| B | 64 | 16 | 6 | 4 |
| A | 17 | 17 | 1 | 1 |
| B | 65 | 17 | 2 | 1 |
| B | 66 | 17 | 3 | 2 |
| B | 67 | 17 | 5 | 3 |
| B | 68 | 17 | 6 | 4 |
| A | 18 | 18 | 1 | 1 |
| B | 69 | 18 | 2 | 1 |
| B | 70 | 18 | 3 | 2 |
| B | 71 | 18 | 5 | 3 |
| B | 72 | 18 | 6 | 4 |
| A | 19 | 19 | 1 | 1 |
| B | 73 | 19 | 2 | 1 |
| B | 74 | 19 | 3 | 2 |
| B | 75 | 19 | 5 | 3 |
| B | 76 | 19 | 6 | 4 |
| A | 20 | 20 | 1 | 1 |
| B | 77 | 20 | 2 | 1 |
| B | 78 | 20 | 3 | 2 |
| B | 79 | 20 | 5 | 3 |
| B | 80 | 20 | 6 | 4 |
| B | 81 | 21 | 2 | 1 |
| B | 82 | 21 | 3 | 2 |
| B | 83 | 21 | 5 | 3 |
| B | 84 | 21 | 6 | 4 |
| B | 85 | 22 | 2 | 1 |
| B | 86 | 22 | 3 | 2 |
| B | 87 | 22 | 5 | 3 |
| B | 88 | 22 | 6 | 4 |
| B | 89 | 23 | 2 | 1 |
| B | 90 | 23 | 3 | 2 |
| B | 91 | 23 | 5 | 3 |
| B | 92 | 23 | 6 | 4 |
| B | 93 | 24 | 2 | 1 |
| B | 94 | 24 | 3 | 2 |
| B | 95 | 24 | 5 | 3 |
| B | 96 | 24 | 6 | 4 |
| B | 97 | 25 | 2 | 1 |
| B | 98 | 25 | 3 | 2 |
| B | 99 | 25 | 5 | 3 |
| B | 100 | 25 | 6 | 4 |
-- arbitrary pattern
WITH pattern AS (
SELECT *, row_number() OVER (PARTITION BY type ORDER BY ord) AS pos
FROM unnest('{B,A,C,B,B,B,A}'::text[]) WITH ORDINALITY t(type, ord) -- provide pattern here
)
, type_frequency AS (
SELECT type, count(*) AS freq
FROM pattern
GROUP BY 1
)
SELECT type, t.value, t.epoch, p.ord, pos
FROM (
SELECT type, value
, ceil(rn / freq::float) AS epoch
, (rn - 1) % freq + 1 AS pos
FROM (
SELECT *, row_number() OVER (PARTITION BY type ORDER BY value) AS rn
FROM t1
JOIN type_frequency tf USING (type)
) sub
) t
JOIN pattern p USING (type, pos)
ORDER BY t.epoch, p.ord;
| type | value | epoch | ord | pos |
|---|---|---|---|---|
| B | 1 | 1 | 1 | 1 |
| A | 1 | 1 | 2 | 1 |
| C | 1 | 1 | 3 | 1 |
| B | 2 | 1 | 4 | 2 |
| B | 3 | 1 | 5 | 3 |
| B | 4 | 1 | 6 | 4 |
| A | 2 | 1 | 7 | 2 |
| B | 5 | 2 | 1 | 1 |
| A | 3 | 2 | 2 | 1 |
| C | 2 | 2 | 3 | 1 |
| B | 6 | 2 | 4 | 2 |
| B | 7 | 2 | 5 | 3 |
| B | 8 | 2 | 6 | 4 |
| A | 4 | 2 | 7 | 2 |
| B | 9 | 3 | 1 | 1 |
| A | 5 | 3 | 2 | 1 |
| C | 3 | 3 | 3 | 1 |
| B | 10 | 3 | 4 | 2 |
| B | 11 | 3 | 5 | 3 |
| B | 12 | 3 | 6 | 4 |
| A | 6 | 3 | 7 | 2 |
| B | 13 | 4 | 1 | 1 |
| A | 7 | 4 | 2 | 1 |
| C | 4 | 4 | 3 | 1 |
| B | 14 | 4 | 4 | 2 |
| B | 15 | 4 | 5 | 3 |
| B | 16 | 4 | 6 | 4 |
| A | 8 | 4 | 7 | 2 |
| B | 17 | 5 | 1 | 1 |
| A | 9 | 5 | 2 | 1 |
| C | 5 | 5 | 3 | 1 |
| B | 18 | 5 | 4 | 2 |
| B | 19 | 5 | 5 | 3 |
| B | 20 | 5 | 6 | 4 |
| A | 10 | 5 | 7 | 2 |
| B | 21 | 6 | 1 | 1 |
| A | 11 | 6 | 2 | 1 |
| B | 22 | 6 | 4 | 2 |
| B | 23 | 6 | 5 | 3 |
| B | 24 | 6 | 6 | 4 |
| A | 12 | 6 | 7 | 2 |
| B | 25 | 7 | 1 | 1 |
| A | 13 | 7 | 2 | 1 |
| B | 26 | 7 | 4 | 2 |
| B | 27 | 7 | 5 | 3 |
| B | 28 | 7 | 6 | 4 |
| A | 14 | 7 | 7 | 2 |
| B | 29 | 8 | 1 | 1 |
| A | 15 | 8 | 2 | 1 |
| B | 30 | 8 | 4 | 2 |
| B | 31 | 8 | 5 | 3 |
| B | 32 | 8 | 6 | 4 |
| A | 16 | 8 | 7 | 2 |
| B | 33 | 9 | 1 | 1 |
| A | 17 | 9 | 2 | 1 |
| B | 34 | 9 | 4 | 2 |
| B | 35 | 9 | 5 | 3 |
| B | 36 | 9 | 6 | 4 |
| A | 18 | 9 | 7 | 2 |
| B | 37 | 10 | 1 | 1 |
| A | 19 | 10 | 2 | 1 |
| B | 38 | 10 | 4 | 2 |
| B | 39 | 10 | 5 | 3 |
| B | 40 | 10 | 6 | 4 |
| A | 20 | 10 | 7 | 2 |
| B | 41 | 11 | 1 | 1 |
| B | 42 | 11 | 4 | 2 |
| B | 43 | 11 | 5 | 3 |
| B | 44 | 11 | 6 | 4 |
| B | 45 | 12 | 1 | 1 |
| B | 46 | 12 | 4 | 2 |
| B | 47 | 12 | 5 | 3 |
| B | 48 | 12 | 6 | 4 |
| B | 49 | 13 | 1 | 1 |
| B | 50 | 13 | 4 | 2 |
| B | 51 | 13 | 5 | 3 |
| B | 52 | 13 | 6 | 4 |
| B | 53 | 14 | 1 | 1 |
| B | 54 | 14 | 4 | 2 |
| B | 55 | 14 | 5 | 3 |
| B | 56 | 14 | 6 | 4 |
| B | 57 | 15 | 1 | 1 |
| B | 58 | 15 | 4 | 2 |
| B | 59 | 15 | 5 | 3 |
| B | 60 | 15 | 6 | 4 |
| B | 61 | 16 | 1 | 1 |
| B | 62 | 16 | 4 | 2 |
| B | 63 | 16 | 5 | 3 |
| B | 64 | 16 | 6 | 4 |
| B | 65 | 17 | 1 | 1 |
| B | 66 | 17 | 4 | 2 |
| B | 67 | 17 | 5 | 3 |
| B | 68 | 17 | 6 | 4 |
| B | 69 | 18 | 1 | 1 |
| B | 70 | 18 | 4 | 2 |
| B | 71 | 18 | 5 | 3 |
| B | 72 | 18 | 6 | 4 |
| B | 73 | 19 | 1 | 1 |
| B | 74 | 19 | 4 | 2 |
| B | 75 | 19 | 5 | 3 |
| B | 76 | 19 | 6 | 4 |
| B | 77 | 20 | 1 | 1 |
| B | 78 | 20 | 4 | 2 |
| B | 79 | 20 | 5 | 3 |
| B | 80 | 20 | 6 | 4 |
| B | 81 | 21 | 1 | 1 |
| B | 82 | 21 | 4 | 2 |
| B | 83 | 21 | 5 | 3 |
| B | 84 | 21 | 6 | 4 |
| B | 85 | 22 | 1 | 1 |
| B | 86 | 22 | 4 | 2 |
| B | 87 | 22 | 5 | 3 |
| B | 88 | 22 | 6 | 4 |
| B | 89 | 23 | 1 | 1 |
| B | 90 | 23 | 4 | 2 |
| B | 91 | 23 | 5 | 3 |
| B | 92 | 23 | 6 | 4 |
| B | 93 | 24 | 1 | 1 |
| B | 94 | 24 | 4 | 2 |
| B | 95 | 24 | 5 | 3 |
| B | 96 | 24 | 6 | 4 |
| B | 97 | 25 | 1 | 1 |
| B | 98 | 25 | 4 | 2 |
| B | 99 | 25 | 5 | 3 |
| B | 100 | 25 | 6 | 4 |