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) |
INSERT 0 4
booking_id | guest_name | room_category_id | booking_period | sum |
---|---|---|---|---|
1 | John Doe | 1 | [2023-01-15,2023-01-20) | 400.00 |
2 | Jane Smith | 1 | [2023-01-30,2023-02-02) | 245.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 |
SELECT 4
booking_id | guest_name | room_category_id | booking_period | booking_price_adjustment |
---|---|---|---|---|
1 | John Doe | 1 | [2023-01-15,2023-01-20) | 129.00 |
2 | Jane Smith | 1 | [2023-01-30,2023-02-02) | 96.00 |
SELECT 2
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 |
SELECT 4