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 (
id int
, name text
, price numeric NOT NULL CHECK (price > 0) -- !
);
INSERT INTO tbl VALUES
(1, 'apple' , 12)
, (2, 'banana' , 6)
, (3, 'orange' , 18)
, (4, 'pineapple', 26)
, (4, 'lemon' , 30)
;
5 rows affected
WITH bounds AS (
SELECT *
FROM (
SELECT bound AS lo, lead(bound) OVER (ORDER BY bound) AS hi
FROM (
SELECT generate_series(0, x, x/3) AS bound -- desired number of partitions
FROM (SELECT max(price) AS x FROM tbl) x
) sub1
) sub2
WHERE hi IS NOT NULL
)
SELECT b.hi, count(t.price)
FROM bounds b
LEFT JOIN tbl t ON t.price > b.lo AND t.price <= b.hi
GROUP BY 1
ORDER BY 1;
| hi | count |
|---|---|
| 10.0000000000000000 | 1 |
| 20.0000000000000000 | 2 |
| 30.0000000000000000 | 2 |
-- the same for 5 partitions
WITH bounds AS (
SELECT *
FROM (
SELECT bound AS lo, lead(bound) OVER (ORDER BY bound) AS hi
FROM (
SELECT generate_series(0, x, x/5) AS bound -- desired number of partitions
FROM (SELECT max(price) AS x FROM tbl) x
) sub1
) sub2
WHERE hi IS NOT NULL
)
SELECT b.hi, count(t.price)
FROM bounds b
LEFT JOIN tbl t ON t.price > b.lo AND t.price <= b.hi
GROUP BY 1
ORDER BY 1;
| hi | count |
|---|---|
| 6.0000000000000000 | 1 |
| 12.0000000000000000 | 1 |
| 18.0000000000000000 | 1 |
| 24.0000000000000000 | 0 |
| 30.0000000000000000 | 2 |