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
CREATE TABLE
CREATE TABLE
CREATE TABLE
| category_id | category_name |
|---|---|
| 1 | single room |
| 2 | double room |
INSERT 0 2
| category_price_id | room_category_id | valid_period | base_price |
|---|---|---|---|
| 1 | 1 | [2023-01-01,2023-02-01) | 80.00 |
| 2 | 1 | [2023-02-01,2023-03-01) | 85.00 |
| 3 | 1 | [2023-03-01,2023-04-01) | 88.00 |
| 4 | 2 | [2023-01-01,2023-02-01) | 100.00 |
| 5 | 2 | [2023-02-01,2023-03-01) | 105.00 |
| 6 | 2 | [2023-03-01,2023-04-01) | 108.00 |
INSERT 0 6
| adjustment_id | room_category_id | valid_period | price_adjustment |
|---|---|---|---|
| 1 | 1 | [2023-01-15,2023-02-15) | 11.00 |
| 2 | 1 | [2023-01-10,2023-01-21) | 7.00 |
| 3 | 1 | [2023-01-28,2023-02-15) | 8.00 |
| 4 | 1 | [2023-01-17,2023-02-04) | 13.00 |
INSERT 0 4
| booking_id | guest_name | room_category_id | booking_period |
|---|---|---|---|
| 1 | John Doe | 1 | [2023-01-15,2023-01-20) |
| 2 | Jane Smith | 1 | [2023-01-30,2023-02-02) |
| 3 | Jane Smith | 1 | [2023-02-25,2023-03-03) |
| 4 | Jordan Miller | 2 | [2023-01-30,2023-03-02) |
| 5 | Partial Outlier | 2 | [2023-03-28,2023-04-02) |
| 6 | Total Outlier | 2 | [2023-04-28,2023-05-02) |
INSERT 0 6
| booking_id | guest_name | room_category_id | book_price_period | base_price |
|---|---|---|---|---|
| 1 | John Doe | 1 | [2023-01-15,2023-01-20) | 80.00 |
| 2 | Jane Smith | 1 | [2023-01-30,2023-02-01) | 80.00 |
| 2 | Jane Smith | 1 | [2023-02-01,2023-02-02) | 85.00 |
| 3 | Jane Smith | 1 | [2023-02-25,2023-03-01) | 85.00 |
| 3 | Jane Smith | 1 | [2023-03-01,2023-03-03) | 88.00 |
| 4 | Jordan Miller | 2 | [2023-01-30,2023-02-01) | 100.00 |
| 4 | Jordan Miller | 2 | [2023-02-01,2023-03-01) | 105.00 |
| 4 | Jordan Miller | 2 | [2023-03-01,2023-03-02) | 108.00 |
| 5 | Partial Outlier | 2 | [2023-03-28,2023-04-01) | 108.00 |
SELECT 9
| booking_id | guest_name | room_category_id | the_day | base_price | sum_price_adjustment | total_price_of_booking |
|---|---|---|---|---|---|---|
| 1 | John Doe | 1 | 2023-01-15 | 80.00 | 18.00 | 529.00 |
| 1 | John Doe | 1 | 2023-01-16 | 80.00 | 18.00 | 529.00 |
| 1 | John Doe | 1 | 2023-01-17 | 80.00 | 31.00 | 529.00 |
| 1 | John Doe | 1 | 2023-01-18 | 80.00 | 31.00 | 529.00 |
| 1 | John Doe | 1 | 2023-01-19 | 80.00 | 31.00 | 529.00 |
| 2 | Jane Smith | 1 | 2023-01-30 | 80.00 | 32.00 | 341.00 |
| 2 | Jane Smith | 1 | 2023-01-31 | 80.00 | 32.00 | 341.00 |
| 2 | Jane Smith | 1 | 2023-02-01 | 85.00 | 32.00 | 341.00 |
| 3 | Jane Smith | 1 | 2023-02-25 | 85.00 | 0 | 516.00 |
| 3 | Jane Smith | 1 | 2023-02-26 | 85.00 | 0 | 516.00 |
| 3 | Jane Smith | 1 | 2023-02-27 | 85.00 | 0 | 516.00 |
| 3 | Jane Smith | 1 | 2023-02-28 | 85.00 | 0 | 516.00 |
| 3 | Jane Smith | 1 | 2023-03-01 | 88.00 | 0 | 516.00 |
| 3 | Jane Smith | 1 | 2023-03-02 | 88.00 | 0 | 516.00 |
| 4 | Jordan Miller | 2 | 2023-01-30 | 100.00 | 0 | 3248.00 |
| 4 | Jordan Miller | 2 | 2023-01-31 | 100.00 | 0 | 3248.00 |
| 4 | Jordan Miller | 2 | 2023-02-01 | 105.00 | 0 | 3248.00 |
| 4 | Jordan Miller | 2 | 2023-02-02 | 105.00 | 0 | 3248.00 |
| 4 | Jordan Miller | 2 | 2023-02-03 | 105.00 | 0 | 3248.00 |
| 4 | Jordan Miller | 2 | 2023-02-04 | 105.00 | 0 | 3248.00 |
| 4 | Jordan Miller | 2 | 2023-02-05 | 105.00 | 0 | 3248.00 |
| 4 | Jordan Miller | 2 | 2023-02-06 | 105.00 | 0 | 3248.00 |
| 4 | Jordan Miller | 2 | 2023-02-07 | 105.00 | 0 | 3248.00 |
| 4 | Jordan Miller | 2 | 2023-02-08 | 105.00 | 0 | 3248.00 |
| 4 | Jordan Miller | 2 | 2023-02-09 | 105.00 | 0 | 3248.00 |
| 4 | Jordan Miller | 2 | 2023-02-10 | 105.00 | 0 | 3248.00 |
| 4 | Jordan Miller | 2 | 2023-02-11 | 105.00 | 0 | 3248.00 |
| 4 | Jordan Miller | 2 | 2023-02-12 | 105.00 | 0 | 3248.00 |
| 4 | Jordan Miller | 2 | 2023-02-13 | 105.00 | 0 | 3248.00 |
| 4 | Jordan Miller | 2 | 2023-02-14 | 105.00 | 0 | 3248.00 |
| 4 | Jordan Miller | 2 | 2023-02-15 | 105.00 | 0 | 3248.00 |
| 4 | Jordan Miller | 2 | 2023-02-16 | 105.00 | 0 | 3248.00 |
| 4 | Jordan Miller | 2 | 2023-02-17 | 105.00 | 0 | 3248.00 |
| 4 | Jordan Miller | 2 | 2023-02-18 | 105.00 | 0 | 3248.00 |
| 4 | Jordan Miller | 2 | 2023-02-19 | 105.00 | 0 | 3248.00 |
| 4 | Jordan Miller | 2 | 2023-02-20 | 105.00 | 0 | 3248.00 |
| 4 | Jordan Miller | 2 | 2023-02-21 | 105.00 | 0 | 3248.00 |
| 4 | Jordan Miller | 2 | 2023-02-22 | 105.00 | 0 | 3248.00 |
| 4 | Jordan Miller | 2 | 2023-02-23 | 105.00 | 0 | 3248.00 |
| 4 | Jordan Miller | 2 | 2023-02-24 | 105.00 | 0 | 3248.00 |
| 4 | Jordan Miller | 2 | 2023-02-25 | 105.00 | 0 | 3248.00 |
| 4 | Jordan Miller | 2 | 2023-02-26 | 105.00 | 0 | 3248.00 |
| 4 | Jordan Miller | 2 | 2023-02-27 | 105.00 | 0 | 3248.00 |
| 4 | Jordan Miller | 2 | 2023-02-28 | 105.00 | 0 | 3248.00 |
| 4 | Jordan Miller | 2 | 2023-03-01 | 108.00 | 0 | 3248.00 |
| 5 | Partial Outlier | 2 | 2023-03-28 | 108.00 | 0 | 432.00 |
| 5 | Partial Outlier | 2 | 2023-03-29 | 108.00 | 0 | 432.00 |
| 5 | Partial Outlier | 2 | 2023-03-30 | 108.00 | 0 | 432.00 |
| 5 | Partial Outlier | 2 | 2023-03-31 | 108.00 | 0 | 432.00 |
SELECT 49
| booking_id | guest_name | room_category_id | booking_period | total_price |
|---|---|---|---|---|
| 1 | John Doe | 1 | [2023-01-15,2023-01-20) | 529.00 |
| 2 | Jane Smith | 1 | [2023-01-30,2023-02-02) | 341.00 |
| 3 | Jane Smith | 1 | [2023-02-25,2023-03-03) | 516.00 |
| 4 | Jordan Miller | 2 | [2023-01-30,2023-03-02) | 3248.00 |
| 5 | Partial Outlier | 2 | [2023-03-28,2023-04-02) | 432.00 |
SELECT 5