wiki:QueryOptimization

Context Navigation

Version 4 (modified by 231151, 5 days ago) ( diff )
--

QueryOptimization

View1: Анализа на поглед - Активни возења

Примарен филтер за погледот vw_active_trips ќе биде според STATUS на патувањето ('SCHEDULED', 'IN_PROGRESS'), бидејќи view-от ги прикажува само активните возења во моментот.
Примарен случај на употреба е за real-time мониторинг на активни возења. За овој поглед ни се важни перформансите, бидејќи се користи континуирано.
Иницијалното време за извршување на погледот е 7.38 секунди. Ова не е прифатливо време за апликација која треба да прикажува live статус на возења, па затоа пристапуваме кон индексирање на колоната STATUS во табелата TRIP.
Набљудувани операции се full scan на табелата TRIP без индекс на колоната STATUS, со повеќе Hash Join операции низ ROUTE, VEHICLE, DRIVER и EMPLOYEE табелите.
Времето изминато во извршување на query-то со индекс изнесува 485 ms (0.48 секунди), и тоа е прифатливо подобрување — 15 пати побрзо.

Hash Left Join  (cost=3171.49..15071.96 rows=135727 width=287) (actual time=31.892..479.130 rows=135807 loops=1)
  Hash Cond: (t.vehicle_type_id = vt.vehicle_type_id)
  ->  Hash Join  (cost=3151.59..14013.71 rows=135727 width=155) (actual time=31.841..429.095 rows=135807 loops=1)
        Hash Cond: ((dr.employee_id)::text = (e.employee_id)::text)
        ->  Hash Join  (cost=2048.09..12553.87 rows=135727 width=147) (actual time=17.571..354.868 rows=135807 loops=1)
              Hash Cond: ((t.driver_id)::text = (dr.driver_id)::text)
              ->  Hash Join  (cost=1670.84..11820.22 rows=135727 width=139) (actual time=11.744..290.015 rows=135807 loops=1)
                    Hash Cond: ((t.vehicle_id)::text = (v.vehicle_id)::text)
                    ->  Hash Join  (cost=1504.34..11297.13 rows=135727 width=123) (actual time=9.158..232.483 rows=135807 loops=1)
                          Hash Cond: ((t.route_id)::text = (r.route_id)::text)
                          ->  Bitmap Heap Scan on trip t  (cost=1456.72..10891.31 rows=135727 width=97) (actual time=8.634..168.601 rows=135807 loops=1)
                                Recheck Cond: ((status)::text = ANY ('{SCHEDULED,IN_PROGRESS}'::text[]))
                                Heap Blocks: exact=7738
                                ->  Bitmap Index Scan on idx_trip_status  (cost=0.00..1422.79 rows=135727 width=0) (actual time=7.136..7.136 rows=135807 loops=1)
                                      Index Cond: ((status)::text = ANY ('{SCHEDULED,IN_PROGRESS}'::text[]))
                          ->  Hash  (cost=37.83..37.83 rows=783 width=41) (actual time=0.458..0.458 rows=783 loops=1)
                                Buckets: 1024  Batches: 1  Memory Usage: 65kB
                                ->  Seq Scan on route r  (cost=0.00..37.83 rows=783 width=41) (actual time=0.053..0.216 rows=783 loops=1)
                    ->  Hash  (cost=104.00..104.00 rows=5000 width=21) (actual time=2.512..2.513 rows=5000 loops=1)
                          Buckets: 8192  Batches: 1  Memory Usage: 329kB
                          ->  Seq Scan on vehicle v  (cost=0.00..104.00 rows=5000 width=21) (actual time=0.009..1.018 rows=5000 loops=1)
              ->  Hash  (cost=221.00..221.00 rows=12500 width=20) (actual time=5.693..5.693 rows=12500 loops=1)
                    Buckets: 16384  Batches: 1  Memory Usage: 771kB
                    ->  Seq Scan on driver dr  (cost=0.00..221.00 rows=12500 width=20) (actual time=0.021..2.062 rows=12500 loops=1)
        ->  Hash  (cost=791.00..791.00 rows=25000 width=20) (actual time=14.020..14.021 rows=25000 loops=1)
              Buckets: 32768  Batches: 1  Memory Usage: 1542kB
              ->  Seq Scan on employee e  (cost=0.00..791.00 rows=25000 width=20) (actual time=0.025..6.289 rows=25000 loops=1)
  ->  Hash  (cost=14.40..14.40 rows=440 width=122) (actual time=0.037..0.038 rows=3 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 9kB
        ->  Seq Scan on vehicle_type vt  (cost=0.00..14.40 rows=440 width=122) (actual time=0.010..0.012 rows=3 loops=1)
Planning Time: 4.422 ms
Execution Time: 485.098 ms

По индексирање, планерот користи Bitmap Index Scan на idx_trip_status наместо Seq Scan, со што филтрирањето на 135,807 активни возења се врши директно преку индексот.

View2: Анализа на поглед - Распоред на возачи и инструктори

Примарен филтер за погледот vw_staff_schedule ќе биде според DRIVER_ID, CONDUCTOR_ID, TRIP_ID и SCHEDULE_DATE, бидејќи view-от со UNION ALL ги спојува распоредите на возачи и кондуктори.
Примарен случај на употреба е преглед на смени на персоналот по ден и линија. View-от користи UNION ALL па без индекси двете страни — DRIVER_SCHEDULE и CONDUCTOR_SCHEDULE — се скенираат целосно.
Иницијалното време за извршување на погледот е над 30 минути (query прекинато). Ова е сосема неприфатливо, па затоа пристапуваме кон индексирање на двете распоредни табели.
Набљудувани операции се Seq Scan на DRIVER_SCHEDULE (387,533 редови) и Seq Scan на CONDUCTOR_SCHEDULE (271,712 редови), со повеќе Hash Join операции низ TRIP, EMPLOYEE, SHIFT, ROUTE и VEHICLE.
Времето изминато во извршување на query-то со индекси изнесува 22.1 секунди, вкупно обработени 659,245 редови.

Append  (cost=21578.58..91196.61 rows=659245 width=269) (actual time=265.002..22100.792 rows=659245 loops=1)
  ->  Hash Join  (cost=21578.58..47282.64 rows=387533 width=269) (actual time=265.001..11422.640 rows=387533 loops=1)
        Hash Cond: ((t.vehicle_id)::text = (v.vehicle_id)::text)
        ->  Hash Join  (cost=21412.08..44160.34 rows=387533 width=216) (actual time=263.090..11245.169 rows=387533 loops=1)
              Hash Cond: ((t.route_id)::text = (r.route_id)::text)
              ->  Hash Join  (cost=21364.47..43089.94 rows=387533 width=203) (actual time=262.753..11133.889 rows=387533 loops=1)
                    Hash Cond: (ds.shift_id = sh.shift_id)
                    ->  Hash Join  (cost=21345.24..42042.93 rows=387533 width=73) (actual time=254.783..11030.818 rows=387533 loops=1)
                          Hash Cond: ((dr.employee_id)::text = (e.employee_id)::text)
                          ->  Hash Join  (cost=20241.74..39921.98 rows=387533 width=55) (actual time=241.790..10861.173 rows=387533 loops=1)
                                Hash Cond: ((ds.trip_id)::text = (t.trip_id)::text)
                                ->  Hash Join  (cost=377.25..9577.20 rows=387533 width=35) (actual time=27.301..10254.297 rows=387533 loops=1)
                                      Hash Cond: ((ds.driver_id)::text = (dr.driver_id)::text)
                                      ->  Seq Scan on driver_schedule ds  (cost=0.00..8182.33 rows=387533 width=35) (actual time=23.007..10084.784 rows=387533 loops=1)
                                      ->  Hash  (cost=221.00..221.00 rows=12500 width=12) (actual time=4.261..4.262 rows=12500 loops=1)
                                            Buckets: 16384  Batches: 1  Memory Usage: 674kB
                                            ->  Seq Scan on driver dr  (cost=0.00..221.00 rows=12500 width=12) (actual time=0.019..1.918 rows=12500 loops=1)
                                ->  Hash  (cost=11613.33..11613.33 rows=387533 width=41) (actual time=213.993..213.994 rows=387533 loops=1)
                                      Buckets: 131072  Batches: 8  Memory Usage: 4531kB
                                      ->  Seq Scan on trip t  (cost=0.00..11613.33 rows=387533 width=41) (actual time=0.011..78.219 rows=387533 loops=1)
                          ->  Hash  (cost=791.00..791.00 rows=25000 width=30) (actual time=12.938..12.939 rows=25000 loops=1)
                                Buckets: 32768  Batches: 1  Memory Usage: 1787kB
                                ->  Seq Scan on employee e  (cost=0.00..791.00 rows=25000 width=30) (actual time=0.012..6.373 rows=25000 loops=1)
                    ->  Hash  (cost=14.10..14.10 rows=410 width=138) (actual time=7.960..7.960 rows=3 loops=1)
                          Buckets: 1024  Batches: 1  Memory Usage: 9kB
                          ->  Seq Scan on shift sh  (cost=0.00..14.10 rows=410 width=138) (actual time=7.954..7.955 rows=3 loops=1)
              ->  Hash  (cost=37.83..37.83 rows=783 width=41) (actual time=0.326..0.327 rows=783 loops=1)
                    Buckets: 1024  Batches: 1  Memory Usage: 65kB
                    ->  Seq Scan on route r  (cost=0.00..37.83 rows=783 width=41) (actual time=0.022..0.155 rows=783 loops=1)
        ->  Hash  (cost=104.00..104.00 rows=5000 width=13) (actual time=1.888..1.889 rows=5000 loops=1)
              Buckets: 8192  Batches: 1  Memory Usage: 288kB
              ->  Seq Scan on vehicle v  (cost=0.00..104.00 rows=5000 width=13) (actual time=0.025..0.877 rows=5000 loops=1)
  ->  Hash Join  (cost=21500.33..40617.75 rows=271712 width=269) (actual time=558.212..10626.730 rows=271712 loops=1)
        Hash Cond: ((t_1.vehicle_id)::text = (v_1.vehicle_id)::text)
        ->  Hash Join  (cost=21333.83..38378.85 rows=271712 width=216) (actual time=556.482..10501.186 rows=271712 loops=1)
              Hash Cond: ((t_1.route_id)::text = (r_1.route_id)::text)
              ->  Hash Join  (cost=21286.22..37614.12 rows=271712 width=203) (actual time=556.125..10420.599 rows=271712 loops=1)
                    Hash Cond: (cs.shift_id = sh_1.shift_id)
                    ->  Hash Join  (cost=21266.99..36874.28 rows=271712 width=73) (actual time=556.099..10353.492 rows=271712 loops=1)
                          Hash Cond: ((c.employee_id)::text = (e_1.employee_id)::text)
                          ->  Hash Join  (cost=20163.49..35057.41 rows=271712 width=55) (actual time=543.852..10227.463 rows=271712 loops=1)
                                Hash Cond: ((cs.trip_id)::text = (t_1.trip_id)::text)
                                ->  Hash Join  (cost=299.00..6826.66 rows=271712 width=35) (actual time=333.030..9745.885 rows=271712 loops=1)
                                      Hash Cond: ((cs.conductor_id)::text = (c.conductor_id)::text)
                                      ->  Seq Scan on conductor_schedule cs  (cost=0.00..5814.12 rows=271712 width=36) (actual time=118.020..9414.529 rows=271712 loops=1)
                                      ->  Hash  (cost=174.00..174.00 rows=10000 width=13) (actual time=214.970..214.971 rows=10000 loops=1)
                                            Buckets: 16384  Batches: 1  Memory Usage: 582kB
                                            ->  Seq Scan on conductor c  (cost=0.00..174.00 rows=10000 width=13) (actual time=0.023..212.927 rows=10000 loops=1)
                                ->  Hash  (cost=11613.33..11613.33 rows=387533 width=41) (actual time=210.327..210.328 rows=387533 loops=1)
                                      Buckets: 131072  Batches: 8  Memory Usage: 4531kB
                                      ->  Seq Scan on trip t_1  (cost=0.00..11613.33 rows=387533 width=41) (actual time=0.013..70.554 rows=387533 loops=1)
                          ->  Hash  (cost=791.00..791.00 rows=25000 width=30) (actual time=12.196..12.196 rows=25000 loops=1)
                                Buckets: 32768  Batches: 1  Memory Usage: 1787kB
                                ->  Seq Scan on employee e_1  (cost=0.00..791.00 rows=25000 width=30) (actual time=0.011..5.545 rows=25000 loops=1)
                    ->  Hash  (cost=14.10..14.10 rows=410 width=138) (actual time=0.014..0.014 rows=3 loops=1)
                          Buckets: 1024  Batches: 1  Memory Usage: 9kB
                          ->  Seq Scan on shift sh_1  (cost=0.00..14.10 rows=410 width=138) (actual time=0.010..0.011 rows=3 loops=1)
              ->  Hash  (cost=37.83..37.83 rows=783 width=41) (actual time=0.335..0.336 rows=783 loops=1)
                    Buckets: 1024  Batches: 1  Memory Usage: 65kB
                    ->  Seq Scan on route r_1  (cost=0.00..37.83 rows=783 width=41) (actual time=0.026..0.162 rows=783 loops=1)
        ->  Hash  (cost=104.00..104.00 rows=5000 width=13) (actual time=1.703..1.704 rows=5000 loops=1)
              Buckets: 8192  Batches: 1  Memory Usage: 288kB
              ->  Seq Scan on vehicle v_1  (cost=0.00..104.00 rows=5000 width=13) (actual time=0.018..0.746 rows=5000 loops=1)
Planning Time: 543.865 ms
Execution Time: 22127.391 ms

По индексирање планот сеуште користи Seq Scan на DRIVER_SCHEDULE и CONDUCTOR_SCHEDULE бидејќи query-от враќа целата содржина на табелите — индексите помагаат при филтрирање по конкретен датум или вработен, не при SELECT *.

View3: Анализа на поглед - Нотификации по патник

Примарен филтер за погледот vw_passenger_notifications ќе биде според PASSENGER_ID и STATUS на нотификацијата, бидејќи view-от ги прикажува сите нотификации по патник.
Примарен случај на употреба е прикажување на нотификации во мобилна апликација за конкретен патник. Без индекс, секое повикување скенира ја целата NOTIFICATION табела.
Иницијалното време за извршување на погледот е над 30 минути (query прекинат). Ова е неприфатливо за мобилна апликација, па затоа пристапуваме кон индексирање на NOTIFICATION табелата по PASSENGER_ID и STATUS.
Набљудувани операции се full scan на NOTIFICATION табелата без филтрирање по PASSENGER_ID. Времето изминато во извршување на query-то со индекс изнесува 4 секунди при SELECT *.
Бидејќи view-от во реална апликација секогаш се повикува за конкретен патник, извршуваме дополнителна проверка со филтер по PASSENGER_ID:
```
   EXPLAIN ANALYZE
   SELECT * FROM vw_passenger_notifications
   WHERE passenger_id = '1000';
```

Планерот користи Index Scan на idx_notification_passenger_status наместо Seq Scan, со време на извршување од 0.060 ms — што е практично моментално и целосно прифатливо за production употреба.

Nested Loop  (cost=0.87..24.98 rows=3 width=121) (actual time=0.024..0.025 rows=0 loops=1)
  ->  Index Scan using pk_passenger on passenger p  (cost=0.43..8.45 rows=1 width=24) (actual time=0.023..0.024 rows=0 loops=1)
        Index Cond: ((passenger_id)::text = '1000'::text)
  ->  Index Scan using idx_notification_passenger_status on notification n  (cost=0.43..16.49 rows=3 width=89) (never executed)
        Index Cond: ((passenger_id)::text = '1000'::text)
Planning Time: 0.173 ms
Execution Time: 0.053 ms

Note: See TracWiki for help on using the wiki.

Download in other formats:

Plain Text