Changes between Version 56 and Version 57 of QueryOptimization


Ignore:
Timestamp:
05/09/26 22:39:33 (2 weeks ago)
Author:
231027
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • QueryOptimization

    v56 v57  
    11611161}}}
    11621162
     1163||= QUERY PLAN =||
     1164||Nested Loop  (cost\=0.86..26.26 rows\=1 width\=92) (actual time\=0.055..0.056 rows\=0 loops\=1)||
     1165||  ->  Nested Loop  (cost\=0.57..17.94 rows\=1 width\=63) (actual time\=0.054..0.055 rows\=0 loops\=1)||
     1166||        ->  Index Scan using uq_happening_time_venue on "Event_Happening" eh  (cost\=0.29..9.64 rows\=1 width\=32) (actual time\=0.054..0.054 rows\=0 loops\=1)||
     1167||              Index Cond: ((event_time > CURRENT_TIMESTAMP) AND (venue_id \= 1))||
     1168||        ->  Index Scan using "Event_pkey" on "Event" e  (cost\=0.29..8.30 rows\=1 width\=39) (never executed)||
     1169||              Index Cond: (event_id \= eh.event_id)||
     1170||  ->  Index Scan using "Venue_pkey" on "Venue" v  (cost\=0.29..8.30 rows\=1 width\=37) (never executed)||
     1171||        Index Cond: (venue_id \= 1)||
     1172||Planning Time: 4.258 ms||
     1173||Execution Time: 0.193 ms||
     1174
    11631175 * '''INSERT'''
    11641176
    … …  
    11701182
    11711183}}}
     1184
     1185||= QUERY PLAN =||
     1186||Insert on "Event_Happening"  (cost\=0.33..0.34 rows\=0 width\=0) (actual time\=572.079..572.083 rows\=0 loops\=1)||
     1187||  InitPlan 2||
     1188||    ->  Result  (cost\=0.31..0.33 rows\=1 width\=8) (actual time\=0.260..0.262 rows\=1 loops\=1)||
     1189||          InitPlan 1||
     1190||            ->  Limit  (cost\=0.29..0.31 rows\=1 width\=8) (actual time\=0.256..0.258 rows\=1 loops\=1)||
     1191||                  ->  Index Only Scan Backward using "Event_Happening_pkey" on "Event_Happening" "Event_Happening_1"  (cost\=0.29..822.34 rows\=31337 width\=8) (actual time\=0.255..0.256 rows\=1 loops\=1)||
     1192||                        Heap Fetches: 0||
     1193||  ->  Result  (cost\=0.00..0.01 rows\=1 width\=36) (actual time\=0.263..0.264 rows\=1 loops\=1)||
     1194||Planning Time: 0.199 ms||
     1195||Trigger for constraint fk_event_happening_event: time\=1.080 calls\=1||
     1196||Trigger for constraint fk_event_happening_venue: time\=0.448 calls\=1||
     1197||Execution Time: 573.660 ms||
    11721198
    11731199 * '''UPDATE'''
    … …  
    11821208}}}
    11831209
     1210||= QUERY PLAN =||
     1211||Update on "Event_Happening"  (cost\=0.61..8.63 rows\=0 width\=0) (actual time\=0.569..0.570 rows\=0 loops\=1)||
     1212||  InitPlan 2||
     1213||    ->  Result  (cost\=0.31..0.32 rows\=1 width\=8) (actual time\=0.292..0.293 rows\=1 loops\=1)||
     1214||          InitPlan 1||
     1215||            ->  Limit  (cost\=0.29..0.31 rows\=1 width\=8) (actual time\=0.288..0.288 rows\=1 loops\=1)||
     1216||                  ->  Index Only Scan Backward using "Event_Happening_pkey" on "Event_Happening" "Event_Happening_1"  (cost\=0.29..822.34 rows\=31337 width\=8) (actual time\=0.287..0.287 rows\=1 loops\=1)||
     1217||                        Heap Fetches: 1||
     1218||  ->  Index Scan using "Event_Happening_pkey" on "Event_Happening"  (cost\=0.29..8.30 rows\=1 width\=14) (actual time\=0.310..0.312 rows\=1 loops\=1)||
     1219||        Index Cond: (event_happening_id \= (InitPlan 2).col1)||
     1220||Planning Time: 0.240 ms||
     1221||Execution Time: 0.662 ms||
     1222
    11841223Времето на извршување е релативно ниско, но базата троши 4.258 ms само на планирање на секој поединечен запис. Бидејќи редовите не се подредени по време, системот мора да врши постојани споредби за секој настан. Заради ова, потребен е индекс кој ќе овозможи моментално лоцирање на идните настани без пребарување на целата табела.
    11851224
    … …  
    12111250}}}
    12121251
     1252||= QUERY PLAN =||
     1253||Nested Loop  (cost\=0.86..24.93 rows\=1 width\=92) (actual time\=0.153..0.157 rows\=1 loops\=1)||
     1254||  ->  Nested Loop  (cost\=0.57..16.61 rows\=1 width\=63) (actual time\=0.081..0.083 rows\=1 loops\=1)||
     1255||        ->  Index Scan using idx_event_happening_venue_time on "Event_Happening" eh  (cost\=0.29..8.31 rows\=1 width\=32) (actual time\=0.067..0.068 rows\=1 loops\=1)||
     1256||              Index Cond: ((venue_id \= 1) AND (event_time > CURRENT_TIMESTAMP))||
     1257||        ->  Index Scan using "Event_pkey" on "Event" e  (cost\=0.29..8.30 rows\=1 width\=39) (actual time\=0.009..0.009 rows\=1 loops\=1)||
     1258||              Index Cond: (event_id \= eh.event_id)||
     1259||  ->  Index Scan using "Venue_pkey" on "Venue" v  (cost\=0.29..8.30 rows\=1 width\=37) (actual time\=0.070..0.071 rows\=1 loops\=1)||
     1260||        Index Cond: (venue_id \= 1)||
     1261||Planning Time: 203.237 ms||
     1262||Execution Time: 0.215 ms||
     1263
    12131264 * '''INSERT'''
    12141265
    … …  
    12201271
    12211272}}}
     1273
     1274||= QUERY PLAN =||
     1275||Insert on "Event_Happening"  (cost\=0.33..0.34 rows\=0 width\=0) (actual time\=0.726..0.728 rows\=0 loops\=1)||
     1276||  InitPlan 2||
     1277||    ->  Result  (cost\=0.31..0.33 rows\=1 width\=8) (actual time\=0.281..0.282 rows\=1 loops\=1)||
     1278||          InitPlan 1||
     1279||            ->  Limit  (cost\=0.29..0.31 rows\=1 width\=8) (actual time\=0.278..0.279 rows\=1 loops\=1)||
     1280||                  ->  Index Only Scan Backward using "Event_Happening_pkey" on "Event_Happening" "Event_Happening_1"  (cost\=0.29..826.36 rows\=31338 width\=8) (actual time\=0.276..0.277 rows\=1 loops\=1)||
     1281||                        Heap Fetches: 1||
     1282||  ->  Result  (cost\=0.00..0.01 rows\=1 width\=36) (actual time\=0.284..0.284 rows\=1 loops\=1)||
     1283||Planning Time: 0.235 ms||
     1284||Trigger for constraint fk_event_happening_event: time\=0.252 calls\=1||
     1285||Trigger for constraint fk_event_happening_venue: time\=0.255 calls\=1||
     1286||Execution Time: 1.278 ms||
    12221287
    12231288 * '''UPDATE'''
    … …  
    12311296
    12321297}}}
     1298
     1299||= QUERY PLAN =||
     1300||Update on "Event_Happening"  (cost\=0.61..8.63 rows\=0 width\=0) (actual time\=0.543..0.544 rows\=0 loops\=1)||
     1301||  InitPlan 2||
     1302||    ->  Result  (cost\=0.31..0.32 rows\=1 width\=8) (actual time\=0.169..0.170 rows\=1 loops\=1)||
     1303||          InitPlan 1||
     1304||            ->  Limit  (cost\=0.29..0.31 rows\=1 width\=8) (actual time\=0.166..0.166 rows\=1 loops\=1)||
     1305||                  ->  Index Only Scan Backward using "Event_Happening_pkey" on "Event_Happening" "Event_Happening_1"  (cost\=0.29..826.36 rows\=31338 width\=8) (actual time\=0.165..0.165 rows\=1 loops\=1)||
     1306||                        Heap Fetches: 1||
     1307||  ->  Index Scan using "Event_Happening_pkey" on "Event_Happening"  (cost\=0.29..8.30 rows\=1 width\=14) (actual time\=0.183..0.185 rows\=1 loops\=1)||
     1308||        Index Cond: (event_happening_id \= (InitPlan 2).col1)||
     1309||Planning Time: 0.283 ms||
     1310||Execution Time: 0.616 ms||
    12331311
    12341312== Анализа и оптимизација на `Available_Tickets`