Postgres 16

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 conversion_rates (
  from_currency VARCHAR(3) NOT NULL,
  to_currency VARCHAR(3) NOT NULL,
  effective_date DATE NOT NULL,
  conversion_rate DECIMAL(10,2) NOT NULL
);

CREATE TABLE


INSERT INTO conversion_rates (from_currency, to_currency, effective_date, conversion_rate)
VALUES ('USD', 'INR', '2024-03-01', 80);

INSERT INTO conversion_rates (from_currency, to_currency, effective_date, conversion_rate)
VALUES ('USD', 'INR', '2024-01-01', 85);

INSERT INTO conversion_rates (from_currency, to_currency, effective_date, conversion_rate)
VALUES ('USD', 'GBP', '2023-10-01', 0.80);

INSERT INTO conversion_rates (from_currency, to_currency, effective_date, conversion_rate)
VALUES ('USD', 'GBP', '2024-03-01', 0.85);

INSERT INTO conversion_rates (from_currency, to_currency, effective_date, conversion_rate)
VALUES ('USD', 'AUD', '2023-03-01', 1.55);

INSERT INTO conversion_rates (from_currency, to_currency, effective_date, conversion_rate)
VALUES ('USD', 'AUD', '2024-03-01', 1.60);

INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1


select * from conversion_rates;


from_currency
to_currency
effective_date
conversion_rate


USD
INR
2024-03-01
80.00

USD
INR
2024-01-01
85.00

USD
GBP
2023-10-01
0.80

USD
GBP
2024-03-01
0.85

USD
AUD
2023-03-01
1.55

USD
AUD
2024-03-01
1.60



SELECT 6


SELECT
  from_currency,
  to_currency,
  MAX(effective_date) FILTER (WHERE effective_date <= '2023-12-05') AS effective_date,
  MAX(conversion_rate) FILTER (WHERE effective_date <= '2023-12-05') AS conversion_rate
FROM conversion_rates
WHERE from_currency = 'USD'
GROUP BY from_currency, to_currency
ORDER BY from_currency, to_currency;


from_currency
to_currency
effective_date
conversion_rate


USD
AUD
2023-03-01
1.55

USD
GBP
2023-10-01
0.80

USD
INR
null
null



SELECT 3

from_currency	to_currency	effective_date	conversion_rate
USD	INR	2024-03-01	80.00
USD	INR	2024-01-01	85.00
USD	GBP	2023-10-01	0.80
USD	GBP	2024-03-01	0.85
USD	AUD	2023-03-01	1.55
USD	AUD	2024-03-01	1.60