Как улучшить или ускорить запрос?

Question

[bohdan-shulha]

В ходе решения задачи по практикованию своего SQL-fu, у меня возникла следующая задача: необходимо выбрать последнюю просмотренную и следующие за ней 3 серии. Если просмотренных нет - выбрать первые четыре серии сериала.

SQL запрос

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

Структура базы с тестовыми записями

-- ----------------------------
-- 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;

До этого имел дело только с MySQL. Поначалу пытался как-то прилепить сюда WITH, но потерпел фиаско.

Answer 1

[Amver]

Можно использовать window-функцию row_number() для минимизации обращения к таблице

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;

Comments

[bohdan-shulha]

Очень круто, спасибо!