postgresql - PostgreSQL 13 中的查询优化
问题描述
问题：
SQL QUERY：需要用户 (cuenta_usuario) 的姓名 ("nombre") 和电子邮件 ("e_mail")，这些用户可以访问评级 ("valoracion") 超过 5 的内容 (contenidos) 并拥有电影 (peliculas)时长（“durasion”）在 90 到 100 分钟之间或系列（系列）少于 6 个季节（“temporadas”）或超过 15 个章节（“capitulos”）；并且这些用户可以看到在 5 月的前 7 天内至少支付过一次付款 (pago) 的超过 3 个相关类型 (generos) 的内容
我需要为 Netflix 数据库优化以下查询，因为它从不显示结果。我在同一个数据库中用更少的行尝试了相同的查询，它运行良好，但原始数据库有超过 20.000.000 行，它从不显示结果。这是查询：
select "nombre","e_mail" from cuenta_usuario
-- Inner join cuenta_usuario --> pago
inner join (select "ID_cuenta_usuario", "suscripcion_ID_suscripcion" from pago
where "fecha">'2020-05-01' and "fecha"<'2020-05-07')
AS "P" on "ID"="ID_cuenta_usuario"
-- Inner join pago --> suscripción
inner join (select "suscripcion_ID" from suscripcion)
as "S" on "P"."suscripcion_ID_suscripcion"="S"."suscripcion_ID"
-- Inner join suscripción --> suscripción_contenidos
inner join (select "suscripcion_ID_suscripcion","producto_ID_contenidos" from suscripcion_contenidos)
as "SC" on "S"."suscripcion_ID"="SC"."suscripcion_ID_suscripcion"
-- Inner join suscripción_contenidos --> contenidos
inner join (select "producto_ID" from contenidos
where "valoracion">5)
as "C" on "SC"."producto_ID_contenidos"="C"."producto_ID"
-- Inner join contenidos --> peliculas y series
inner join (select peliculas."producto_ID_contenidos" as "pelPID",series."producto_ID_contenidos" as "serPID" from peliculas,series
where ("duracion" between 90 and 100) or ("temporadas"<6 or "capitulos">15)
and peliculas."producto_ID_contenidos"=series."producto_ID_contenidos")
as "PS" on "C"."producto_ID"="producto_ID_contenidos"
-- Inner join contenidos --> genero_contenidos
inner join(select "producto_ID_contenidos" as "gcPID","genero_ID_genero" as "generos" from genero_contenidos)
as "GC" on "C"."producto_ID"="GC"."gcPID"
-- Inner join genero_contenidos --> genero
inner join(select "genero_ID" as "gID" from genero)
as "G" on "GC"."generos"="G"."gID"
-- Agrupamos resultado en función del nombre y el e_mail de los usuarios
group by "nombre","e_mail"
-- Debe tener contenidos con más de 3 géneros asociados
having count (distinct("gID"))>3
DB 的模型如下： BD 模型（如果有帮助）
我尝试添加索引之类的东西，但 Postgrest 只使用了我创建的一些索引。Postgres 估计的查询计划如下：
"GroupAggregate (cost=1152464845530975390662656.00..1463024484904095765757952.00 rows=666667 width=40)"
" Group Key: cuenta_usuario.nombre, cuenta_usuario.e_mail"
" Filter: (count(DISTINCT genero.""genero_ID"") > 3)"
" -> Gather Merge (cost=1152464845530975390662656.00..1444235556551140296359936.00 rows=2505190447060735695519744 width=72)"
" Workers Planned: 2"
" -> Sort (cost=1152464845530975390662656.00..1155074418913330384076800.00 rows=1043829352941973206466560 width=72)"
" Sort Key: cuenta_usuario.nombre, cuenta_usuario.e_mail"
" -> Hash Join (cost=52617333935284.70..8212401427811531751424.00 rows=1043829352941973206466560 width=72)"
" Hash Cond: (genero_contenidos.""genero_ID_genero"" = genero.""genero_ID"")"
" -> Parallel Hash Join (cost=52617333935283.25..4891719298764879429632.00 rows=1043829352941973206466560 width=45)"
" Hash Cond: (pago.""suscripcion_ID_suscripcion"" = suscripcion.""suscripcion_ID"")"
" -> Merge Join (cost=9825389.26..594642346.41 rows=38982692229 width=55)"
" Merge Cond: (pago.""suscripcion_ID_suscripcion"" = suscripcion_contenidos.""suscripcion_ID_suscripcion"")"
" -> Sort (cost=440627.45..440953.26 rows=130324 width=45)"
" Sort Key: pago.""suscripcion_ID_suscripcion"""
" -> Parallel Hash Join (cost=56908.40..425544.29 rows=130324 width=45)"
" Hash Cond: (pago.""ID_cuenta_usuario"" = cuenta_usuario.""ID"")"
" -> Parallel Bitmap Heap Scan on pago (cost=4266.40..363961.20 rows=130324 width=11)"
" Recheck Cond: ((fecha > '2020-05-01'::date) AND (fecha < '2020-05-07'::date))"
" -> Bitmap Index Scan on arbol_fecha (cost=0.00..4188.21 rows=312777 width=0)"
" Index Cond: ((fecha > '2020-05-01'::date) AND (fecha < '2020-05-07'::date))"
" -> Parallel Hash (cost=34900.33..34900.33 rows=833333 width=46)"
" -> Parallel Seq Scan on cuenta_usuario (cost=0.00..34900.33 rows=833333 width=46)"
" -> Materialize (cost=9384761.81..9534360.41 rows=29919720 width=10)"
" -> Sort (cost=9384761.81..9459561.11 rows=29919720 width=10)"
" Sort Key: suscripcion_contenidos.""suscripcion_ID_suscripcion"""
" -> Hash Join (cost=1367846.01..4135564.91 rows=29919720 width=10)"
" Hash Cond: (genero_contenidos.""producto_ID_contenidos"" = suscripcion_contenidos.""producto_ID_contenidos"")"
" -> Seq Scan on genero_contenidos (cost=0.00..924254.28 rows=59995428 width=13)"
" -> Hash (cost=1184729.01..1184729.01 rows=9974000 width=21)"
" -> Hash Join (cost=580205.00..1184729.01 rows=9974000 width=21)"
" Hash Cond: (suscripcion_contenidos.""producto_ID_contenidos"" = contenidos.""producto_ID"")"
" -> Seq Scan on suscripcion_contenidos (cost=0.00..317749.00 rows=20000000 width=13)"
" -> Hash (cost=416569.00..416569.00 rows=9974000 width=8)"
" -> Seq Scan on contenidos (cost=0.00..416569.00 rows=9974000 width=8)"
" Filter: (valoracion > '5'::numeric)"
" -> Parallel Hash (cost=34317517658090.93..34317517658090.93 rows=1115416774205125 width=5)"
" -> Nested Loop (cost=20000059362.35..34317517658090.93 rows=1115416774205125 width=5)"
" -> Nested Loop (cost=10000059362.35..845014431937.93 rows=11154167742051 width=0)"
" -> Parallel Seq Scan on series (cost=0.00..105332.12 rows=4165512 width=17)"
" -> Bitmap Heap Scan on peliculas (cost=59362.35..173681.57 rows=2677742 width=12)"
" Recheck Cond: (((duracion >= '90'::numeric) AND (duracion <= '100'::numeric)) OR (""producto_ID_contenidos"" = series.""producto_ID_contenidos""))"
" Filter: (((duracion >= '90'::numeric) AND (duracion <= '100'::numeric)) OR (((series.temporadas < '6'::numeric) OR (series.capitulos > '15'::numeric)) AND (""producto_ID_contenidos"" = series.""producto_ID_contenidos"")))"
" -> BitmapOr (cost=59362.35..59362.35 rows=2677743 width=0)"
" -> Bitmap Index Scan on arbol_duracion (cost=0.00..56189.85 rows=2677742 width=0)"
" Index Cond: ((duracion >= '90'::numeric) AND (duracion <= '100'::numeric))"
" -> Bitmap Index Scan on peliculas_pk (cost=0.00..2.37 rows=1 width=0)"
" Index Cond: (""producto_ID_contenidos"" = series.""producto_ID_contenidos"")"
" -> Seq Scan on suscripcion (cost=0.00..2.00 rows=100 width=5)"
" -> Hash (cost=1.20..1.20 rows=20 width=32)"
" -> Seq Scan on genero (cost=0.00..1.20 rows=20 width=32)"
我还尝试了一些其他类型的优化：
set constraint_exclusion = on;
set cursor_tuple_fraction = 1.0;
但它们都不能正常工作。问题可能是查询的编写方式，或者我应该添加其他类型的修改。希望你能帮我解决这个问题。谢谢！
解决方案
推荐阅读
- octave - 在程序以八度自动结束后重新运行程序
- javascript - 如何在 React Native 中使用 useState 在 AsyncStorage 中保存数据？
- asp.net-mvc - 在 C# 中以通用存储库模式调用数据库视图
- reactjs - 从 store 访问 state 和 props
- c# - C# 单选按钮 SQL Server
- routes - 在我在 Firefox 的 Angular 应用程序中的 accessiBe 中启用 Seizure Safe 配置文件后的导航问题
- html - 嵌入网站时如何仅循环播放我的 youtube 频道中的视频
- tensorflow - 为什么 Keras Embedding 层的 input_dim = vocab_size + 1
- angular - 如何将 Angular 部署到 IIS 并通过域名而不是域名和应用名称访问应用
- amazon-web-services - 如何将 AWS EFS 卷附加到 EC2 现货实例？