PostgreSQL 中的查询调优

我有一个在 17 秒内运行的查询，但我想不出优化此查询的方法。非常需要一些帮助。

EXPLAIN ANALYSE
CREATE materialized VIEW professores_fizeram_planejamentoTEST as
    SELECT unities.id as id_escola,
           unities.name as nome_escola,
           teachers.id as id_professor,
           teachers.name as nome_professor,
           datas.dia,
           COALESCE((SELECT true
               FROM lesson_plans
              WHERE lesson_plans.teacher_id = teachers.id and
                datas.dia between lesson_plans.start_at and lesson_plans.end_at
              LIMIT 1), false) as criou_plano_aula,
           COALESCE((select true
               from content_records
              where content_records.teacher_id = teachers.id and
                content_records.record_date = datas.dia
              limit 1), false) as criou_registro_conteudo
      FROM (SELECT i::date as dia,
               EXTRACT(year FROM i::date) as ano
          FROM generate_series(date_trunc('year', now()), now(), '1 day'::INTERVAL) i
          WHERE EXTRACT(dow from i::timestamp) in (1,2,3,4,5)) datas
      JOIN (SELECT distinct teacher_id, classroom_id, YEAR
          FROM teacher_discipline_classrooms) teacher_discipline_classrooms ON (teacher_discipline_classrooms.year = datas.ano)
      JOIN classrooms on (classrooms.id = teacher_discipline_classrooms.classroom_id)
      JOIN unities on (unities.id = classrooms.unity_id)
      JOIN teachers on (teachers.id = teacher_discipline_classrooms.teacher_id)
      WHERE NOT EXISTS(SELECT 1
                 FROM school_calendars
                 JOIN school_calendar_events on (school_calendar_events.school_calendar_id = school_calendars.id and
                                 school_calendar_events.event_type = 'no_school' and
                                 datas.dia between school_calendar_events.start_date and school_calendar_events.end_date)
                 WHERE school_calendars.unity_id = unities.id)

此查询返回以下分析

Nested Loop  (cost=143.840..3721.540 rows=38 width=66) (actual time=1.923..17270.125 rows=171231 loops=1)
  ->  Nested Loop  (cost=143.690..1523.510 rows=38 width=41) (actual time=1.744..5996.571 rows=171231 loops=1)
          Join Filter: (NOT (delta 3))
          Rows Removed by Join Filter: 15249
        ->  Nested Loop  (cost=143.550..203.530 rows=76 width=16) (actual time=1.661..568.049 rows=186480 loops=1)
              ->  Hash Join  (cost=143.270..165.450 rows=76 width=16) (actual time=1.651..183.740 rows=186660 loops=1)
                      Hash Cond: ((victor.juliet_seven)::double precision = echo_tango('quebec_four'::text, ((alpha_quebec_whiskey.alpha_quebec_whiskey)::date)::timestamp without time zone))
                    ->  HashAggregate  (cost=121.700..127.820 rows=612 width=12) (actual time=1.384..3.336 rows=2388 loops=1)
                            Group Key: victor.foxtrot_six, victor.oscar_kilo, victor.juliet_seven
                          ->  Seq Scan on victor  (cost=0.000..94.400 rows=3640 width=12) (actual time=0.004..0.563 rows=3640 loops=1)
                    ->  Hash  (cost=21.260..21.260 rows=25 width=8) (actual time=0.256..0.256 rows=180 loops=1)
                            Buckets: 1024  Batches: 1  Memory Usage: 16kB
                          ->  Function Scan on xray_yankee alpha_quebec_whiskey  (cost=0.010..21.260 rows=25 width=8) (actual time=0.081..0.195 rows=180 loops=1)
                                  Filter: (echo_tango('papa'::text, (alpha_quebec_whiskey)::timestamp without time zone) = ANY ('oscar_seven_charlie'::double precision[]))
                                  Rows Removed by Filter: 72
              ->  Index Scan using echo_victor on uniform  (cost=0.280..0.490 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=186660)
                      Index Cond: (quebec_seven = victor.oscar_kilo)
        ->  Index Scan using golf on four  (cost=0.140..0.160 rows=1 width=29) (actual time=0.001..0.001 rows=1 loops=186480)
                Index Cond: (quebec_seven = uniform.xray_victor)
        SubPlan
          ->  Nested Loop  (cost=0.280..34.110 rows=2 width=0) (actual time=0.027..0.027 rows=0 loops=186480)
                ->  Seq Scan on seven  (cost=0.000..1.990 rows=2 width=4) (actual time=0.003..0.008 rows=2 loops=186480)
                        Filter: (xray_victor = four.quebec_seven)
                        Rows Removed by Filter: 75
                ->  Index Scan using alpha_quebec_papa on two  (cost=0.280..16.050 rows=1 width=4) (actual time=0.008..0.008 rows=0 loops=372960)
                        Index Cond: (zulu = seven.quebec_seven)
                        Filter: (((xray_delta)::text = 'oscar_seven_golf'::text) AND ((alpha_quebec_whiskey.alpha_quebec_whiskey)::date >= foxtrot_three) AND ((alpha_quebec_whiskey.alpha_quebec_whiskey)::date <= lima))
                        Rows Removed by Filter: 14
  ->  Index Scan using tango on romeo  (cost=0.150..0.200 rows=1 width=29) (actual time=0.001..0.001 rows=1 loops=171231)
          Index Cond: (quebec_seven = victor.foxtrot_six)
  SubPlan
    ->  Limit  (cost=0.000..20.600 rows=1 width=0) (actual time=0.048..0.048 rows=0 loops=171231)
          ->  Seq Scan on five  (cost=0.000..20.600 rows=1 width=0) (actual time=0.045..0.045 rows=0 loops=171231)
                  Filter: ((foxtrot_six = romeo.quebec_seven) AND ((alpha_quebec_whiskey.alpha_quebec_whiskey)::date >= oscar_echo) AND ((alpha_quebec_whiskey.alpha_quebec_whiskey)::date <= xray_three))
                  Rows Removed by Filter: 246
  SubPlan
    ->  Limit  (cost=4.810..37.030 rows=1 width=0) (actual time=0.015..0.015 rows=0 loops=171231)
          ->  Bitmap Heap Scan on whiskey  (cost=4.810..37.030 rows=1 width=0) (actual time=0.011..0.011 rows=0 loops=171231)
                  Recheck Cond: (foxtrot_six = romeo.quebec_seven)
                  Filter: (foxtrot_tango = (alpha_quebec_whiskey.alpha_quebec_whiskey)::date)
                  Rows Removed by Filter: 28
                  Heap Blocks: exact=258248
                ->  Bitmap Index Scan on juliet_bravo  (cost=0.000..4.810 rows=70 width=0) (actual time=0.003..0.003 rows=37 loops=171231)
                        Index Cond: (foxtrot_six = romeo.quebec_seven)

解释 1 - 结果 解释 2 - 结果

谢谢。