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?.

select version();

version

PostgreSQL 10.23 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 process(userid int, date_in date, check_in time);
create table checkinout(userid int,checktime  timestamp, sn varchar);
create table table3 (sn varchar, alias varchar);

CREATE TABLE

CREATE TABLE

CREATE TABLE

insert into process values(145, '2017-01-23','08:56:05');
insert into process values(254, '2017-01-24','08:56:54');
insert into process values(202, '2017-01-25','08:53:26');
insert into process values(15, '2017-01-26','08:47:40');

insert into checkinout values(145,'2017-01-23 08:56:05','0345135200184');
insert into checkinout values(254,'2017-01-24 08:56:54','0345135200184');
insert into checkinout values(202,'2017-01-25 08:53:26','0345135200184');
insert into checkinout values(15 ,'2017-01-26 08:47:40','0345135200184');

insert into table3 values('0345135200184','Alam Sutera');

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

select 
t1.userid, t1.date_in, t1.check_in, t2.checktime, t3.alias
from process t1
inner join checkinout t2 on t2. checktime= date_in + check_in and t1.userid=t2.userid
inner join table3 t3 on t2.sn=t3.sn

userid
date_in
check_in
checktime
alias

15
2017-01-26
08:47:40
2017-01-26 08:47:40
Alam Sutera

202
2017-01-25
08:53:26
2017-01-25 08:53:26
Alam Sutera

254
2017-01-24
08:56:54
2017-01-24 08:56:54
Alam Sutera

145
2017-01-23
08:56:05
2017-01-23 08:56:05
Alam Sutera

SELECT 4

userid	date_in	check_in	checktime	alias
15	2017-01-26	08:47:40	2017-01-26 08:47:40	Alam Sutera
202	2017-01-25	08:53:26	2017-01-25 08:53:26	Alam Sutera
254	2017-01-24	08:56:54	2017-01-24 08:56:54	Alam Sutera
145	2017-01-23	08:56:05	2017-01-23 08:56:05	Alam Sutera