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
View4: Анализа на поглед - Постојки по линија
- Примарен филтер за погледот vw_route_timetable ќе биде според ROUTE_ID, TRIP_ID и STOP_SEQUENCE, бидејќи view-от ги прикажува сите постојки по линија сортирани по редослед.
- Примарен случај на употреба е прикажување на возен ред за конкретна линија. View-от содржи ORDER BY преку повеќе табели и JOIN на STOP_TIME која е најголемата табела во системот.
- Иницијалното време за извршување на погледот е над 30 минути (query прекинато). Ова е сосема неприфатливо, па затоа пристапуваме кон индексирање на STOP_TIME табелата по TRIP_ID и STOP_SEQUENCE.
- Набљудувани операции се full scan на STOP_TIME без индекс, со скапи Sort операции при ORDER BY. Времето изминато во извршување на query-то со индекс изнесува 55 секунди при SELECT *.
- Бидејќи view-от во реална апликација секогаш се повикува за конкретна линија, извршуваме дополнителна проверка со филтер по ROUTE_ID:
EXPLAIN ANALYZE SELECT * FROM vw_route_timetable WHERE route_id= '14-200-aus-1';
- Планерот користи 4 паралелни workers и Index Scan на pk_route за директно наоѓање на линијата, со вкупно време на извршување од 1.766 секунди за 52,574 редови. Паралелното извршување значително го намалува времето наспроти последователното скенирање. Сепак, планерот сеуште користи Parallel Seq Scan на STOP_TIME (2,966,852 редови по worker) бидејќи query-от враќа голем дел од табелата — за целосна оптимизација би се разгледало додавање индекс на STOP_TIME(TRIP_ID) поврзан со ROUTE_ID филтерот.
Nested Loop (cost=351844.40..357291.23 rows=41130 width=354) (actual time=1701.703..1761.489 rows=52574 loops=1) -> Gather Merge (cost=351844.12..356768.81 rows=41130 width=328) (actual time=1701.571..1741.830 rows=52574 loops=1) Workers Planned: 4 Workers Launched: 4 -> Sort (cost=350844.06..350869.77 rows=10282 width=328) (actual time=1636.076..1636.764 rows=10515 loops=5) Sort Key: t.trip_id, st.stop_sequence Sort Method: quicksort Memory: 2708kB Worker 0: Sort Method: quicksort Memory: 25kB Worker 1: Sort Method: quicksort Memory: 1905kB Worker 2: Sort Method: quicksort Memory: 2728kB Worker 3: Sort Method: quicksort Memory: 1816kB -> Hash Left Join (cost=10661.45..350158.88 rows=10282 width=328) (actual time=1006.772..1629.749 rows=10515 loops=5) Hash Cond: ((s.zone_id)::text = (z.zone_id)::text) -> Hash Join (cost=10646.95..350116.82 rows=10282 width=114) (actual time=1006.669..1626.898 rows=10515 loops=5) Hash Cond: ((st.stop_id)::text = (s.stop_id)::text) -> Parallel Hash Join (cost=9761.99..349077.74 rows=10259 width=69) (actual time=268.500..884.784 rows=10515 loops=5) Hash Cond: ((st.trip_id)::text = (t.trip_id)::text) -> Parallel Seq Scan on stop_time st (cost=0.00..329580.66 rows=3708566 width=46) (actual time=0.070..439.414 rows=2966852 loops=5) -> Parallel Hash (cost=9756.40..9756.40 rows=447 width=44) (actual time=21.035..21.036 rows=178 loops=5) Buckets: 2048 Batches: 1 Memory Usage: 176kB -> Parallel Seq Scan on trip t (cost=0.00..9756.40 rows=447 width=44) (actual time=0.060..20.613 rows=178 loops=5) Filter: ((route_id)::text = '14-200-aus-1'::text) Rows Removed by Filter: 77328 -> Hash (cost=553.87..553.87 rows=26487 width=50) (actual time=737.833..737.834 rows=26487 loops=5) Buckets: 32768 Batches: 1 Memory Usage: 2458kB -> Seq Scan on stops s (cost=0.00..553.87 rows=26487 width=50) (actual time=156.397..726.901 rows=26487 loops=5) -> Hash (cost=12.00..12.00 rows=200 width=336) (actual time=0.088..0.089 rows=20 loops=4) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Seq Scan on zone z (cost=0.00..12.00 rows=200 width=336) (actual time=0.053..0.058 rows=20 loops=4) -> Materialize (cost=0.28..8.30 rows=1 width=41) (actual time=0.000..0.000 rows=1 loops=52574) -> Index Scan using pk_route on route r (cost=0.28..8.29 rows=1 width=41) (actual time=0.113..0.116 rows=1 loops=1) Index Cond: ((route_id)::text = '14-200-aus-1'::text) Planning Time: 2.499 ms JIT: Functions: 151 Options: Inlining false, Optimization false, Expressions true, Deforming true Timing: Generation 11.588 ms (Deform 6.605 ms), Inlining 0.000 ms, Optimization 5.391 ms, Emission 736.476 ms, Total 753.456 ms Execution Time: 1766.202 ms
View5: Анализа на поглед - Детали по тикет
- Примарен филтер за погледот vw_ticket_details ќе биде според PASSENGER_ID и комбинацијата STOP_ID + VEHICLE_TYPE_ID за почетна и крајна постојка, бидејќи view-от ги прикажува деталите на секој билет со полни информации.
- Примарен случај на употреба е преглед на историја на билети по патник. View-от содржи повеќе LEFT JOIN-ови на STOPS табелата со составен услов, што без индекс предизвикува скапи nested loop скенирања.
- Иницијалното време за извршување на погледот е над 30 минути (query прекинато). Ова е неприфатливо, па затоа пристапуваме кон индексирање на TICKET по PASSENGER_ID и на STOPS по STOP_ID + VEHICLE_TYPE_ID.
- Набљудувани операции се Seq Scan на TICKET (10,000,000 редови), Seq Scan на PASSENGER (5,000,000 редови) и двоен Seq Scan на STOPS за почетна и крајна постојка, со повеќе Hash Join операции.
- Времето изминато во извршување на query-то со индекс изнесува 49.5 секунди за 10,000,000 редови.
Hash Left Join (cost=272402.37..1376871.00 rows=10000000 width=482) (actual time=3538.270..49122.132 rows=10000000 loops=1) Hash Cond: (((t.end_stop_id)::text = (s2.stop_id)::text) AND (t.end_stop_vt = s2.vehicle_type_id)) -> Hash Left Join (cost=271451.19..1273417.38 rows=10000000 width=450) (actual time=3525.176..44813.338 rows=10000000 loops=1) Hash Cond: (((t.start_stop_id)::text = (s1.stop_id)::text) AND (t.start_stop_vt = s1.vehicle_type_id)) -> Hash Left Join (cost=270500.02..1219963.74 rows=10000000 width=436) (actual time=3511.604..41479.190 rows=10000000 loops=1) Hash Cond: ((tr.route_id)::text = (r.route_id)::text) -> Hash Left Join (cost=270452.40..1193524.05 rows=10000000 width=423) (actual time=3510.999..38605.858 rows=10000000 loops=1) Hash Cond: (t.discount_id = d.discount_id) -> Hash Join (cost=270437.00..1167143.44 rows=10000000 width=197) (actual time=3510.885..37057.803 rows=10000000 loops=1) Hash Cond: (t.ticket_type_id = tt.ticket_type_id) -> Hash Join (cost=270418.00..1140596.31 rows=10000000 width=83) (actual time=2961.969..34707.444 rows=10000000 loops=1) Hash Cond: ((t.passenger_id)::text = (p.passenger_id)::text) -> Hash Left Join (cost=19485.49..580210.78 rows=10000000 width=79) (actual time=213.191..14106.212 rows=10000000 loops=1) Hash Cond: ((t.trip_id)::text = (tr.trip_id)::text) -> Seq Scan on ticket t (cost=0.00..258009.00 rows=10000000 width=85) (actual time=0.028..2521.552 rows=10000000 loops=1) -> Hash (cost=11613.33..11613.33 rows=387533 width=36) (actual time=212.763..212.765 rows=387533 loops=1) Buckets: 131072 Batches: 4 Memory Usage: 7477kB -> Seq Scan on trip tr (cost=0.00..11613.33 rows=387533 width=36) (actual time=0.023..81.617 rows=387533 loops=1) -> Hash (cost=159141.67..159141.67 rows=4999667 width=24) (actual time=2744.675..2744.676 rows=5000000 loops=1) Buckets: 131072 Batches: 64 Memory Usage: 5354kB -> Seq Scan on passenger p (cost=0.00..159141.67 rows=4999667 width=24) (actual time=0.093..1020.752 rows=5000000 loops=1) -> Hash (cost=14.00..14.00 rows=400 width=122) (actual time=548.879..548.880 rows=6 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Seq Scan on ticket_type tt (cost=0.00..14.00 rows=400 width=122) (actual time=548.842..548.847 rows=6 loops=1) -> Hash (cost=12.40..12.40 rows=240 width=234) (actual time=0.053..0.054 rows=10 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Seq Scan on discount d (cost=0.00..12.40 rows=240 width=234) (actual time=0.036..0.038 rows=10 loops=1) -> Hash (cost=37.83..37.83 rows=783 width=41) (actual time=0.538..0.539 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.107..0.269 rows=783 loops=1) -> Hash (cost=553.87..553.87 rows=26487 width=34) (actual time=13.291..13.292 rows=26487 loops=1) Buckets: 32768 Batches: 1 Memory Usage: 2032kB -> Seq Scan on stops s1 (cost=0.00..553.87 rows=26487 width=34) (actual time=0.022..4.334 rows=26487 loops=1) -> Hash (cost=553.87..553.87 rows=26487 width=34) (actual time=12.783..12.784 rows=26487 loops=1) Buckets: 32768 Batches: 1 Memory Usage: 2032kB -> Seq Scan on stops s2 (cost=0.00..553.87 rows=26487 width=34) (actual time=0.020..3.732 rows=26487 loops=1) Planning Time: 6.020 ms JIT: Functions: 62 Options: Inlining true, Optimization true, Expressions true, Deforming true Timing: Generation 3.237 ms (Deform 1.411 ms), Inlining 15.323 ms, Optimization 328.820 ms, Emission 205.012 ms, Total 552.392 ms Execution Time: 49521.706 ms - По индексирање планерот сеуште користи Seq Scan на TICKET и PASSENGER бидејќи query-от враќа целата содржина на табелите — со 10 милиони билети и 5 милиони патници, индексот помага при филтрирање по конкретен патник, не при SELECT *. За апликациска употреба се препорачува повикување со филтер по PASSENGER_ID каде индексот idx_ticket_passenger_id го намалува времето на извршување на под 1 ms.
View6: Анализа на поглед - Моментална позиција на возило
- Примарен филтер за погледот vw_vehicle_current_location ќе биде според VEHICLE_ID и RECORDED_AT (DESC), бидејќи view-от користи DISTINCT ON за да ја врати само последната локација по возило.
- Примарен случај на употреба е real-time мапа на возила. DISTINCT ON без индекс бара целосно скенирање и сортирање на VEHICLE_LOG табелата која расте континуирано.
- Иницијалното време за извршување на погледот е над 30 минути (query прекинато). Ова е критично неприфатливо за real-time приказ, па затоа пристапуваме кон индексирање на VEHICLE_LOG по VEHICLE_ID и RECORDED_AT DESC.
- Набљудувани операции се Parallel Seq Scan на VEHICLE_LOG (5,466,813 редови), external merge Sort на диск (околу 1GB spread низ 5 workers) и Subquery Scan со филтер по registration_number по веќе вратените резултати.
- Времето изминато во извршување на query-то со индекс изнесува 42 секунди при SELECT *. Бидејќи view-от во реална апликација се повикува за конкретно возило, извршуваме дополнителна проверка со филтер по REGISTRATION_NUMBER:
EXPLAIN ANALYZE SELECT * FROM vw_vehicle_current_location where registration_number = 'KM779MN';
- Планерот го применува филтерот дури по целосното извршување на DISTINCT ON — односно прво ги обработува сите 5,466,813 редови, потоа ги филтрира на 1 резултат, со вкупно 11 секунди. Ова е бидејќи registration_number не е дел од VEHICLE_LOG туку доаѓа од JOIN со VEHICLE, па индексот не може да го примени рано. За оптимална апликациска употреба се препорачува филтрирање по VEHICLE_ID наместо по registration_number, каде индексот idx_vehicle_log_vehicle_recorded го намалува времето драстично.
Subquery Scan on vw_vehicle_current_location (cost=549951.26..1308514.19 rows=25 width=304) (actual time=5553.031..10957.527 rows=1 loops=1) Filter: ((vw_vehicle_current_location.registration_number)::text = 'KM779MN'::text) Rows Removed by Filter: 4999 -> Unique (cost=549951.26..1308451.70 rows=4999 width=304) (actual time=5547.810..10955.987 rows=5000 loops=1) -> Merge Join (cost=549951.26..1294881.33 rows=5428149 width=304) (actual time=5547.808..10518.551 rows=5466813 loops=1) Merge Cond: ((vl.vehicle_id)::text = (v.vehicle_id)::text) -> Gather Merge (cost=549506.92..1199444.38 rows=5428149 width=168) (actual time=5534.883..8500.321 rows=5466813 loops=1) Workers Planned: 4 Workers Launched: 4 -> Sort (cost=548506.86..551899.45 rows=1357037 width=168) (actual time=5485.885..6124.527 rows=1093363 loops=5) Sort Key: vl.vehicle_id, vl.recorded_at DESC Sort Method: external merge Disk: 200024kB Worker 0: Sort Method: external merge Disk: 199576kB Worker 1: Sort Method: external merge Disk: 197768kB Worker 2: Sort Method: external merge Disk: 200704kB Worker 3: Sort Method: external merge Disk: 200784kB -> Hash Left Join (cost=13188.65..187636.87 rows=1357037 width=168) (actual time=611.558..2046.207 rows=1093363 loops=5) Hash Cond: ((vl.next_stop_id)::text = (ns.stop_id)::text) -> Hash Left Join (cost=12303.70..167150.46 rows=1357037 width=150) (actual time=600.399..1736.136 rows=1089270 loops=5) Hash Cond: ((vl.current_stop_id)::text = (cs.stop_id)::text) -> Hash Join (cost=11418.74..147477.88 rows=1357037 width=131) (actual time=588.724..1429.761 rows=1085630 loops=5) Hash Cond: ((t.route_id)::text = (r.route_id)::text) -> Parallel Hash Join (cost=11371.12..143848.76 rows=1357037 width=118) (actual time=66.011..656.170 rows=1085630 loops=5) Hash Cond: ((vl.trip_id)::text = (t.trip_id)::text) -> Parallel Seq Scan on vehicle_log vl (cost=0.00..128915.37 rows=1357037 width=103) (actual time=0.077..191.084 rows=1085630 loops=5) -> Parallel Hash (cost=9352.72..9352.72 rows=161472 width=36) (actual time=64.801..64.802 rows=77507 loops=5) Buckets: 524288 Batches: 1 Memory Usage: 31520kB -> Parallel Seq Scan on trip t (cost=0.00..9352.72 rows=161472 width=36) (actual time=0.045..25.916 rows=96883 loops=4) -> Hash (cost=37.83..37.83 rows=783 width=41) (actual time=522.584..522.585 rows=783 loops=5) Buckets: 1024 Batches: 1 Memory Usage: 65kB -> Seq Scan on route r (cost=0.00..37.83 rows=783 width=41) (actual time=522.217..522.374 rows=783 loops=5) -> Hash (cost=553.87..553.87 rows=26487 width=30) (actual time=11.577..11.578 rows=26487 loops=5) Buckets: 32768 Batches: 1 Memory Usage: 1889kB -> Seq Scan on stops cs (cost=0.00..553.87 rows=26487 width=30) (actual time=0.055..4.678 rows=26487 loops=5) -> Hash (cost=553.87..553.87 rows=26487 width=30) (actual time=10.892..10.893 rows=26487 loops=5) Buckets: 32768 Batches: 1 Memory Usage: 1889kB -> Seq Scan on stops ns (cost=0.00..553.87 rows=26487 width=30) (actual time=0.022..3.091 rows=26487 loops=5) -> Sort (cost=444.34..456.84 rows=5000 width=141) (actual time=12.854..318.819 rows=5465700 loops=1) Sort Key: v.vehicle_id Sort Method: quicksort Memory: 435kB -> Hash Left Join (cost=19.90..137.15 rows=5000 width=141) (actual time=0.116..1.899 rows=5000 loops=1) Hash Cond: (v.vehicle_type_id = vt.vehicle_type_id) -> Seq Scan on vehicle v (cost=0.00..104.00 rows=5000 width=27) (actual time=0.032..0.619 rows=5000 loops=1) -> Hash (cost=14.40..14.40 rows=440 width=122) (actual time=0.047..0.049 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.034..0.035 rows=3 loops=1) Planning Time: 2.924 ms JIT: Functions: 194 Options: Inlining true, Optimization true, Expressions true, Deforming true Timing: Generation 13.610 ms (Deform 7.431 ms), Inlining 481.656 ms, Optimization 1298.695 ms, Emission 830.943 ms, Total 2624.905 ms Execution Time: 11010.528 ms
Last modified
5 days ago
Last modified on 05/20/26 17:58:54
Note:
See TracWiki
for help on using the wiki.
