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?.
| version |
|---|
| PostgreSQL 12.13 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-15), 64-bit |
SELECT 1
CREATE TABLE
INSERT 0 9
| bp_date | bp_count | coin_price | bp_total |
|---|---|---|---|
| 2020-01-05 10:00:00 | 10 | 500 | 5000 |
| 2020-01-25 10:00:00 | 20 | 1000 | 20000 |
| 2020-03-05 10:00:00 | 10 | 1500 | 15000 |
| 2020-03-05 10:00:00 | 10 | 2000 | 20000 |
| 2020-05-17 10:00:00 | 10 | 2500 | 25000 |
| 2020-05-19 10:00:00 | 10 | 3000 | 30000 |
| 2020-07-23 10:00:00 | 10 | 500.45 | 5004.5 |
| 2020-07-27 10:00:00 | 10 | 500.45 | 5004.5 |
| 2020-09-30 10:00:00 | 10 | 500.45 | 5004.5 |
SELECT 9
SELECT 12
| dc |
|---|
| 2020-01-01 |
| 2020-02-01 |
| 2020-03-01 |
| 2020-04-01 |
| 2020-05-01 |
| 2020-06-01 |
| 2020-07-01 |
| 2020-08-01 |
| 2020-09-01 |
| 2020-10-01 |
| 2020-11-01 |
| 2020-12-01 |
SELECT 12
| dc | date_trunc | bp_date | bp_count | coin_price | bp_total | date_trunc |
|---|---|---|---|---|---|---|
| 2020-01-01 | 2020-01-01 00:00:00+00 | 2020-01-05 10:00:00 | 10 | 500 | 5000 | 2020-01-01 00:00:00 |
| 2020-01-01 | 2020-01-01 00:00:00+00 | 2020-01-25 10:00:00 | 20 | 1000 | 20000 | 2020-01-01 00:00:00 |
| 2020-02-01 | 2020-02-01 00:00:00+00 | null | null | null | null | null |
| 2020-03-01 | 2020-03-01 00:00:00+00 | 2020-03-05 10:00:00 | 10 | 1500 | 15000 | 2020-03-01 00:00:00 |
| 2020-03-01 | 2020-03-01 00:00:00+00 | 2020-03-05 10:00:00 | 10 | 2000 | 20000 | 2020-03-01 00:00:00 |
| 2020-04-01 | 2020-04-01 00:00:00+01 | null | null | null | null | null |
| 2020-05-01 | 2020-05-01 00:00:00+01 | 2020-05-17 10:00:00 | 10 | 2500 | 25000 | 2020-05-01 00:00:00 |
| 2020-05-01 | 2020-05-01 00:00:00+01 | 2020-05-19 10:00:00 | 10 | 3000 | 30000 | 2020-05-01 00:00:00 |
| 2020-06-01 | 2020-06-01 00:00:00+01 | null | null | null | null | null |
| 2020-07-01 | 2020-07-01 00:00:00+01 | 2020-07-23 10:00:00 | 10 | 500.45 | 5004.5 | 2020-07-01 00:00:00 |
| 2020-07-01 | 2020-07-01 00:00:00+01 | 2020-07-27 10:00:00 | 10 | 500.45 | 5004.5 | 2020-07-01 00:00:00 |
| 2020-08-01 | 2020-08-01 00:00:00+01 | null | null | null | null | null |
| 2020-09-01 | 2020-09-01 00:00:00+01 | 2020-09-30 10:00:00 | 10 | 500.45 | 5004.5 | 2020-09-01 00:00:00 |
| 2020-10-01 | 2020-10-01 00:00:00+01 | null | null | null | null | null |
| 2020-11-01 | 2020-11-01 00:00:00+00 | null | null | null | null | null |
| 2020-12-01 | 2020-12-01 00:00:00+00 | null | null | null | null | null |
SELECT 16
| Month: | Amount/mth | No. of coins | Avg price/mth |
|---|---|---|---|
| 1 | 25000 | 30 | 833.33 |
| 2 | 0 | 0 | 0 |
| 3 | 35000 | 20 | 1750.00 |
| 4 | 0 | 0 | 0 |
| 5 | 55000 | 20 | 2750.00 |
| 6 | 0 | 0 | 0 |
| 7 | 10009 | 20 | 500.45 |
| 8 | 0 | 0 | 0 |
| 9 | 5004.5 | 10 | 500.45 |
| 10 | 0 | 0 | 0 |
| 11 | 0 | 0 | 0 |
| 12 | 0 | 0 | 0 |
SELECT 12