Postgres 11

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;

ERROR:  column "version" does not exist
LINE 1: select @@version;
                 ^


create table sessionlatest
(
  session_id bigint                   not null
    constraint "PK_session_id_latest"
    primary key,
  update_id bigint not null
);

CREATE TABLE


create table session(
  update_id                                     bigint                   not null
    constraint "PK_gameSession_update"
    primary key,
  session_id                                    bigint                   not null,
  status text  not null
);

CREATE TABLE


insert into sessionlatest 
  (update_id, session_id)
values
  (1,1),
  (2,2),
  (3,3),
  (5,4),
  (8,5)  ;

INSERT 0 5


insert into session 
  (update_id, session_id, status)
values
  (1,1, 'STARTED'),
  (2,2, 'STARTED'),
  (3,3, 'STARTED'),
  (4,4, 'STARTED'),
  (5,4, 'STARTED'),

  (6,5, 'STARTED'),
  (7,5, 'STARTED'),
  (8,5, 'FINISHED') ;

INSERT 0 8


-- EXPLAIN ANALYSE 
SELECT *
FROM session gs
join "sessionlatest" gsl
  on gs."update_id" = gsl."update_id"
WHERE gs."status" = 'STARTED'
order by gs."session_id" desc
LIMIT 500;


update_id
session_id
status
session_id
update_id


5
4
STARTED
4
5

3
3
STARTED
3
3

2
2
STARTED
2
2

1
1
STARTED
1
1



SELECT 4


    select gs.*, gsl.*
    from sessionlatest gsl
      join lateral
      ( select s.*
        from session s
        where s.update_id = gsl.update_id
        order by s.update_id desc
        LIMIT 1
      ) as gs
      on gs.status = 'STARTED'
    order by gsl.session_id desc
    limit 500 ;


update_id
session_id
status
session_id
update_id


5
4
STARTED
4
5

3
3
STARTED
3
3

2
2
STARTED
2
2

1
1
STARTED
1
1



SELECT 4


    select gs.*, gsl.*
    from sessionlatest gsl
      join lateral
      ( select s.*
        from session s
        -- where s.update_id = gsl.update_id
        where s.session_id = gsl.session_id
        order by s.update_id desc
        LIMIT 1
      ) as gs
      on gs.status = 'STARTED'
    order by gsl.session_id desc
    limit 500 ;


update_id
session_id
status
session_id
update_id


5
4
STARTED
4
5

3
3
STARTED
3
3

2
2
STARTED
2
2

1
1
STARTED
1
1



SELECT 4


    select gs.*, gsl.*
    from sessionlatest gsl
      join lateral
      ( select s.*
        from session s
        where s.session_id = gsl.session_id
          and s.update_id = gsl.update_id
        order by s.update_id desc
        LIMIT 1
      ) as gs
      on gs.status = 'STARTED'
    order by gsl.session_id desc
    limit 500 ;


update_id
session_id
status
session_id
update_id


5
4
STARTED
4
5

3
3
STARTED
3
3

2
2
STARTED
2
2

1
1
STARTED
1
1



SELECT 4