Changes between Version 53 and Version 54 of QueryOptimization

Timestamp:

05/09/26 22:30:49 (2 weeks ago)

Author:

231027

Comment:

--

QueryOptimization

@@ -582 +582 @@
 }}}
 
+||= QUERY PLAN =||
+||Nested Loop  (cost\=1.29..21.42 rows\=1 width\=131) (actual time\=0.104..0.105 rows\=0 loops\=1)||
+||  ->  Nested Loop  (cost\=0.86..12.96 rows\=1 width\=113) (actual time\=0.103..0.105 rows\=0 loops\=1)||
+||        ->  Nested Loop  (cost\=0.57..12.61 rows\=1 width\=82) (actual time\=0.103..0.104 rows\=0 loops\=1)||
+||              ->  Index Scan using idx_ehr_user_id on "Event_Happening_Rating" ehr  (cost\=0.29..4.30 rows\=1 width\=74) (actual time\=0.103..0.103 rows\=0 loops\=1)||
+||                    Index Cond: (user_id \= 1)||
+||              ->  Index Scan using "Event_Happening_pkey" on "Event_Happening" eh  (cost\=0.29..8.30 rows\=1 width\=16) (never executed)||
+||                    Index Cond: (event_happening_id \= ehr.event_happening_id)||
+||        ->  Index Scan using "Event_pkey" on "Event" e  (cost\=0.29..0.35 rows\=1 width\=39) (never executed)||
+||              Index Cond: (event_id \= eh.event_id)||
+||  ->  Index Scan using "User_pkey" on "User" u  (cost\=0.43..8.45 rows\=1 width\=26) (never executed)||
+||        Index Cond: (user_id \= 1)||
+||Planning Time: 464.594 ms||
+||Execution Time: 0.187 ms||
+
  * '''INSERT'''
 
@@ -593 +608 @@
 }}}
 
+||= QUERY PLAN =||
+||Insert on "Event_Happening_Rating"  (cost\=0.31..0.34 rows\=0 width\=0) (actual time\=0.521..0.522 rows\=0 loops\=1)||
+||  ->  Subquery Scan on "*SELECT*"  (cost\=0.31..0.34 rows\=1 width\=60) (actual time\=0.122..0.123 rows\=1 loops\=1)||
+||        ->  Result  (cost\=0.31..0.33 rows\=1 width\=52) (actual time\=0.119..0.120 rows\=1 loops\=1)||
+||              InitPlan 1||
+||                ->  Limit  (cost\=0.29..0.31 rows\=1 width\=8) (actual time\=0.114..0.115 rows\=1 loops\=1)||
+||                      ->  Index Only Scan Backward using "Event_Happening_Rating_pkey" on "Event_Happening_Rating" "Event_Happening_Rating_1"  (cost\=0.29..478.37 rows\=17872 width\=8) (actual time\=0.113..0.113 rows\=1 loops\=1)||
+||                            Heap Fetches: 6||
+||Planning Time: 0.297 ms||
+||Trigger for constraint fk_event_happening_rating_event_happening: time\=0.490 calls\=1||
+||Trigger for constraint fk_event_happening_rating_user: time\=0.370 calls\=1||
+||Execution Time: 1.444 ms||
+
  * '''UPDATE'''
 
@@ -604 +632 @@
 }}}
 
+||= QUERY PLAN =||
+||Update on "Event_Happening_Rating"  (cost\=0.29..4.31 rows\=0 width\=0) (actual time\=0.176..0.177 rows\=0 loops\=1)||
+||  ->  Index Scan using idx_ehr_user_id on "Event_Happening_Rating"  (cost\=0.29..4.31 rows\=1 width\=42) (actual time\=0.039..0.042 rows\=1 loops\=1)||
+||        Index Cond: (user_id \= 1)||
+||        Filter: (event_happening_id \= 1)||
+||Planning Time: 0.153 ms||
+||Execution Time: 0.218 ms||
+
 Без индекси, секое пребарување на оценките по корисник предизвикува непотребно оптоварување на меморијата преку Seq Scan. Индексирањето на user_id и event_happening_id обезбедува брза филтрација и поврзување на рејтинзите со соодветните термини на настаните.
 
@@ -628 +664 @@
 
 }}}
+
+||= QUERY PLAN =||
+||Nested Loop  (cost\=1.00..476.51 rows\=1 width\=131) (actual time\=359.514..359.524 rows\=1 loops\=1)||
+||  ->  Nested Loop  (cost\=0.57..468.06 rows\=1 width\=113) (actual time\=359.389..359.396 rows\=1 loops\=1)||
+||        ->  Nested Loop  (cost\=0.29..467.71 rows\=1 width\=82) (actual time\=2.104..2.110 rows\=1 loops\=1)||
+||              ->  Seq Scan on "Event_Happening_Rating" ehr  (cost\=0.00..459.40 rows\=1 width\=74) (actual time\=2.038..2.041 rows\=1 loops\=1)||
+||                    Filter: (user_id \= 1)||
+||                    Rows Removed by Filter: 17871||
+||              ->  Index Scan using "Event_Happening_pkey" on "Event_Happening" eh  (cost\=0.29..8.30 rows\=1 width\=16) (actual time\=0.061..0.062 rows\=1 loops\=1)||
+||                    Index Cond: (event_happening_id \= ehr.event_happening_id)||
+||        ->  Index Scan using "Event_pkey" on "Event" e  (cost\=0.29..0.35 rows\=1 width\=39) (actual time\=357.279..357.280 rows\=1 loops\=1)||
+||              Index Cond: (event_id \= eh.event_id)||
+||  ->  Index Scan using "User_pkey" on "User" u  (cost\=0.43..8.45 rows\=1 width\=26) (actual time\=0.120..0.121 rows\=1 loops\=1)||
+||        Index Cond: (user_id \= 1)||
+||Planning Time: 31.049 ms||
+||Execution Time: 359.600 ms||
 
  * '''INSERT'''
@@ -640 +692 @@
 }}}
 
+||= QUERY PLAN =||
+||Insert on "Event_Happening_Rating"  (cost\=0.31..0.34 rows\=0 width\=0) (actual time\=0.482..0.483 rows\=0 loops\=1)||
+||  ->  Subquery Scan on "*SELECT*"  (cost\=0.31..0.34 rows\=1 width\=60) (actual time\=0.117..0.119 rows\=1 loops\=1)||
+||        ->  Result  (cost\=0.31..0.33 rows\=1 width\=52) (actual time\=0.115..0.115 rows\=1 loops\=1)||
+||              InitPlan 1||
+||                ->  Limit  (cost\=0.29..0.31 rows\=1 width\=8) (actual time\=0.110..0.111 rows\=1 loops\=1)||
+||                      ->  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.108..0.108 rows\=1 loops\=1)||
+||                            Heap Fetches: 4||
+||Planning Time: 0.203 ms||
+||Trigger for constraint fk_event_happening_rating_event_happening: time\=0.197 calls\=1||
+||Trigger for constraint fk_event_happening_rating_user: time\=0.114 calls\=1||
+||Execution Time: 0.842 ms||
+
  * '''UPDATE'''
 
@@ -650 +715 @@
 
 }}}
+
+||= QUERY PLAN =||
+||Update on "Event_Happening_Rating"  (cost\=0.29..8.31 rows\=0 width\=0) (actual time\=0.021..0.022 rows\=0 loops\=1)||
+||  ->  Index Scan using uq_rating_happening_user on "Event_Happening_Rating"  (cost\=0.29..8.31 rows\=1 width\=42) (actual time\=0.020..0.021 rows\=0 loops\=1)||
+||        Index Cond: ((event_happening_id \= 1) AND (user_id \= 2))||
+||Planning Time: 0.132 ms||
+||Execution Time: 0.081 ms||
 
 == Анализа и оптимизација на `Event_Overall_Ratings`