How to improve or speed up the query?

Question

PostgreSQL

In the course of solving the problem of practicing my SQL-fu, I had the following task: you need to select the last viewed and the following 3 series. If there are no viewers, select the first four episodes of the series.

SQL query

SELECT
  *
FROM episode AS me
LEFT OUTER JOIN user_episode AS ue ON ue.episode_id = me.id
WHERE
  me.id IN ( 
    SELECT id FROM episode AS me2 WHERE me2.id >= ( 
      SELECT MAX(episode_id) FROM user_episode 
      INNER JOIN episode ON episode.id = user_episode.episode_id
      WHERE episode.material_id = me.material_id
    ) 
    ORDER BY me2.id
    LIMIT 4
  )
  OR me.id IN (
    SELECT id 
    FROM episode AS me2
    WHERE me2.material_id NOT IN (
      SELECT DISTINCT episode.material_id
      FROM episode
      INNER JOIN user_episode ON user_episode.episode_id = episode.id
    )
    ORDER BY me2.id
    LIMIT 4
  )
ORDER BY me.id

The structure of the database with test records

-- ----------------------------
-- Table structure for episode
-- ----------------------------
DROP TABLE IF EXISTS "public"."episode";
CREATE TABLE "public"."episode" (
"id" int4 DEFAULT nextval('material_episode_id_seq'::regclass) NOT NULL,
"material_id" int4 NOT NULL,
"name" varchar(255) COLLATE "default" NOT NULL
)
WITH (OIDS=FALSE)

;

-- ----------------------------
-- Records of episode
-- ----------------------------
INSERT INTO "public"."episode" VALUES ('3', '1', 'МС 1');
INSERT INTO "public"."episode" VALUES ('4', '1', 'МС 2');
INSERT INTO "public"."episode" VALUES ('5', '1', 'МС 3');
INSERT INTO "public"."episode" VALUES ('6', '1', 'МС 4');
INSERT INTO "public"."episode" VALUES ('7', '1', 'МС 5');
INSERT INTO "public"."episode" VALUES ('8', '1', 'МС 6');
INSERT INTO "public"."episode" VALUES ('9', '1', 'МС 7');
INSERT INTO "public"."episode" VALUES ('10', '1', 'МС 8');
INSERT INTO "public"."episode" VALUES ('11', '2', 'ВК 1');
INSERT INTO "public"."episode" VALUES ('12', '2', 'ВК 2');
INSERT INTO "public"."episode" VALUES ('13', '2', 'ВК 3');
INSERT INTO "public"."episode" VALUES ('14', '2', 'ВК 4');
INSERT INTO "public"."episode" VALUES ('15', '2', 'ВК 5');
INSERT INTO "public"."episode" VALUES ('16', '2', 'ВК 6');
INSERT INTO "public"."episode" VALUES ('17', '2', 'ВК 7');
INSERT INTO "public"."episode" VALUES ('18', '2', 'ВК 8');
INSERT INTO "public"."episode" VALUES ('19', '3', 'Голум');
INSERT INTO "public"."episode" VALUES ('20', '3', 'Беглая гора');
INSERT INTO "public"."episode" VALUES ('21', '3', 'Тихий ужас');

-- ----------------------------
-- Table structure for material
-- ----------------------------
DROP TABLE IF EXISTS "public"."material";
CREATE TABLE "public"."material" (
"id" int4 DEFAULT nextval('material_id_seq'::regclass) NOT NULL,
"name" varchar(255) COLLATE "default" NOT NULL
)
WITH (OIDS=FALSE)

;

-- ----------------------------
-- Records of material
-- ----------------------------
INSERT INTO "public"."material" VALUES ('1', 'Матрица');
INSERT INTO "public"."material" VALUES ('2', 'Властелин колец');
INSERT INTO "public"."material" VALUES ('3', 'Хоббит');

-- ----------------------------
-- Table structure for user_episode
-- ----------------------------
DROP TABLE IF EXISTS "public"."user_episode";
CREATE TABLE "public"."user_episode" (
"id" int4 DEFAULT nextval('user_episode_id_seq'::regclass) NOT NULL,
"episode_id" int4 NOT NULL,
"user_id" int4 NOT NULL
)
WITH (OIDS=FALSE)

;

-- ----------------------------
-- Records of user_episode
-- ----------------------------
INSERT INTO "public"."user_episode" VALUES ('3', '3', '1');
INSERT INTO "public"."user_episode" VALUES ('4', '4', '1');
INSERT INTO "public"."user_episode" VALUES ('5', '5', '1');
INSERT INTO "public"."user_episode" VALUES ('6', '14', '1');
INSERT INTO "public"."user_episode" VALUES ('7', '15', '1');
INSERT INTO "public"."user_episode" VALUES ('8', '16', '1');

-- ----------------------------
-- Uniques structure for table episode
-- ----------------------------
ALTER TABLE "public"."episode" ADD UNIQUE ("id");

-- ----------------------------
-- Foreign Key structure for table "public"."episode"
-- ----------------------------
ALTER TABLE "public"."episode" ADD FOREIGN KEY ("material_id") REFERENCES "public"."material" ("id") ON DELETE CASCADE ON UPDATE CASCADE;


-- ----------------------------
-- Uniques structure for table material
-- ----------------------------
ALTER TABLE "public"."material" ADD UNIQUE ("id");


-- ----------------------------
-- Foreign Key structure for table "public"."user_episode"
-- ----------------------------
ALTER TABLE "public"."user_episode" ADD FOREIGN KEY ("episode_id") REFERENCES "public"."episode" ("id") ON DELETE CASCADE ON UPDATE CASCADE;

Before that, I only dealt with MySQL. At first I tried to somehow stick WITH here, but failed.

Answer 1

You can use the row_number() window function to minimize table access

with max_m_ep as
(	select tue.user_id, te.material_id, max(tue.episode_id) AS "_ep" from user_episode tue
                  left join episode te ON te.id = tue.episode_id
                  group by tue.user_id, te.material_id )
select * from
(
  select e.*, row_number() OVER (partition by e.material_id order by e.id) AS ep_order
  from episode e
  left join max_m_ep mx ON mx.material_id = e.material_id and mx.user_id = 2
    where e.id >= mx._ep
    or mx._ep is null

    order by e.id
) data
where ep_order < 5;