Changes between Version 47 and Version 48 of QueryOptimization
- Timestamp:
- 05/09/26 22:15:19 (3 weeks ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
QueryOptimization
v47 v48 58 58 }}} 59 59 60 ||= QUERY PLAN =|| 61 ||Insert on "Event_Happening_Performer" (cost\=0.00..0.01 rows\=0 width\=0) (actual time\=558.243..558.244 rows\=0 loops\=1)|| 62 || -> Result (cost\=0.00..0.01 rows\=1 width\=16) (actual time\=0.001..0.002 rows\=1 loops\=1)|| 63 ||Planning Time: 0.042 ms|| 64 ||Trigger for constraint fk_ehp_event_happening: time\=826.105 calls\=1|| 65 ||Trigger for constraint fk_ehp_performer: time\=40.075 calls\=1|| 66 ||Execution Time: 1424.451 ms|| 67 60 68 * '''UPDATE''' 61 69 … … 69 77 }}} 70 78 79 ||= QUERY PLAN =|| 80 ||Update on "Event_Happening_Performer" (cost\=0.29..8.31 rows\=0 width\=0) (actual time\=0.219..0.219 rows\=0 loops\=1)|| 81 || -> Index Scan using uq_performer_at_time on "Event_Happening_Performer" (cost\=0.29..8.31 rows\=1 width\=14) (actual time\=0.120..0.121 rows\=1 loops\=1)|| 82 || Index Cond: ((performer_id \= 10) AND (event_happening_id \= 1))|| 83 ||Planning Time: 0.139 ms|| 84 ||Trigger for constraint fk_ehp_performer: time\=0.279 calls\=1|| 85 ||Execution Time: 23.621 ms|| 86 71 87 Погледот е бавен поради комплексни врски помеѓи четири табели што резултира со време од 3.2s. Индексите на performer_id и event_id го елиминираат целосното скенирање на табелите и овозможуваат инстантно поврзување на изведувачите со нивните настани. 72 88 … … 95 111 }}} 96 112 113 ||= QUERY PLAN =|| 114 ||Nested Loop (cost\=1.14..77.38 rows\=7 width\=68) (actual time\=0.251..0.479 rows\=4 loops\=1)|| 115 || -> Nested Loop (cost\=0.86..74.92 rows\=7 width\=37) (actual time\=0.201..0.306 rows\=4 loops\=1)|| 116 || -> Index Scan using "Performer_pkey" on "Performer" p (cost\=0.28..8.30 rows\=1 width\=21) (actual time\=0.093..0.094 rows\=1 loops\=1)|| 117 || Index Cond: (performer_id \= 10)|| 118 || -> Nested Loop (cost\=0.57..66.55 rows\=7 width\=24) (actual time\=0.105..0.207 rows\=4 loops\=1)|| 119 || -> Index Only Scan using uq_performer_at_time on "Event_Happening_Performer" ehp (cost\=0.29..8.41 rows\=7 width\=16) (actual time\=0.062..0.065 rows\=4 loops\=1)|| 120 || Index Cond: (performer_id \= 10)|| 121 || Heap Fetches: 1|| 122 || -> Index Scan using "Event_Happening_pkey" on "Event_Happening" eh (cost\=0.29..8.30 rows\=1 width\=24) (actual time\=0.032..0.032 rows\=1 loops\=4)|| 123 || Index Cond: (event_happening_id \= ehp.event_happening_id)|| 124 || -> Index Scan using "Event_pkey" on "Event" e (cost\=0.29..0.35 rows\=1 width\=39) (actual time\=0.041..0.041 rows\=1 loops\=4)|| 125 || Index Cond: (event_id \= eh.event_id)|| 126 ||Planning Time: 1.600 ms|| 127 ||Execution Time: 0.533 ms|| 128 97 129 * '''INSERT''' 98 130 … … 104 136 105 137 }}} 138 139 ||= QUERY PLAN =|| 140 ||Insert on "Event_Happening_Performer" (cost\=0.00..0.01 rows\=0 width\=0) (actual time\=0.307..0.308 rows\=0 loops\=1)|| 141 || -> Result (cost\=0.00..0.01 rows\=1 width\=16) (actual time\=0.001..0.001 rows\=1 loops\=1)|| 142 ||Planning Time: 0.046 ms|| 143 ||Trigger for constraint fk_ehp_event_happening: time\=0.332 calls\=1|| 144 ||Trigger for constraint fk_ehp_performer: time\=0.169 calls\=1|| 145 ||Execution Time: 0.833 ms|| 106 146 107 147 * '''UPDATE''' … … 115 155 116 156 }}} 157 158 ||= QUERY PLAN =|| 159 ||Update on "Event_Happening_Performer" (cost\=0.29..8.31 rows\=0 width\=0) (actual time\=0.437..0.438 rows\=0 loops\=1)|| 160 || -> Index Scan using idx_ehp_happening_id on "Event_Happening_Performer" (cost\=0.29..8.31 rows\=1 width\=14) (actual time\=0.174..0.175 rows\=1 loops\=1)|| 161 || Index Cond: (event_happening_id \= 2)|| 162 || Filter: (performer_id \= 10)|| 163 || Rows Removed by Filter: 1|| 164 ||Planning Time: 0.161 ms|| 165 ||Trigger for constraint fk_ehp_performer: time\=0.271 calls\=1|| 166 ||Execution Time: 0.763 ms|| 117 167 118 168 == Анализа и оптимизација на `Venue_Layout`
