Changes between Version 90 and Version 91 of QueryOptimization


Ignore:
Timestamp:
06/30/26 23:29:43 (5 days ago)
Author:
231027
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • QueryOptimization

    v90 v91  
    177177}}}
    178178
    179 ||= QUERY PLAN =||
     179||= QUERY PLAN = =  = =  =||
    180180||Gather  (cost\=110699.49..459840.66 rows\=4 width\=145) (actual time\=370.701..375.993 rows\=0.00 loops\=1)||
    181 ||Workers Planned: 2||
    182 ||Workers Launched: 2||
    183 ||Buffers: shared read\=82353||
     181||Workers Planned 2||
     182||Workers Launched 2||
     183||Buffers shared read\=82353||
    184184||->  Nested Loop Left Join  (cost\=109699.49..458840.26 rows\=2 width\=145) (actual time\=312.925..312.929 rows\=0.00 loops\=3)||
    185 ||Buffers: shared read\=82353||
     185||Buffers shared read\=82353||
    186186||->  Nested Loop Left Join  (cost\=109699.07..458836.23 rows\=2 width\=145) (actual time\=312.924..312.928 rows\=0.00 loops\=3)||
    187 ||Buffers: shared read\=82353||
     187||Buffers shared read\=82353||
    188188||->  Nested Loop  (cost\=109698.64..458832.18 rows\=2 width\=129) (actual time\=312.923..312.927 rows\=0.00 loops\=3)||
    189 ||Buffers: shared read\=82353||
     189||Buffers shared read\=82353||
    190190||->  Nested Loop  (cost\=109698.35..458828.40 rows\=2 width\=98) (actual time\=312.923..312.927 rows\=0.00 loops\=3)||
    191 ||Buffers: shared read\=82353||
     191||Buffers shared read\=82353||
    192192||->  Nested Loop  (cost\=109698.07..458824.71 rows\=2 width\=90) (actual time\=312.923..312.926 rows\=0.00 loops\=3)||
    193 ||Buffers: shared read\=82353||
     193||Buffers shared read\=82353||
    194194||->  Nested Loop  (cost\=109697.63..458819.48 rows\=2 width\=82) (actual time\=312.922..312.925 rows\=0.00 loops\=3)||
    195 ||Buffers: shared read\=82353||
     195||Buffers shared read\=82353||
    196196||->  Parallel Hash Join  (cost\=109696.77..458802.55 rows\=2 width\=62) (actual time\=312.921..312.924 rows\=0.00 loops\=3)||
    197 ||Hash Cond: (toi.order_id \= o.order_id)||
    198 ||Buffers: shared read\=82353||
    199 ||->  Parallel Seq Scan on ""Ticket_Order_Item"" toi  (cost\=0.00..326137.01 rows\=8750001 width\=62) (never executed)||
     197||Hash Cond (toi.order_id \= o.order_id)||
     198||Buffers shared read\=82353||
     199||                                            ->  Parallel Seq Scan on ""Ticket_Order_Item"" toi  (cost\=0.00..326137.01 rows\=8750001 width\=62) (never executed)||
    200200||->  Parallel Hash  (cost\=109696.76..109696.76 rows\=1 width\=16) (actual time\=312.874..312.875 rows\=0.00 loops\=3)||
    201 ||Buckets: 1024  Batches: 1  Memory Usage: 0kB||
    202 ||Buffers: shared read\=82353||
    203 ||->  Parallel Seq Scan on ""Ticket_Order"" o  (cost\=0.00..109696.76 rows\=1 width\=16) (actual time\=312.664..312.664 rows\=0.00 loops\=3)||
    204 ||Filter: (user_id \= 5)||
    205 ||Rows Removed by Filter: 1750000||
    206 ||Buffers: shared read\=82353||
     201||Buckets 1024  Batches 1  Memory Usage 0kB||
     202||Buffers shared read\=82353||
     203||                                                  ->  Parallel Seq Scan on ""Ticket_Order"" o  (cost\=0.00..109696.76 rows\=1 width\=16) (actual time\=312.664..312.664 rows\=0.00 loops\=3)||
     204||Filter (user_id \= 5)||
     205||Rows Removed by Filter 1750000||
     206||Buffers shared read\=82353||
    207207||->  Materialize  (cost\=0.86..16.91 rows\=1 width\=36) (never executed)||
    208208||->  Nested Loop  (cost\=0.86..16.91 rows\=1 width\=36) (never executed)||
    209 ||->  Index Scan using ""User_pkey"" on ""User"" u  (cost\=0.43..8.45 rows\=1 width\=28) (never executed)||
    210 ||Index Cond: (user_id \= 5)||
    211 ||Filter: is_active||
    212 ||Index Searches: 0||
    213 ||->  Index Only Scan using ""Regular_User_pkey"" on ""Regular_User"" ru  (cost\=0.43..8.45 rows\=1 width\=8) (never executed)||
    214 ||Index Cond: (user_id \= 5)||
    215 ||Heap Fetches: 0||
    216 ||Index Searches: 0||
    217 ||->  Index Scan using ""Ticket_pkey"" on ""Ticket"" t  (cost\=0.44..2.61 rows\=1 width\=16) (never executed)||
    218 ||Index Cond: (ticket_id \= toi.ticket_id)||
    219 ||Index Searches: 0||
    220 ||->  Index Scan using ""Event_Happening_pkey"" on ""Event_Happening"" eh  (cost\=0.29..1.85 rows\=1 width\=24) (never executed)||
    221 ||Index Cond: (event_happening_id \= t.event_happening_id)||
    222 ||Index Searches: 0||
    223 ||->  Index Scan using ""Event_pkey"" on ""Event"" e  (cost\=0.29..1.89 rows\=1 width\=39) (never executed)||
    224 ||Index Cond: (event_id \= eh.event_id)||
    225 ||Index Searches: 0||
    226 ||->  Index Scan using ""Ticket_Refund_Item_order_item_id_key"" on ""Ticket_Refund_Item"" tri  (cost\=0.43..2.02 rows\=1 width\=24) (never executed)||
    227 ||Index Cond: (order_item_id \= toi.order_item_id)||
    228 ||Index Searches: 0||
    229 ||->  Index Scan using ""Ticket_Refund_pkey"" on ""Ticket_Refund"" tr  (cost\=0.42..2.02 rows\=1 width\=16) (never executed)||
    230 ||Index Cond: (refund_id \= tri.refund_id)||
    231 ||Index Searches: 0||
     209||                                                  ->  Index Scan using ""User_pkey"" on ""User"" u  (cost\=0.43..8.45 rows\=1 width\=28) (never executed)||
     210||Index Cond (user_id \= 5)||
     211||Filter is_active||
     212||Index Searches 0||
     213||                                                  ->  Index Only Scan using ""Regular_User_pkey"" on ""Regular_User"" ru  (cost\=0.43..8.45 rows\=1 width\=8) (never executed)||
     214||Index Cond (user_id \= 5)||
     215||Heap Fetches 0||
     216||Index Searches 0||
     217||                                ->  Index Scan using ""Ticket_pkey"" on ""Ticket"" t  (cost\=0.44..2.61 rows\=1 width\=16) (never executed)||
     218||Index Cond (ticket_id \= toi.ticket_id)||
     219||Index Searches 0||
     220||                          ->  Index Scan using ""Event_Happening_pkey"" on ""Event_Happening"" eh  (cost\=0.29..1.85 rows\=1 width\=24) (never executed)||
     221||Index Cond (event_happening_id \= t.event_happening_id)||
     222||Index Searches 0||
     223||                    ->  Index Scan using ""Event_pkey"" on ""Event"" e  (cost\=0.29..1.89 rows\=1 width\=39) (never executed)||
     224||Index Cond (event_id \= eh.event_id)||
     225||Index Searches 0||
     226||              ->  Index Scan using ""Ticket_Refund_Item_order_item_id_key"" on ""Ticket_Refund_Item"" tri  (cost\=0.43..2.02 rows\=1 width\=24) (never executed)||
     227||Index Cond (order_item_id \= toi.order_item_id)||
     228||Index Searches 0||
     229||        ->  Index Scan using ""Ticket_Refund_pkey"" on ""Ticket_Refund"" tr  (cost\=0.42..2.02 rows\=1 width\=16) (never executed)||
     230||Index Cond (refund_id \= tri.refund_id)||
     231||Index Searches 0||
    232232||Planning||
    233 ||Buffers: shared hit\=33 read\=44 dirtied\=2||
    234 ||Planning Time: 13.836 ms||
    235 ||Execution Time: 376.133 ms||
     233||Buffers shared hit\=33 read\=44 dirtied\=2||
     234||Planning Time 13.836 ms||
     235||Execution Time 376.133 ms||
    236236
    237237 * '''INSERT'''
    … …  
    245245}}}
    246246
    247 ||= QUERY PLAN =||
     247||= QUERY PLAN = =  =||
    248248||Insert on ""Ticket_Order_Item""  (cost\=0.00..0.01 rows\=0 width\=0) (actual time\=0.049..0.049 rows\=0.00 loops\=1)||
    249 ||Buffers: shared hit\=5 dirtied\=1||
     249||Buffers shared hit\=5 dirtied\=1||
    250250||->  Result  (cost\=0.00..0.01 rows\=1 width\=556) (actual time\=0.001..0.001 rows\=1.00 loops\=1)||
    251 ||Planning Time: 0.028 ms||
    252 ||Trigger for constraint fk_item_order: time\=0.251 calls\=1||
    253 ||Trigger for constraint fk_item_ticket: time\=0.210 calls\=1||
    254 ||Execution Time: 0.523 ms||
     251||Planning Time 0.028 ms||
     252||Trigger for constraint fk_item_order time\=0.251 calls\=1||
     253||Trigger for constraint fk_item_ticket time\=0.210 calls\=1||
     254||Execution Time 0.523 ms||
    255255
    256256 * '''UPDATE'''
    … …  
    264264}}}
    265265
    266 ||= QUERY PLAN =||
     266||= QUERY PLAN = =  =||
    267267||Update on ""Ticket_Order_Item""  (cost\=0.44..8.46 rows\=0 width\=0) (actual time\=0.085..0.086 rows\=0.00 loops\=1)||
    268 ||Buffers: shared hit\=7||
    269 ||->  Index Scan using ""Ticket_Order_Item_pkey"" on ""Ticket_Order_Item""  (cost\=0.44..8.46 rows\=1 width\=522) (actual time\=0.039..0.040 rows\=1.00 loops\=1)||
    270 ||Index Cond: (order_item_id \= 99999999)||
    271 ||Index Searches: 1||
    272 ||Buffers: shared hit\=4||
    273 ||Planning Time: 0.173 ms||
    274 ||Execution Time: 0.118 ms||
     268||Buffers shared hit\=7||
     269||  ->  Index Scan using ""Ticket_Order_Item_pkey"" on ""Ticket_Order_Item""  (cost\=0.44..8.46 rows\=1 width\=522) (actual time\=0.039..0.040 rows\=1.00 loops\=1)||
     270||Index Cond (order_item_id \= 99999999)||
     271||Index Searches 1||
     272||Buffers shared hit\=4||
     273||Planning Time 0.173 ms||
     274||Execution Time 0.118 ms||
    275275
    276276==== 5. Оптимизација и индексирање:
    … …  
    316316}}}
    317317
    318 ||= QUERY PLAN =||
     318||= QUERY PLAN = =  =||
    319319||Nested Loop Left Join  (cost\=3.59..75.95 rows\=4 width\=145) (actual time\=1.244..1.247 rows\=0.00 loops\=1)||
    320 ||Buffers: shared hit\=1 read\=10||
     320||Buffers shared hit\=1 read\=10||
    321321||->  Nested Loop Left Join  (cost\=3.17..67.89 rows\=4 width\=145) (actual time\=1.243..1.246 rows\=0.00 loops\=1)||
    322 ||Buffers: shared hit\=1 read\=10||
     322||Buffers shared hit\=1 read\=10||
    323323||->  Nested Loop  (cost\=2.74..59.80 rows\=4 width\=129) (actual time\=1.242..1.245 rows\=0.00 loops\=1)||
    324 ||Buffers: shared hit\=1 read\=10||
     324||Buffers shared hit\=1 read\=10||
    325325||->  Nested Loop  (cost\=2.46..52.24 rows\=4 width\=98) (actual time\=1.242..1.244 rows\=0.00 loops\=1)||
    326 ||Buffers: shared hit\=1 read\=10||
     326||Buffers shared hit\=1 read\=10||
    327327||->  Nested Loop  (cost\=2.17..44.84 rows\=4 width\=90) (actual time\=1.241..1.244 rows\=0.00 loops\=1)||
    328 ||Buffers: shared hit\=1 read\=10||
     328||Buffers shared hit\=1 read\=10||
    329329||->  Nested Loop  (cost\=1.73..34.40 rows\=4 width\=82) (actual time\=1.241..1.243 rows\=0.00 loops\=1)||
    330 ||Buffers: shared hit\=1 read\=10||
     330||Buffers shared hit\=1 read\=10||
    331331||->  Nested Loop  (cost\=1.29..25.36 rows\=1 width\=36) (actual time\=1.240..1.242 rows\=0.00 loops\=1)||
    332 ||Buffers: shared hit\=1 read\=10||
     332||Buffers shared hit\=1 read\=10||
    333333||->  Nested Loop  (cost\=0.86..16.91 rows\=1 width\=36) (actual time\=1.119..1.123 rows\=1.00 loops\=1)||
    334 ||Buffers: shared hit\=1 read\=7||
    335 ||->  Index Scan using ""User_pkey"" on ""User"" u  (cost\=0.43..8.45 rows\=1 width\=28) (actual time\=0.716..0.718 rows\=1.00 loops\=1)||
    336 ||Index Cond: (user_id \= 99)||
    337 ||Filter: is_active||
    338 ||Index Searches: 1||
    339 ||Buffers: shared read\=4||
    340 ||->  Index Only Scan using ""Regular_User_pkey"" on ""Regular_User"" ru  (cost\=0.43..8.45 rows\=1 width\=8) (actual time\=0.398..0.400 rows\=1.00 loops\=1)||
    341 ||Index Cond: (user_id \= 99)||
    342 ||Heap Fetches: 0||
    343 ||Index Searches: 1||
    344 ||Buffers: shared hit\=1 read\=3||
    345 ||->  Index Scan using idx_ticket_order_user_id on ""Ticket_Order"" o  (cost\=0.43..8.44 rows\=1 width\=16) (actual time\=0.116..0.116 rows\=0.00 loops\=1)||
    346 ||Index Cond: (user_id \= 99)||
    347 ||Index Searches: 1||
    348 ||Buffers: shared read\=3||
    349 ||->  Index Scan using idx_toi_order_id on ""Ticket_Order_Item"" toi  (cost\=0.44..8.82 rows\=22 width\=62) (never executed)||
    350 ||Index Cond: (order_id \= o.order_id)||
    351 ||Index Searches: 0||
    352 ||->  Index Scan using ""Ticket_pkey"" on ""Ticket"" t  (cost\=0.44..2.61 rows\=1 width\=16) (never executed)||
    353 ||Index Cond: (ticket_id \= toi.ticket_id)||
    354 ||Index Searches: 0||
    355 ||->  Index Scan using ""Event_Happening_pkey"" on ""Event_Happening"" eh  (cost\=0.29..1.85 rows\=1 width\=24) (never executed)||
    356 ||Index Cond: (event_happening_id \= t.event_happening_id)||
    357 ||Index Searches: 0||
    358 ||->  Index Scan using ""Event_pkey"" on ""Event"" e  (cost\=0.29..1.89 rows\=1 width\=39) (never executed)||
    359 ||Index Cond: (event_id \= eh.event_id)||
    360 ||Index Searches: 0||
    361 ||->  Index Scan using ""Ticket_Refund_Item_order_item_id_key"" on ""Ticket_Refund_Item"" tri  (cost\=0.43..2.02 rows\=1 width\=24) (never executed)||
    362 ||Index Cond: (order_item_id \= toi.order_item_id)||
    363 ||Index Searches: 0||
    364 ||->  Index Scan using ""Ticket_Refund_pkey"" on ""Ticket_Refund"" tr  (cost\=0.42..2.02 rows\=1 width\=16) (never executed)||
    365 ||Index Cond: (refund_id \= tri.refund_id)||
    366 ||Index Searches: 0||
    367 ||Planning||
    368 ||Buffers: shared hit\=264 read\=37||
    369 ||Planning Time: 41.864 ms||
    370 ||Execution Time: 1.392 ms||
     334||Buffers shared hit\=1 read\=7||
     335||                                            ->  Index Scan using ""User_pkey"" on ""User"" u  (cost\=0.43..8.45 rows\=1 width\=28) (actual time\=0.716..0.718 rows\=1.00 loops\=1)||
     336||Index Cond (user_id \= 99)||
     337||Filter is_active||
     338||Index Searches 1||
     339||Buffers shared read\=4||
     340||                                            ->  Index Only Scan using ""Regular_User_pkey"" on ""Regular_User"" ru  (cost\=0.43..8.45 rows\=1 width\=8) (actual time\=0.398..0.400 rows\=1.00 loops\=1)||
     341||Index Cond (user_id \= 99)||
     342||Heap Fetches 0||
     343||Index Searches 1||
     344||Buffers shared hit\=1 read\=3||
     345||                                      ->  Index Scan using idx_ticket_order_user_id on ""Ticket_Order"" o  (cost\=0.43..8.44 rows\=1 width\=16) (actual time\=0.116..0.116 rows\=0.00 loops\=1)||
     346||Index Cond (user_id \= 99)||
     347||Index Searches 1||
     348||Buffers shared read\=3||
    371349
    372350 * '''INSERT'''
    … …  
    380358}}}
    381359
    382 ||= QUERY PLAN =||
     360||= QUERY PLAN = =  =||
    383361||Insert on ""Ticket_Order_Item""  (cost\=0.00..0.01 rows\=0 width\=0) (actual time\=0.273..0.273 rows\=0.00 loops\=1)||
    384 ||Buffers: shared hit\=11 read\=3 dirtied\=3||
     362||Buffers shared hit\=11 read\=3 dirtied\=3||
    385363||->  Result  (cost\=0.00..0.01 rows\=1 width\=556) (actual time\=0.001..0.001 rows\=1.00 loops\=1)||
    386 ||Planning Time: 0.031 ms||
    387 ||Trigger for constraint fk_item_order: time\=0.241 calls\=1||
    388 ||Trigger for constraint fk_item_ticket: time\=2.938 calls\=1||
    389 ||Execution Time: 3.470 ms||
     364||Planning Time 0.031 ms||
     365||Trigger for constraint fk_item_order time\=0.241 calls\=1||
     366||Trigger for constraint fk_item_ticket time\=2.938 calls\=1||
     367||Execution Time 3.470 ms||
    390368
    391369 * '''UPDATE'''
    … …  
    398376}}}
    399377
    400 ||= QUERY PLAN =||
     378||= QUERY PLAN = =  =||
    401379||Update on ""Ticket_Order_Item""  (cost\=0.44..8.46 rows\=0 width\=0) (actual time\=0.102..0.103 rows\=0.00 loops\=1)||
    402 ||Buffers: shared hit\=7||
    403 ||->  Index Scan using ""Ticket_Order_Item_pkey"" on ""Ticket_Order_Item""  (cost\=0.44..8.46 rows\=1 width\=522) (actual time\=0.049..0.051 rows\=1.00 loops\=1)||
    404 ||Index Cond: (order_item_id \= 77777777)||
    405 ||Index Searches: 1||
    406 ||Buffers: shared hit\=4||
    407 ||Planning Time: 0.160 ms||
    408 ||Execution Time: 0.179 ms||
     380||Buffers shared hit\=7||
     381||  ->  Index Scan using ""Ticket_Order_Item_pkey"" on ""Ticket_Order_Item""  (cost\=0.44..8.46 rows\=1 width\=522) (actual time\=0.049..0.051 rows\=1.00 loops\=1)||
     382||Index Cond (order_item_id \= 77777777)||
     383||Index Searches 1||
     384||Buffers shared hit\=4||
     385||Planning Time 0.160 ms||
     386||Execution Time 0.179 ms||
    409387
    410388