Changes between Version 6 and Version 7 of QueryOptimization


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

--

Legend:

Unmodified
Added
Removed
Modified

  • QueryOptimization

    v6 v7  
    260260}}}
    2612616. По индексирање планерот сеуште користи Seq Scan на TICKET и PASSENGER бидејќи query-от враќа целата содржина на табелите — со 10 милиони билети и 5 милиони патници, индексот помага при филтрирање по конкретен патник, не при SELECT *. За апликациска употреба се препорачува повикување со филтер по PASSENGER_ID каде индексот idx_ticket_passenger_id го намалува времето на извршување на под 1 ms.
     262
     263== View6: Анализа на поглед - **Моментална позиција на возило**
     264
     2651. Примарен филтер за погледот vw_vehicle_current_location ќе биде според VEHICLE_ID и RECORDED_AT (DESC), бидејќи view-от користи DISTINCT ON за да ја врати само последната локација по возило.
     2662. Примарен случај на употреба е real-time мапа на возила. DISTINCT ON без индекс бара целосно скенирање и сортирање на VEHICLE_LOG табелата која расте континуирано.
     2673. Иницијалното време за извршување на погледот е над 30 минути (query прекинато). Ова е критично неприфатливо за real-time приказ, па затоа пристапуваме кон индексирање на VEHICLE_LOG по VEHICLE_ID и RECORDED_AT DESC.
     2684. Набљудувани операции се Parallel Seq Scan на VEHICLE_LOG (5,466,813 редови), external merge Sort на диск (околу 1GB spread низ 5 workers) и Subquery Scan со филтер по registration_number по веќе вратените резултати.
     2695. Времето изминато во извршување на query-то со индекс изнесува 42 секунди при SELECT *. Бидејќи view-от во реална апликација се повикува за конкретно возило, извршуваме дополнителна проверка со филтер по REGISTRATION_NUMBER:
     270{{{
     271EXPLAIN ANALYZE
     272SELECT * FROM vw_vehicle_current_location
     273where registration_number = 'KM779MN';
     274}}}
     2756. Планерот го применува филтерот дури по целосното извршување на DISTINCT ON — односно прво ги обработува сите 5,466,813 редови, потоа ги филтрира на 1 резултат, со вкупно 11 секунди. Ова е бидејќи registration_number не е дел од VEHICLE_LOG туку доаѓа од JOIN со VEHICLE, па индексот не може да го примени рано. За оптимална апликациска употреба се препорачува филтрирање по VEHICLE_ID наместо по registration_number, каде индексот idx_vehicle_log_vehicle_recorded го намалува времето драстично.
     276{{{
     277Subquery 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)
     278  Filter: ((vw_vehicle_current_location.registration_number)::text = 'KM779MN'::text)
     279  Rows Removed by Filter: 4999
     280  ->  Unique  (cost=549951.26..1308451.70 rows=4999 width=304) (actual time=5547.810..10955.987 rows=5000 loops=1)
     281        ->  Merge Join  (cost=549951.26..1294881.33 rows=5428149 width=304) (actual time=5547.808..10518.551 rows=5466813 loops=1)
     282              Merge Cond: ((vl.vehicle_id)::text = (v.vehicle_id)::text)
     283              ->  Gather Merge  (cost=549506.92..1199444.38 rows=5428149 width=168) (actual time=5534.883..8500.321 rows=5466813 loops=1)
     284                    Workers Planned: 4
     285                    Workers Launched: 4
     286                    ->  Sort  (cost=548506.86..551899.45 rows=1357037 width=168) (actual time=5485.885..6124.527 rows=1093363 loops=5)
     287                          Sort Key: vl.vehicle_id, vl.recorded_at DESC
     288                          Sort Method: external merge  Disk: 200024kB
     289                          Worker 0:  Sort Method: external merge  Disk: 199576kB
     290                          Worker 1:  Sort Method: external merge  Disk: 197768kB
     291                          Worker 2:  Sort Method: external merge  Disk: 200704kB
     292                          Worker 3:  Sort Method: external merge  Disk: 200784kB
     293                          ->  Hash Left Join  (cost=13188.65..187636.87 rows=1357037 width=168) (actual time=611.558..2046.207 rows=1093363 loops=5)
     294                                Hash Cond: ((vl.next_stop_id)::text = (ns.stop_id)::text)
     295                                ->  Hash Left Join  (cost=12303.70..167150.46 rows=1357037 width=150) (actual time=600.399..1736.136 rows=1089270 loops=5)
     296                                      Hash Cond: ((vl.current_stop_id)::text = (cs.stop_id)::text)
     297                                      ->  Hash Join  (cost=11418.74..147477.88 rows=1357037 width=131) (actual time=588.724..1429.761 rows=1085630 loops=5)
     298                                            Hash Cond: ((t.route_id)::text = (r.route_id)::text)
     299                                            ->  Parallel Hash Join  (cost=11371.12..143848.76 rows=1357037 width=118) (actual time=66.011..656.170 rows=1085630 loops=5)
     300                                                  Hash Cond: ((vl.trip_id)::text = (t.trip_id)::text)
     301                                                  ->  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)
     302                                                  ->  Parallel Hash  (cost=9352.72..9352.72 rows=161472 width=36) (actual time=64.801..64.802 rows=77507 loops=5)
     303                                                        Buckets: 524288  Batches: 1  Memory Usage: 31520kB
     304                                                        ->  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)
     305                                            ->  Hash  (cost=37.83..37.83 rows=783 width=41) (actual time=522.584..522.585 rows=783 loops=5)
     306                                                  Buckets: 1024  Batches: 1  Memory Usage: 65kB
     307                                                  ->  Seq Scan on route r  (cost=0.00..37.83 rows=783 width=41) (actual time=522.217..522.374 rows=783 loops=5)
     308                                      ->  Hash  (cost=553.87..553.87 rows=26487 width=30) (actual time=11.577..11.578 rows=26487 loops=5)
     309                                            Buckets: 32768  Batches: 1  Memory Usage: 1889kB
     310                                            ->  Seq Scan on stops cs  (cost=0.00..553.87 rows=26487 width=30) (actual time=0.055..4.678 rows=26487 loops=5)
     311                                ->  Hash  (cost=553.87..553.87 rows=26487 width=30) (actual time=10.892..10.893 rows=26487 loops=5)
     312                                      Buckets: 32768  Batches: 1  Memory Usage: 1889kB
     313                                      ->  Seq Scan on stops ns  (cost=0.00..553.87 rows=26487 width=30) (actual time=0.022..3.091 rows=26487 loops=5)
     314              ->  Sort  (cost=444.34..456.84 rows=5000 width=141) (actual time=12.854..318.819 rows=5465700 loops=1)
     315                    Sort Key: v.vehicle_id
     316                    Sort Method: quicksort  Memory: 435kB
     317                    ->  Hash Left Join  (cost=19.90..137.15 rows=5000 width=141) (actual time=0.116..1.899 rows=5000 loops=1)
     318                          Hash Cond: (v.vehicle_type_id = vt.vehicle_type_id)
     319                          ->  Seq Scan on vehicle v  (cost=0.00..104.00 rows=5000 width=27) (actual time=0.032..0.619 rows=5000 loops=1)
     320                          ->  Hash  (cost=14.40..14.40 rows=440 width=122) (actual time=0.047..0.049 rows=3 loops=1)
     321                                Buckets: 1024  Batches: 1  Memory Usage: 9kB
     322                                ->  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)
     323Planning Time: 2.924 ms
     324JIT:
     325  Functions: 194
     326  Options: Inlining true, Optimization true, Expressions true, Deforming true
     327  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
     328Execution Time: 11010.528 ms
     329}}}