Changes between Version 54 and Version 55 of QueryOptimization


Ignore:
Timestamp:
05/09/26 22:34:02 (5 weeks ago)
Author:
231027
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • QueryOptimization

    v54 v55  
    755755}}}
    756756
     757||= QUERY PLAN =||
     758||GroupAggregate  (cost\=72.25..72.32 rows\=3 width\=95) (actual time\=1029.642..1029.647 rows\=1 loops\=1)||
     759||  Group Key: eh.event_happening_id||
     760||  ->  Sort  (cost\=72.25..72.26 rows\=3 width\=67) (actual time\=1029.557..1029.604 rows\=1 loops\=1)||
     761||        Sort Key: eh.event_happening_id||
     762||        Sort Method: quicksort  Memory: 25kB||
     763||        ->  Nested Loop  (cost\=4.90..72.22 rows\=3 width\=67) (actual time\=223.616..1029.548 rows\=1 loops\=1)||
     764||              ->  Index Scan using "Event_pkey" on "Event" e  (cost\=0.29..8.30 rows\=1 width\=39) (actual time\=0.046..0.053 rows\=1 loops\=1)||
     765||                    Index Cond: (event_id \= 1)||
     766||              ->  Nested Loop  (cost\=4.61..63.89 rows\=3 width\=36) (actual time\=223.566..1029.488 rows\=1 loops\=1)||
     767||                    ->  Bitmap Heap Scan on "Event_Happening" eh  (cost\=4.33..22.32 rows\=5 width\=24) (actual time\=0.054..805.890 rows\=5 loops\=1)||
     768||                          Recheck Cond: (event_id \= 1)||
     769||                          Heap Blocks: exact\=5||
     770||                          ->  Bitmap Index Scan on idx_event_happening_event_id  (cost\=0.00..4.32 rows\=5 width\=0) (actual time\=0.029..0.029 rows\=5 loops\=1)||
     771||                                Index Cond: (event_id \= 1)||
     772||                    ->  Index Scan using uq_rating_happening_user on "Event_Happening_Rating" ehr  (cost\=0.29..8.30 rows\=1 width\=20) (actual time\=44.710..44.711 rows\=0 loops\=5)||
     773||                          Index Cond: (event_happening_id \= eh.event_happening_id)||
     774||Planning Time: 1.025 ms||
     775||Execution Time: 1029.756 ms||
     776
    757777 * '''INSERT'''
    758778
    … …  
    766786}}}
    767787
     788||= QUERY PLAN =||
     789||Insert on "Event_Happening_Rating"  (cost\=0.31..0.34 rows\=0 width\=0) (actual time\=0.405..0.407 rows\=0 loops\=1)||
     790||  ->  Subquery Scan on "*SELECT*"  (cost\=0.31..0.34 rows\=1 width\=60) (actual time\=0.156..0.158 rows\=1 loops\=1)||
     791||        ->  Result  (cost\=0.31..0.33 rows\=1 width\=52) (actual time\=0.154..0.155 rows\=1 loops\=1)||
     792||              InitPlan 1||
     793||                ->  Limit  (cost\=0.29..0.31 rows\=1 width\=8) (actual time\=0.149..0.149 rows\=1 loops\=1)||
     794||                      ->  Index Only Scan Backward using "Event_Happening_Rating_pkey" on "Event_Happening_Rating" "Event_Happening_Rating_1"  (cost\=0.29..484.37 rows\=17872 width\=8) (actual time\=0.148..0.148 rows\=1 loops\=1)||
     795||                            Heap Fetches: 2||
     796||Planning Time: 0.199 ms||
     797||Trigger for constraint fk_event_happening_rating_event_happening: time\=0.232 calls\=1||
     798||Trigger for constraint fk_event_happening_rating_user: time\=0.234 calls\=1||
     799||Execution Time: 0.918 ms||
     800
    768801 * '''UPDATE'''
    769802
    … …  
    777810}}}
    778811
     812||= QUERY PLAN =||
     813||Update on "Event_Happening_Rating"  (cost\=0.29..8.31 rows\=0 width\=0) (actual time\=0.458..0.459 rows\=0 loops\=1)||
     814||  ->  Index Scan using uq_rating_happening_user on "Event_Happening_Rating"  (cost\=0.29..8.31 rows\=1 width\=42) (actual time\=0.131..0.133 rows\=1 loops\=1)||
     815||        Index Cond: ((event_happening_id \= 1) AND (user_id \= 15))||
     816||Planning Time: 0.136 ms||
     817||Execution Time: 0.500 ms||
     818
    779819Пресметката на просечни оценки бара постојано агрегирање на податоци, што е бавно при секој нов приказ. Композитен индекс на (event_happening_id, rating) овозможува математичките операции да се вршат директно врз индексот, забрзувајќи го приказот на почетната страна.
    780820
    … …  
    798838
    799839}}}
     840
     841||= QUERY PLAN =||
     842||GroupAggregate  (cost\=72.25..72.32 rows\=3 width\=95) (actual time\=0.387..0.389 rows\=1 loops\=1)||
     843||  Group Key: eh.event_happening_id||
     844||  ->  Sort  (cost\=72.25..72.26 rows\=3 width\=67) (actual time\=0.374..0.375 rows\=1 loops\=1)||
     845||        Sort Key: eh.event_happening_id||
     846||        Sort Method: quicksort  Memory: 25kB||
     847||        ->  Nested Loop  (cost\=4.90..72.22 rows\=3 width\=67) (actual time\=0.271..0.367 rows\=1 loops\=1)||
     848||              ->  Index Scan using "Event_pkey" on "Event" e  (cost\=0.29..8.30 rows\=1 width\=39) (actual time\=0.126..0.128 rows\=1 loops\=1)||
     849||                    Index Cond: (event_id \= 1)||
     850||              ->  Nested Loop  (cost\=4.61..63.89 rows\=3 width\=36) (actual time\=0.142..0.235 rows\=1 loops\=1)||
     851||                    ->  Bitmap Heap Scan on "Event_Happening" eh  (cost\=4.33..22.32 rows\=5 width\=24) (actual time\=0.067..0.144 rows\=5 loops\=1)||
     852||                          Recheck Cond: (event_id \= 1)||
     853||                          Heap Blocks: exact\=5||
     854||                          ->  Bitmap Index Scan on idx_event_happening_event_id  (cost\=0.00..4.32 rows\=5 width\=0) (actual time\=0.038..0.038 rows\=5 loops\=1)||
     855||                                Index Cond: (event_id \= 1)||
     856||                    ->  Index Scan using uq_rating_happening_user on "Event_Happening_Rating" ehr  (cost\=0.29..8.30 rows\=1 width\=20) (actual time\=0.016..0.016 rows\=0 loops\=5)||
     857||                          Index Cond: (event_happening_id \= eh.event_happening_id)||
     858||Planning Time: 1.134 ms||
     859||Execution Time: 0.459 ms||
    800860
    801861 * '''INSERT'''
    … …  
    810870}}}
    811871
     872||= QUERY PLAN =||
     873||Insert on "Event_Happening_Rating"  (cost\=0.31..0.34 rows\=0 width\=0) (actual time\=0.538..0.539 rows\=0 loops\=1)||
     874||  ->  Subquery Scan on "*SELECT*"  (cost\=0.31..0.34 rows\=1 width\=60) (actual time\=0.111..0.112 rows\=1 loops\=1)||
     875||        ->  Result  (cost\=0.31..0.33 rows\=1 width\=52) (actual time\=0.109..0.109 rows\=1 loops\=1)||
     876||              InitPlan 1||
     877||                ->  Limit  (cost\=0.29..0.31 rows\=1 width\=8) (actual time\=0.104..0.105 rows\=1 loops\=1)||
     878||                      ->  Index Only Scan Backward using "Event_Happening_Rating_pkey" on "Event_Happening_Rating" "Event_Happening_Rating_1"  (cost\=0.29..485.40 rows\=17874 width\=8) (actual time\=0.102..0.103 rows\=1 loops\=1)||
     879||                            Heap Fetches: 1||
     880||Planning Time: 0.195 ms||
     881||Trigger for constraint fk_event_happening_rating_event_happening: time\=0.223 calls\=1||
     882||Trigger for constraint fk_event_happening_rating_user: time\=0.172 calls\=1||
     883||Execution Time: 0.980 ms||
     884
    812885 * '''UPDATE'''
    813886
    … …  
    820893
    821894}}}
     895
     896||= QUERY PLAN =||
     897||Update on "Event_Happening_Rating"  (cost\=0.29..8.31 rows\=0 width\=0) (actual time\=0.482..0.483 rows\=0 loops\=1)||
     898||  ->  Index Scan using uq_rating_happening_user on "Event_Happening_Rating"  (cost\=0.29..8.31 rows\=1 width\=42) (actual time\=0.096..0.098 rows\=1 loops\=1)||
     899||        Index Cond: ((event_happening_id \= 1) AND (user_id \= 20))||
     900||Planning Time: 0.163 ms||
     901||Execution Time: 0.548 ms||
    822902
    823903== Анализа и оптимизација на `Event_Financial_Summary`