Changes between Version 55 and Version 56 of QueryOptimization


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

--

Legend:

Unmodified
Added
Removed
Modified

  • QueryOptimization

    v55 v56  
    941941}}}
    942942
     943||= QUERY PLAN =||
     944||Finalize GroupAggregate  (cost\=300491.02..300511.03 rows\=5 width\=71) (actual time\=319308.288..319318.376 rows\=1 loops\=1)||
     945||  Group Key: eh.event_happening_id||
     946||  ->  Gather Merge  (cost\=300491.02..300510.84 rows\=10 width\=75) (actual time\=319308.204..319318.298 rows\=1 loops\=1)||
     947||        Workers Planned: 2||
     948||        Workers Launched: 2||
     949||        ->  Partial GroupAggregate  (cost\=299491.00..299509.67 rows\=5 width\=75) (actual time\=319135.885..319135.894 rows\=0 loops\=3)||
     950||              Group Key: eh.event_happening_id||
     951||              ->  Sort  (cost\=299491.00..299493.66 rows\=1064 width\=79) (actual time\=319135.736..319135.767 rows\=388 loops\=3)||
     952||                    Sort Key: eh.event_happening_id||
     953||                    Sort Method: quicksort  Memory: 25kB||
     954||                    Worker 0:  Sort Method: quicksort  Memory: 25kB||
     955||                    Worker 1:  Sort Method: quicksort  Memory: 160kB||
     956||                    ->  Nested Loop Left Join  (cost\=17569.44..299437.50 rows\=1064 width\=79) (actual time\=257215.363..319135.341 rows\=388 loops\=3)||
     957||                          ->  Parallel Hash Join  (cost\=17569.00..291013.94 rows\=1064 width\=67) (actual time\=257187.356..319052.018 rows\=388 loops\=3)||
     958||                                Hash Cond: (tp.ticket_id \= t.ticket_id)||
     959||                                ->  Parallel Seq Scan on "Ticket_Purchase" tp  (cost\=0.00..248438.67 rows\=6666668 width\=20) (actual time\=39.675..315154.157 rows\=5333334 loops\=3)||
     960||                                ->  Parallel Hash  (cost\=17536.03..17536.03 rows\=2638 width\=63) (actual time\=2787.389..2787.393 rows\=1442 loops\=3)||
     961||                                      Buckets: 8192  Batches: 1  Memory Usage: 576kB||
     962||                                      ->  Nested Loop  (cost\=1.14..17536.03 rows\=2638 width\=63) (actual time\=1483.997..2465.151 rows\=1442 loops\=3)||
     963||                                            ->  Nested Loop  (cost\=0.57..1029.33 rows\=3 width\=55) (actual time\=632.603..753.210 rows\=2 loops\=3)||
     964||                                                  ->  Parallel Index Scan using "Event_Happening_pkey" on "Event_Happening" eh  (cost\=0.29..1004.39 rows\=3 width\=24) (actual time\=632.410..752.992 rows\=2 loops\=3)||
     965||                                                        Filter: (event_id \= 1)||
     966||                                                        Rows Removed by Filter: 10444||
     967||                                                  ->  Index Scan using "Event_pkey" on "Event" e  (cost\=0.29..8.30 rows\=1 width\=39) (actual time\=0.094..0.100 rows\=1 loops\=5)||
     968||                                                        Index Cond: (event_id \= 1)||
     969||                                            ->  Index Scan using uq_ticket_event_happening_seat on "Ticket" t  (cost\=0.56..5486.81 rows\=1542 width\=16) (actual time\=652.636..1027.008 rows\=865 loops\=5)||
     970||                                                  Index Cond: (event_happening_id \= eh.event_happening_id)||
     971||                          ->  Index Scan using idx_ticket_refund_purchase_id on "Ticket_Refund" tr  (cost\=0.43..7.92 rows\=1 width\=20) (actual time\=0.214..0.214 rows\=0 loops\=1163)||
     972||                                Index Cond: (purchase_id \= tp.purchase_id)||
     973||Planning Time: 2770.897 ms||
     974||JIT:||
     975||  Functions: 96||
     976||  Options: Inlining false, Optimization false, Expressions true, Deforming true||
     977||  Timing: Generation 9.017 ms (Deform 4.245 ms), Inlining 0.000 ms, Optimization 4.730 ms, Emission 79.393 ms, Total 93.140 ms||
     978||Execution Time: 319320.792 ms||
     979
    943980 * '''INSERT'''
    944981
    … …  
    952989}}}
    953990
     991||= QUERY PLAN =||
     992||Insert on "Ticket_Purchase"  (cost\=0.70..0.73 rows\=0 width\=0) (actual time\=1391.459..1391.461 rows\=0 loops\=1)||
     993||  ->  Subquery Scan on "*SELECT*"  (cost\=0.70..0.73 rows\=1 width\=552) (actual time\=0.132..0.137 rows\=1 loops\=1)||
     994||        ->  Result  (cost\=0.70..0.71 rows\=1 width\=88) (actual time\=0.106..0.109 rows\=1 loops\=1)||
     995||              InitPlan 1||
     996||                ->  Limit  (cost\=0.43..0.70 rows\=1 width\=8) (actual time\=0.101..0.102 rows\=1 loops\=1)||
     997||                      ->  Index Only Scan Backward using "Ticket_Purchase_pkey" on "Ticket_Purchase" "Ticket_Purchase_1"  (cost\=0.43..4162966.51 rows\=16000002 width\=8) (actual time\=0.099..0.099 rows\=1 loops\=1)||
     998||                            Heap Fetches: 1||
     999||Planning Time: 0.232 ms||
     1000||Trigger for constraint fk_purchase_ticket: time\=1.278 calls\=1||
     1001||Trigger for constraint fk_purchase_user: time\=0.358 calls\=1||
     1002||Execution Time: 1393.154 ms||
     1003
    9541004 * '''UPDATE'''
    9551005
    … …  
    9631013}}}
    9641014
     1015||= QUERY PLAN =||
     1016||Update on "Ticket_Purchase"  (cost\=0.56..8.58 rows\=0 width\=0) (actual time\=0.266..0.267 rows\=0 loops\=1)||
     1017||  ->  Index Scan using "Ticket_Purchase_qr_code_key" on "Ticket_Purchase"  (cost\=0.56..8.58 rows\=1 width\=10) (actual time\=0.117..0.119 rows\=1 loops\=1)||
     1018||        Index Cond: ((qr_code)::text \= 'QR-TEST-CODE-003'::text)||
     1019||Planning Time: 0.188 ms||
     1020||Execution Time: 0.364 ms||
     1021
    9651022Овој поглед има критично време на извршување од над 5 минути поради обработка на милиони трансакции. Индексите на ticket_id го намалуваат времето за 99%, овозможувајќи моментален преглед на приходите и рефундациите за секој настан.
    9661023
    … …  
    9901047
    9911048}}}
     1049
     1050||= QUERY PLAN =||
     1051||Finalize GroupAggregate  (cost\=298955.06..298975.08 rows\=5 width\=71) (actual time\=1499.323..1508.016 rows\=1 loops\=1)||
     1052||  Group Key: eh.event_happening_id||
     1053||  ->  Gather Merge  (cost\=298955.06..298974.89 rows\=10 width\=75) (actual time\=1499.269..1507.962 rows\=1 loops\=1)||
     1054||        Workers Planned: 2||
     1055||        Workers Launched: 2||
     1056||        ->  Partial GroupAggregate  (cost\=297955.04..297973.71 rows\=5 width\=75) (actual time\=1463.528..1463.535 rows\=0 loops\=3)||
     1057||              Group Key: eh.event_happening_id||
     1058||              ->  Sort  (cost\=297955.04..297957.70 rows\=1064 width\=79) (actual time\=1463.377..1463.407 rows\=388 loops\=3)||
     1059||                    Sort Key: eh.event_happening_id||
     1060||                    Sort Method: quicksort  Memory: 25kB||
     1061||                    Worker 0:  Sort Method: quicksort  Memory: 25kB||
     1062||                    Worker 1:  Sort Method: quicksort  Memory: 160kB||
     1063||                    ->  Nested Loop Left Join  (cost\=16033.48..297901.55 rows\=1064 width\=79) (actual time\=1201.975..1463.060 rows\=388 loops\=3)||
     1064||                          ->  Parallel Hash Join  (cost\=16033.05..289477.99 rows\=1064 width\=67) (actual time\=1201.913..1460.100 rows\=388 loops\=3)||
     1065||                                Hash Cond: (tp.ticket_id \= t.ticket_id)||
     1066||                                ->  Parallel Seq Scan on "Ticket_Purchase" tp  (cost\=0.00..248438.67 rows\=6666668 width\=20) (actual time\=0.091..663.287 rows\=5333334 loops\=3)||
     1067||                                ->  Parallel Hash  (cost\=16000.08..16000.08 rows\=2638 width\=63) (actual time\=13.806..13.809 rows\=1442 loops\=3)||
     1068||                                      Buckets: 8192  Batches: 1  Memory Usage: 512kB||
     1069||                                      ->  Nested Loop  (cost\=1.01..16000.08 rows\=2638 width\=63) (actual time\=30.720..39.526 rows\=4325 loops\=1)||
     1070||                                            ->  Nested Loop  (cost\=0.57..1029.33 rows\=3 width\=55) (actual time\=30.641..37.392 rows\=5 loops\=1)||
     1071||                                                  ->  Parallel Index Scan using "Event_Happening_pkey" on "Event_Happening" eh  (cost\=0.29..1004.39 rows\=3 width\=24) (actual time\=30.536..37.242 rows\=5 loops\=1)||
     1072||                                                        Filter: (event_id \= 1)||
     1073||                                                        Rows Removed by Filter: 31332||
     1074||                                                  ->  Index Scan using "Event_pkey" on "Event" e  (cost\=0.29..8.30 rows\=1 width\=39) (actual time\=0.021..0.023 rows\=1 loops\=5)||
     1075||                                                        Index Cond: (event_id \= 1)||
     1076||                                            ->  Index Scan using idx_ticket_event_happening_id on "Ticket" t  (cost\=0.44..4974.83 rows\=1542 width\=16) (actual time\=0.052..0.324 rows\=865 loops\=5)||
     1077||                                                  Index Cond: (event_happening_id \= eh.event_happening_id)||
     1078||                          ->  Index Scan using idx_ticket_refund_purchase_id on "Ticket_Refund" tr  (cost\=0.43..7.92 rows\=1 width\=20) (actual time\=0.007..0.007 rows\=0 loops\=1163)||
     1079||                                Index Cond: (purchase_id \= tp.purchase_id)||
     1080||Planning Time: 1.604 ms||
     1081||JIT:||
     1082||  Functions: 96||
     1083||  Options: Inlining false, Optimization false, Expressions true, Deforming true||
     1084||  Timing: Generation 8.629 ms (Deform 3.959 ms), Inlining 0.000 ms, Optimization 3.603 ms, Emission 79.354 ms, Total 91.585 ms||
     1085||Execution Time: 1510.555 ms||
    9921086
    9931087 * '''INSERT'''
    … …  
    10021096}}}
    10031097
     1098||= QUERY PLAN =||
     1099||Insert on "Ticket_Purchase"  (cost\=0.70..0.73 rows\=0 width\=0) (actual time\=0.351..0.352 rows\=0 loops\=1)||
     1100||  ->  Subquery Scan on "*SELECT*"  (cost\=0.70..0.73 rows\=1 width\=552) (actual time\=0.145..0.147 rows\=1 loops\=1)||
     1101||        ->  Result  (cost\=0.70..0.71 rows\=1 width\=88) (actual time\=0.132..0.133 rows\=1 loops\=1)||
     1102||              InitPlan 1||
     1103||                ->  Limit  (cost\=0.43..0.70 rows\=1 width\=8) (actual time\=0.126..0.127 rows\=1 loops\=1)||
     1104||                      ->  Index Only Scan Backward using "Ticket_Purchase_pkey" on "Ticket_Purchase" "Ticket_Purchase_1"  (cost\=0.43..4162966.51 rows\=16000002 width\=8) (actual time\=0.124..0.124 rows\=1 loops\=1)||
     1105||                            Heap Fetches: 2||
     1106||Planning Time: 0.208 ms||
     1107||Trigger for constraint fk_purchase_ticket: time\=0.225 calls\=1||
     1108||Trigger for constraint fk_purchase_user: time\=0.176 calls\=1||
     1109||Execution Time: 0.802 ms||
     1110
    10041111 * '''UPDATE'''
    10051112
    … …  
    10121119
    10131120}}}
     1121
     1122||= QUERY PLAN =||
     1123||Update on "Ticket_Purchase"  (cost\=0.56..8.58 rows\=0 width\=0) (actual time\=0.161..0.162 rows\=0 loops\=1)||
     1124||  ->  Index Scan using "Ticket_Purchase_qr_code_key" on "Ticket_Purchase"  (cost\=0.56..8.58 rows\=1 width\=10) (actual time\=0.115..0.116 rows\=1 loops\=1)||
     1125||        Index Cond: ((qr_code)::text \= 'QR-TEST-CODE-004'::text)||
     1126||Planning Time: 0.134 ms||
     1127||Execution Time: 0.195 ms||
    10141128
    10151129== Анализа и оптимизација на `Future_Events`