Changes between Version 49 and Version 50 of QueryOptimization


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

--

Legend:

Unmodified
Added
Removed
Modified

  • QueryOptimization

    v49 v50  
    373373}}}
    374374
     375||= QUERY PLAN =||
     376||Nested Loop Left Join  (cost\=1002.00..232798.26 rows\=1 width\=115) (actual time\=431.345..441.975 rows\=1 loops\=1)||
     377||  ->  Nested Loop  (cost\=1001.56..232789.81 rows\=1 width\=99) (actual time\=431.312..441.941 rows\=1 loops\=1)||
     378||        ->  Nested Loop  (cost\=1001.28..232789.45 rows\=1 width\=68) (actual time\=431.253..441.878 rows\=1 loops\=1)||
     379||              ->  Nested Loop  (cost\=1000.99..232789.15 rows\=1 width\=68) (actual time\=431.200..441.822 rows\=1 loops\=1)||
     380||                    ->  Nested Loop  (cost\=1000.43..232780.57 rows\=1 width\=60) (actual time\=431.038..441.657 rows\=1 loops\=1)||
     381||                          ->  Index Scan using "User_pkey" on "User" u  (cost\=0.43..8.45 rows\=1 width\=26) (actual time\=0.114..0.118 rows\=1 loops\=1)||
     382||                                Index Cond: (user_id \= 1)||
     383||                          ->  Gather  (cost\=1000.00..232772.11 rows\=1 width\=42) (actual time\=430.826..441.440 rows\=1 loops\=1)||
     384||                                Workers Planned: 4||
     385||                                Workers Launched: 4||
     386||                                ->  Parallel Seq Scan on "Ticket_Purchase" tp  (cost\=0.00..231772.01 rows\=1 width\=42) (actual time\=389.356..389.357 rows\=0 loops\=5)||
     387||                                      Filter: (user_id \= 1)||
     388||                                      Rows Removed by Filter: 3200000||
     389||                    ->  Index Scan using "Ticket_pkey" on "Ticket" t  (cost\=0.56..8.58 rows\=1 width\=16) (actual time\=0.104..0.105 rows\=1 loops\=1)||
     390||                          Index Cond: (ticket_id \= tp.ticket_id)||
     391||              ->  Index Scan using "Event_Happening_pkey" on "Event_Happening" eh  (cost\=0.29..0.31 rows\=1 width\=16) (actual time\=0.033..0.034 rows\=1 loops\=1)||
     392||                    Index Cond: (event_happening_id \= t.event_happening_id)||
     393||        ->  Index Scan using "Event_pkey" on "Event" e  (cost\=0.29..0.35 rows\=1 width\=39) (actual time\=0.039..0.040 rows\=1 loops\=1)||
     394||              Index Cond: (event_id \= eh.event_id)||
     395||  ->  Index Scan using "Ticket_Refund_purchase_id_key" on "Ticket_Refund" tr  (cost\=0.43..8.45 rows\=1 width\=24) (actual time\=0.015..0.016 rows\=0 loops\=1)||
     396||        Index Cond: (purchase_id \= tp.purchase_id)||
     397||Planning Time: 15.360 ms||
     398||JIT:||
     399||  Functions: 44||
     400||  Options: Inlining false, Optimization false, Expressions true, Deforming true||
     401||  Timing: Generation 3.367 ms (Deform 1.376 ms), Inlining 0.000 ms, Optimization 2.363 ms, Emission 42.753 ms, Total 48.484 ms||
     402||Execution Time: 443.784 ms||
     403
    375404 * '''INSERT'''
    376405
    … …  
    384413}}}
    385414
     415||= QUERY PLAN =||
     416||Insert on "Ticket_Purchase"  (cost\=0.47..0.50 rows\=0 width\=0) (actual time\=0.217..0.218 rows\=0 loops\=1)||
     417||  ->  Subquery Scan on "*SELECT*"  (cost\=0.47..0.50 rows\=1 width\=552) (actual time\=0.055..0.057 rows\=1 loops\=1)||
     418||        ->  Result  (cost\=0.47..0.48 rows\=1 width\=80) (actual time\=0.043..0.044 rows\=1 loops\=1)||
     419||              InitPlan 1||
     420||                ->  Limit  (cost\=0.43..0.47 rows\=1 width\=8) (actual time\=0.038..0.039 rows\=1 loops\=1)||
     421||                      ->  Index Only Scan Backward using "Ticket_Purchase_pkey" on "Ticket_Purchase" "Ticket_Purchase_1"  (cost\=0.43..484152.95 rows\=16000004 width\=8) (actual time\=0.037..0.038 rows\=1 loops\=1)||
     422||                            Heap Fetches: 3||
     423||Planning Time: 0.207 ms||
     424||Trigger for constraint fk_purchase_ticket: time\=0.190 calls\=1||
     425||Trigger for constraint fk_purchase_user: time\=0.136 calls\=1||
     426||Execution Time: 0.600 ms||
     427
    386428 * '''UPDATE'''
    387429
    … …  
    395437}}}
    396438
     439||= QUERY PLAN =||
     440||Update on "Ticket_Purchase"  (cost\=0.56..8.58 rows\=0 width\=0) (actual time\=0.121..0.122 rows\=0 loops\=1)||
     441||  ->  Index Scan using "Ticket_Purchase_qr_code_key" on "Ticket_Purchase"  (cost\=0.56..8.58 rows\=1 width\=10) (actual time\=0.048..0.050 rows\=1 loops\=1)||
     442||        Index Cond: ((qr_code)::text \= 'QR-TEST-CODE-001'::text)||
     443||        Filter: (user_id \= 1)||
     444||Planning Time: 0.181 ms||
     445||Execution Time: 0.157 ms||
     446
    397447Приказот на историјата на билети трае предолги 251.9s, што го блокира корисничкиот профил. Индексот на user_id овозможува базата веднаш да ги лоцира билетите на конкретниот корисник без да ги пребарува сите трансакции во системот.
    398448
    … …  
    425475
    426476}}}
     477
     478||= QUERY PLAN =||
     479||Nested Loop Left Join  (cost\=1002.00..232798.25 rows\=1 width\=115) (actual time\=438.622..449.169 rows\=2 loops\=1)||
     480||  ->  Nested Loop  (cost\=1001.56..232789.80 rows\=1 width\=99) (actual time\=438.588..449.128 rows\=2 loops\=1)||
     481||        ->  Gather  (cost\=1001.13..232781.35 rows\=1 width\=81) (actual time\=438.383..448.900 rows\=2 loops\=1)||
     482||              Workers Planned: 4||
     483||              Workers Launched: 4||
     484||              ->  Nested Loop  (cost\=1.14..231781.25 rows\=1 width\=81) (actual time\=397.225..397.235 rows\=0 loops\=5)||
     485||                    ->  Nested Loop  (cost\=0.85..231780.89 rows\=1 width\=50) (actual time\=397.203..397.211 rows\=0 loops\=5)||
     486||                          ->  Nested Loop  (cost\=0.56..231780.59 rows\=1 width\=50) (actual time\=397.176..397.182 rows\=0 loops\=5)||
     487||                                ->  Parallel Seq Scan on "Ticket_Purchase" tp  (cost\=0.00..231772.01 rows\=1 width\=42) (actual time\=397.114..397.115 rows\=0 loops\=5)||
     488||                                      Filter: (user_id \= 1)||
     489||                                      Rows Removed by Filter: 3200000||
     490||                                ->  Index Scan using "Ticket_pkey" on "Ticket" t  (cost\=0.56..8.58 rows\=1 width\=16) (actual time\=0.107..0.107 rows\=1 loops\=2)||
     491||                                      Index Cond: (ticket_id \= tp.ticket_id)||
     492||                          ->  Index Scan using "Event_Happening_pkey" on "Event_Happening" eh  (cost\=0.29..0.31 rows\=1 width\=16) (actual time\=0.055..0.055 rows\=1 loops\=2)||
     493||                                Index Cond: (event_happening_id \= t.event_happening_id)||
     494||                    ->  Index Scan using "Event_pkey" on "Event" e  (cost\=0.29..0.35 rows\=1 width\=39) (actual time\=0.044..0.044 rows\=1 loops\=2)||
     495||                          Index Cond: (event_id \= eh.event_id)||
     496||        ->  Index Scan using "User_pkey" on "User" u  (cost\=0.43..8.45 rows\=1 width\=26) (actual time\=0.086..0.087 rows\=1 loops\=2)||
     497||              Index Cond: (user_id \= 1)||
     498||  ->  Index Scan using idx_ticket_refund_purchase_id on "Ticket_Refund" tr  (cost\=0.43..8.45 rows\=1 width\=24) (actual time\=0.010..0.010 rows\=0 loops\=2)||
     499||        Index Cond: (purchase_id \= tp.purchase_id)||
     500||Planning Time: 2.459 ms||
     501||JIT:||
     502||  Functions: 99||
     503||  Options: Inlining false, Optimization false, Expressions true, Deforming true||
     504||  Timing: Generation 7.544 ms (Deform 3.336 ms), Inlining 0.000 ms, Optimization 3.874 ms, Emission 71.829 ms, Total 83.247 ms||
     505||Execution Time: 450.880 ms||
    427506
    428507 * '''INSERT'''
    … …  
    437516}}}
    438517
     518||= QUERY PLAN =||
     519||Insert on "Ticket_Purchase"  (cost\=0.47..0.50 rows\=0 width\=0) (actual time\=0.452..0.453 rows\=0 loops\=1)||
     520||  ->  Subquery Scan on "*SELECT*"  (cost\=0.47..0.50 rows\=1 width\=552) (actual time\=0.049..0.051 rows\=1 loops\=1)||
     521||        ->  Result  (cost\=0.47..0.48 rows\=1 width\=80) (actual time\=0.038..0.039 rows\=1 loops\=1)||
     522||              InitPlan 1||
     523||                ->  Limit  (cost\=0.43..0.47 rows\=1 width\=8) (actual time\=0.033..0.033 rows\=1 loops\=1)||
     524||                      ->  Index Only Scan Backward using "Ticket_Purchase_pkey" on "Ticket_Purchase" "Ticket_Purchase_1"  (cost\=0.43..484152.93 rows\=16000003 width\=8) (actual time\=0.032..0.032 rows\=1 loops\=1)||
     525||                            Heap Fetches: 2||
     526||Planning Time: 0.207 ms||
     527||Trigger for constraint fk_purchase_ticket: time\=0.194 calls\=1||
     528||Trigger for constraint fk_purchase_user: time\=0.130 calls\=1||
     529||Execution Time: 0.828 ms||
     530
    439531 * '''UPDATE'''
    440532
    … …  
    447539
    448540}}}
     541
     542||= QUERY PLAN =||
     543||Update on "Ticket_Purchase"  (cost\=0.56..8.58 rows\=0 width\=0) (actual time\=0.207..0.207 rows\=0 loops\=1)||
     544||  ->  Index Scan using "Ticket_Purchase_qr_code_key" on "Ticket_Purchase"  (cost\=0.56..8.58 rows\=1 width\=10) (actual time\=0.124..0.126 rows\=1 loops\=1)||
     545||        Index Cond: ((qr_code)::text \= 'QR-TEST-CODE-002'::text)||
     546||        Filter: (user_id \= 1)||
     547||Planning Time: 0.150 ms||
     548||Execution Time: 0.267 ms||
    449549
    450550== Анализа и оптимизација на `Event_User_Ratings`