Changes between Version 4 and Version 5 of QueryOptimization


Ignore:
Timestamp:
05/20/26 17:40:21 (5 days ago)
Author:
231151
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • QueryOptimization

    v4 v5  
    153153
    154154
    155 
     155== View4: Анализа на поглед - **Постојки по линија**
     156
     1571. Примарен филтер за погледот vw_route_timetable ќе биде според ROUTE_ID, TRIP_ID и STOP_SEQUENCE, бидејќи view-от ги прикажува сите постојки по линија сортирани по редослед.
     1582. Примарен случај на употреба е прикажување на возен ред за конкретна линија. View-от содржи ORDER BY преку повеќе табели и JOIN на STOP_TIME која е најголемата табела во системот.
     1593. Иницијалното време за извршување на погледот е над 30 минути (query прекинато). Ова е сосема неприфатливо, па затоа пристапуваме кон индексирање на STOP_TIME табелата по TRIP_ID и STOP_SEQUENCE.
     1604. Набљудувани операции се full scan на STOP_TIME без индекс, со скапи Sort операции при ORDER BY. Времето изминато во извршување на query-то со индекс изнесува 55 секунди при SELECT *.
     1615. Бидејќи view-от во реална апликација секогаш се повикува за конкретна линија, извршуваме дополнителна проверка со филтер по ROUTE_ID:
     162{{{
     163   EXPLAIN ANALYZE
     164   SELECT * FROM vw_route_timetable
     165   WHERE route_id= '14-200-aus-1';
     166}}}
     1676. Планерот користи 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 филтерот.
     168{{{
     169Nested Loop  (cost=351844.40..357291.23 rows=41130 width=354) (actual time=1701.703..1761.489 rows=52574 loops=1)
     170  ->  Gather Merge  (cost=351844.12..356768.81 rows=41130 width=328) (actual time=1701.571..1741.830 rows=52574 loops=1)
     171        Workers Planned: 4
     172        Workers Launched: 4
     173        ->  Sort  (cost=350844.06..350869.77 rows=10282 width=328) (actual time=1636.076..1636.764 rows=10515 loops=5)
     174              Sort Key: t.trip_id, st.stop_sequence
     175              Sort Method: quicksort  Memory: 2708kB
     176              Worker 0:  Sort Method: quicksort  Memory: 25kB
     177              Worker 1:  Sort Method: quicksort  Memory: 1905kB
     178              Worker 2:  Sort Method: quicksort  Memory: 2728kB
     179              Worker 3:  Sort Method: quicksort  Memory: 1816kB
     180              ->  Hash Left Join  (cost=10661.45..350158.88 rows=10282 width=328) (actual time=1006.772..1629.749 rows=10515 loops=5)
     181                    Hash Cond: ((s.zone_id)::text = (z.zone_id)::text)
     182                    ->  Hash Join  (cost=10646.95..350116.82 rows=10282 width=114) (actual time=1006.669..1626.898 rows=10515 loops=5)
     183                          Hash Cond: ((st.stop_id)::text = (s.stop_id)::text)
     184                          ->  Parallel Hash Join  (cost=9761.99..349077.74 rows=10259 width=69) (actual time=268.500..884.784 rows=10515 loops=5)
     185                                Hash Cond: ((st.trip_id)::text = (t.trip_id)::text)
     186                                ->  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)
     187                                ->  Parallel Hash  (cost=9756.40..9756.40 rows=447 width=44) (actual time=21.035..21.036 rows=178 loops=5)
     188                                      Buckets: 2048  Batches: 1  Memory Usage: 176kB
     189                                      ->  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)
     190                                            Filter: ((route_id)::text = '14-200-aus-1'::text)
     191                                            Rows Removed by Filter: 77328
     192                          ->  Hash  (cost=553.87..553.87 rows=26487 width=50) (actual time=737.833..737.834 rows=26487 loops=5)
     193                                Buckets: 32768  Batches: 1  Memory Usage: 2458kB
     194                                ->  Seq Scan on stops s  (cost=0.00..553.87 rows=26487 width=50) (actual time=156.397..726.901 rows=26487 loops=5)
     195                    ->  Hash  (cost=12.00..12.00 rows=200 width=336) (actual time=0.088..0.089 rows=20 loops=4)
     196                          Buckets: 1024  Batches: 1  Memory Usage: 9kB
     197                          ->  Seq Scan on zone z  (cost=0.00..12.00 rows=200 width=336) (actual time=0.053..0.058 rows=20 loops=4)
     198  ->  Materialize  (cost=0.28..8.30 rows=1 width=41) (actual time=0.000..0.000 rows=1 loops=52574)
     199        ->  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)
     200              Index Cond: ((route_id)::text = '14-200-aus-1'::text)
     201Planning Time: 2.499 ms
     202JIT:
     203  Functions: 151
     204  Options: Inlining false, Optimization false, Expressions true, Deforming true
     205  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
     206Execution Time: 1766.202 ms
     207}}}