Changes between Version 57 and Version 58 of QueryOptimization


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

--

Legend:

Unmodified
Added
Removed
Modified

  • QueryOptimization

    v57 v58  
    13481348}}}
    13491349
     1350||= QUERY PLAN =||
     1351||Merge Join  (cost\=27256.32..27455.75 rows\=811 width\=85) (actual time\=299.567..948.130 rows\=347 loops\=1)||
     1352||  Merge Cond: (st.seat_id \= t.seat_id)||
     1353||  ->  Nested Loop  (cost\=0.86..2908890.51 rows\=20753360 width\=19) (actual time\=19.526..667.820 rows\=1163 loops\=1)||
     1354||        ->  Index Scan using "Seat_pkey" on "Seat" st  (cost\=0.56..2383051.22 rows\=20753360 width\=20) (actual time\=0.716..648.333 rows\=1163 loops\=1)||
     1355||        ->  Memoize  (cost\=0.30..0.32 rows\=1 width\=15) (actual time\=0.016..0.016 rows\=1 loops\=1163)||
     1356||              Cache Key: st.section_id||
     1357||              Cache Mode: logical||
     1358||              Hits: 1156  Misses: 7  Evictions: 0  Overflows: 0  Memory Usage: 1kB||
     1359||              ->  Index Scan using "Section_pkey" on "Section" s  (cost\=0.29..0.31 rows\=1 width\=15) (actual time\=2.600..2.600 rows\=1 loops\=7)||
     1360||                    Index Cond: (section_id \= st.section_id)||
     1361||  ->  Sort  (cost\=27253.12..27255.15 rows\=811 width\=82) (actual time\=280.018..280.056 rows\=347 loops\=1)||
     1362||        Sort Key: t.seat_id||
     1363||        Sort Method: quicksort  Memory: 62kB||
     1364||        ->  Nested Loop  (cost\=5.05..27213.93 rows\=811 width\=82) (actual time\=0.827..279.855 rows\=347 loops\=1)||
     1365||              ->  Nested Loop  (cost\=4.61..30.67 rows\=5 width\=55) (actual time\=0.243..0.408 rows\=7 loops\=1)||
     1366||                    ->  Index Scan using "Event_pkey" on "Event" e  (cost\=0.29..8.30 rows\=1 width\=39) (actual time\=0.141..0.153 rows\=1 loops\=1)||
     1367||                          Index Cond: (event_id \= 1)||
     1368||                    ->  Bitmap Heap Scan on "Event_Happening" eh  (cost\=4.33..22.32 rows\=5 width\=24) (actual time\=0.095..0.239 rows\=7 loops\=1)||
     1369||                          Recheck Cond: (event_id \= 1)||
     1370||                          Heap Blocks: exact\=6||
     1371||                          ->  Bitmap Index Scan on idx_event_happening_event_id  (cost\=0.00..4.32 rows\=5 width\=0) (actual time\=0.066..0.066 rows\=11 loops\=1)||
     1372||                                Index Cond: (event_id \= 1)||
     1373||              ->  Index Scan using idx_ticket_event_happening_id on "Ticket" t  (cost\=0.44..5433.49 rows\=316 width\=35) (actual time\=39.884..39.910 rows\=50 loops\=7)||
     1374||                    Index Cond: (event_happening_id \= eh.event_happening_id)||
     1375||                    Filter: is_available||
     1376||                    Rows Removed by Filter: 568||
     1377||Planning Time: 3.990 ms||
     1378||Execution Time: 948.253 ms||
     1379
    13501380 * '''INSERT'''
    13511381
    … …  
    13571387
    13581388}}}
     1389
     1390||= QUERY PLAN =||
     1391||Insert on "Ticket"  (cost\=0.61..0.62 rows\=0 width\=0) (actual time\=0.429..0.430 rows\=0 loops\=1)||
     1392||  InitPlan 2||
     1393||    ->  Result  (cost\=0.59..0.61 rows\=1 width\=8) (actual time\=0.215..0.216 rows\=1 loops\=1)||
     1394||          InitPlan 1||
     1395||            ->  Limit  (cost\=0.56..0.59 rows\=1 width\=8) (actual time\=0.212..0.213 rows\=1 loops\=1)||
     1396||                  ->  Index Only Scan Backward using "Ticket_pkey" on "Ticket" "Ticket_1"  (cost\=0.56..892367.26 rows\=28748980 width\=8) (actual time\=0.211..0.212 rows\=1 loops\=1)||
     1397||                        Heap Fetches: 1||
     1398||  ->  Result  (cost\=0.00..0.01 rows\=1 width\=147) (actual time\=0.218..0.218 rows\=1 loops\=1)||
     1399||Planning Time: 0.211 ms||
     1400||Trigger for constraint fk_ticket_event_happening: time\=0.325 calls\=1||
     1401||Trigger for constraint fk_ticket_seat: time\=0.243 calls\=1||
     1402||Execution Time: 1.045 ms||
    13591403
    13601404 * '''UPDATE'''
    … …  
    13691413}}}
    13701414
     1415||= QUERY PLAN =||
     1416||Update on "Ticket"  (cost\=1.17..9.18 rows\=0 width\=0) (actual time\=0.340..0.341 rows\=0 loops\=1)||
     1417||  InitPlan 2||
     1418||    ->  Result  (cost\=0.59..0.60 rows\=1 width\=8) (actual time\=0.270..0.271 rows\=1 loops\=1)||
     1419||          InitPlan 1||
     1420||            ->  Limit  (cost\=0.56..0.59 rows\=1 width\=8) (actual time\=0.267..0.268 rows\=1 loops\=1)||
     1421||                  ->  Index Only Scan Backward using "Ticket_pkey" on "Ticket" "Ticket_1"  (cost\=0.56..892367.26 rows\=28748980 width\=8) (actual time\=0.266..0.266 rows\=1 loops\=1)||
     1422||                        Heap Fetches: 1||
     1423||  ->  Index Scan using "Ticket_pkey" on "Ticket"  (cost\=0.56..8.58 rows\=1 width\=10) (actual time\=0.291..0.292 rows\=1 loops\=1)||
     1424||        Index Cond: (ticket_id \= (InitPlan 2).col1)||
     1425||Planning Time: 0.244 ms||
     1426||Execution Time: 0.393 ms||
     1427
    13711428За овој поглед се трошат 948.13 ms бидејќи базата мора да пребарува низ 30 милиони записи. Овој процес вклучува скапи операции со трошок од 27455.75, каде се читаат милиони непотребни записи од дискот. Заради ова, потребен е индекс кој ќе ги издвои само достапните билети и ќе го елиминира ваквото чекање.
    13721429
    … …  
    13971454
    13981455}}}
     1456
     1457||= QUERY PLAN =||
     1458||Merge Join  (cost\=26670.90..26870.11 rows\=793 width\=85) (actual time\=2.496..3.734 rows\=347 loops\=1)||
     1459||  Merge Cond: (st.seat_id \= t.seat_id)||
     1460||  ->  Nested Loop  (cost\=0.86..2908890.51 rows\=20753360 width\=19) (actual time\=0.270..1.289 rows\=1163 loops\=1)||
     1461||        ->  Index Scan using "Seat_pkey" on "Seat" st  (cost\=0.56..2383051.22 rows\=20753360 width\=20) (actual time\=0.049..0.516 rows\=1163 loops\=1)||
     1462||        ->  Memoize  (cost\=0.30..0.32 rows\=1 width\=15) (actual time\=0.000..0.000 rows\=1 loops\=1163)||
     1463||              Cache Key: st.section_id||
     1464||              Cache Mode: logical||
     1465||              Hits: 1156  Misses: 7  Evictions: 0  Overflows: 0  Memory Usage: 1kB||
     1466||              ->  Index Scan using "Section_pkey" on "Section" s  (cost\=0.29..0.31 rows\=1 width\=15) (actual time\=0.009..0.009 rows\=1 loops\=7)||
     1467||                    Index Cond: (section_id \= st.section_id)||
     1468||  ->  Sort  (cost\=26667.76..26669.74 rows\=793 width\=82) (actual time\=2.213..2.236 rows\=347 loops\=1)||
     1469||        Sort Key: t.seat_id||
     1470||        Sort Method: quicksort  Memory: 62kB||
     1471||        ->  Nested Loop  (cost\=5.05..26629.57 rows\=793 width\=82) (actual time\=0.566..2.095 rows\=347 loops\=1)||
     1472||              ->  Nested Loop  (cost\=4.61..30.67 rows\=5 width\=55) (actual time\=0.153..0.260 rows\=7 loops\=1)||
     1473||                    ->  Index Scan using "Event_pkey" on "Event" e  (cost\=0.29..8.30 rows\=1 width\=39) (actual time\=0.077..0.083 rows\=1 loops\=1)||
     1474||                          Index Cond: (event_id \= 1)||
     1475||                    ->  Bitmap Heap Scan on "Event_Happening" eh  (cost\=4.33..22.32 rows\=5 width\=24) (actual time\=0.072..0.168 rows\=7 loops\=1)||
     1476||                          Recheck Cond: (event_id \= 1)||
     1477||                          Heap Blocks: exact\=6||
     1478||                          ->  Bitmap Index Scan on idx_event_happening_event_id  (cost\=0.00..4.32 rows\=5 width\=0) (actual time\=0.047..0.047 rows\=11 loops\=1)||
     1479||                                Index Cond: (event_id \= 1)||
     1480||              ->  Index Scan using idx_ticket_event_happening_id on "Ticket" t  (cost\=0.44..5316.69 rows\=309 width\=35) (actual time\=0.234..0.253 rows\=50 loops\=7)||
     1481||                    Index Cond: (event_happening_id \= eh.event_happening_id)||
     1482||                    Filter: is_available||
     1483||                    Rows Removed by Filter: 568||
     1484||Planning Time: 4.840 ms||
     1485||Execution Time: 3.882 ms||
    13991486
    14001487 * '''INSERT'''
    … …  
    14081495}}}
    14091496
     1497||= QUERY PLAN =||
     1498||Insert on "Ticket"  (cost\=0.61..0.62 rows\=0 width\=0) (actual time\=0.545..0.546 rows\=0 loops\=1)||
     1499||  InitPlan 2||
     1500||    ->  Result  (cost\=0.59..0.61 rows\=1 width\=8) (actual time\=0.092..0.093 rows\=1 loops\=1)||
     1501||          InitPlan 1||
     1502||            ->  Limit  (cost\=0.56..0.59 rows\=1 width\=8) (actual time\=0.089..0.090 rows\=1 loops\=1)||
     1503||                  ->  Index Only Scan Backward using "Ticket_pkey" on "Ticket" "Ticket_1"  (cost\=0.56..882762.56 rows\=28108400 width\=8) (actual time\=0.088..0.089 rows\=1 loops\=1)||
     1504||                        Heap Fetches: 1||
     1505||  ->  Result  (cost\=0.00..0.01 rows\=1 width\=147) (actual time\=0.095..0.095 rows\=1 loops\=1)||
     1506||Planning Time: 0.250 ms||
     1507||Trigger for constraint fk_ticket_event_happening: time\=0.473 calls\=1||
     1508||Trigger for constraint fk_ticket_seat: time\=207.063 calls\=1||
     1509||Execution Time: 208.133 ms||
     1510
    14101511 * '''UPDATE'''
    14111512
    … …  
    14181519
    14191520}}}
     1521
     1522||= QUERY PLAN =||
     1523||Update on "Ticket"  (cost\=1.17..9.18 rows\=0 width\=0) (actual time\=0.168..0.169 rows\=0 loops\=1)||
     1524||  InitPlan 2||
     1525||    ->  Result  (cost\=0.59..0.60 rows\=1 width\=8) (actual time\=0.033..0.034 rows\=1 loops\=1)||
     1526||          InitPlan 1||
     1527||            ->  Limit  (cost\=0.56..0.59 rows\=1 width\=8) (actual time\=0.031..0.031 rows\=1 loops\=1)||
     1528||                  ->  Index Only Scan Backward using "Ticket_pkey" on "Ticket" "Ticket_1"  (cost\=0.56..882762.56 rows\=28108400 width\=8) (actual time\=0.030..0.030 rows\=1 loops\=1)||
     1529||                        Heap Fetches: 1||
     1530||  ->  Index Scan using "Ticket_pkey" on "Ticket"  (cost\=0.56..8.58 rows\=1 width\=10) (actual time\=0.045..0.046 rows\=1 loops\=1)||
     1531||        Index Cond: (ticket_id \= (InitPlan 2).col1)||
     1532||Planning Time: 0.242 ms||
     1533||Execution Time: 0.397 ms||