Postgres 10

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 cars(car_id integer not null primary key, km_driven integer);
insert into cars values (2,430),(3,112),(4,90),(5,201);

create table pricing(from_km int not null primary key, to_km int, price numeric(2,1));
insert into pricing values(1,100,2),(101,200,1),(201,null,0.5);

CREATE TABLE

INSERT 0 4

CREATE TABLE

INSERT 0 3

select * from cars;

car_id
km_driven

2
430

3
112

4
90

5
201

SELECT 4

select * from pricing;

from_km
to_km
price

1
100
2.0

101
200
1.0

201
null
0.5

SELECT 3

select c.car_id, c.km_driven, 
       sum(( least(p.to_km, c.km_driven) - p.from_km + 1) * p.price) as dist_price
from cars c join
     pricing p
     on c.km_driven >= p.from_km
group by c.car_id, c.km_driven;

car_id
km_driven
dist_price

2
430
415.0

3
112
212.0

4
90
180.0

5
201
300.5

SELECT 4

select c.car_id, c.km_driven, 
       sum(least(p.to_km, c.km_driven) - p.from_km + 1) * price) as dist_price
from cars c join
     pricing p
     on c.km_driven >= p.from_km
group by c.car_id, c.km_driven;

ERROR:  syntax error at or near ")"
LINE 2: ...ast(p.to_km, c.km_driven) - p.from_km + 1) * price) as dist_...
                                                             ^

select 
  car_id, km_driven, 
  sum((least(finish,km_driven)-start+1)*price) as dist_price
from cars,pricing
where km_driven >= start
group by car_id,km_driven

ERROR:  column "finish" does not exist
LINE 3:   sum((least(finish,km_driven)-start+1)*price) as dist_price
                     ^