Changes between Version 48 and Version 49 of QueryOptimization

Timestamp:

05/09/26 22:19:25 (3 weeks ago)

Author:

231027

Comment:

--

QueryOptimization

@@ -196 +196 @@
 }}}
 
+||= QUERY PLAN =||
+||Nested Loop  (cost\=1001.14..10398.19 rows\=2264 width\=55) (actual time\=447.576..2164.258 rows\=775 loops\=1)||
+||  ->  Index Scan using "Venue_pkey" on "Venue" v  (cost\=0.29..8.30 rows\=1 width\=28) (actual time\=68.551..68.555 rows\=1 loops\=1)||
+||        Index Cond: (venue_id \= 1)||
+||  ->  Gather  (cost\=1000.85..10367.25 rows\=2264 width\=35) (actual time\=379.017..2095.610 rows\=775 loops\=1)||
+||        Workers Planned: 1||
+||        Workers Launched: 1||
+||        ->  Nested Loop  (cost\=0.85..9140.85 rows\=1332 width\=35) (actual time\=932.279..1787.769 rows\=388 loops\=2)||
+||              ->  Parallel Index Scan using "Section_pkey" on "Section" s  (cost\=0.29..1691.80 rows\=4 width\=23) (actual time\=771.383..1395.534 rows\=2 loops\=2)||
+||                    Filter: (venue_id \= 1)||
+||                    Rows Removed by Filter: 27502||
+||              ->  Index Scan using uq_seat_section_number on "Seat" st  (cost\=0.56..1853.21 rows\=905 width\=20) (actual time\=64.373..156.866 rows\=155 loops\=5)||
+||                    Index Cond: (section_id \= s.section_id)||
+||Planning Time: 1874.048 ms||
+||Execution Time: 2164.361 ms||
+
  * '''INSERT'''
 
@@ -205 +221 @@
 
 }}}
+
+||= QUERY PLAN =||
+||Insert on "Seat"  (cost\=0.67..0.69 rows\=0 width\=0) (actual time\=745.461..745.464 rows\=0 loops\=1)||
+||  ->  Subquery Scan on "*SELECT*"  (cost\=0.67..0.69 rows\=1 width\=20) (actual time\=481.921..481.925 rows\=1 loops\=1)||
+||        ->  Result  (cost\=0.67..0.69 rows\=1 width\=16) (actual time\=481.918..481.920 rows\=1 loops\=1)||
+||              InitPlan 1||
+||                ->  Limit  (cost\=0.56..0.67 rows\=1 width\=8) (actual time\=481.909..481.910 rows\=1 loops\=1)||
+||                      ->  Index Only Scan Backward using "Seat_pkey" on "Seat" "Seat_1"  (cost\=0.56..2330912.81 rows\=20753208 width\=8) (actual time\=481.907..481.908 rows\=1 loops\=1)||
+||                            Heap Fetches: 0||
+||Planning Time: 0.339 ms||
+||Trigger for constraint fk_seat_section: time\=0.533 calls\=1||
+||Execution Time: 746.039 ms||
 
  * '''UPDATE'''
@@ -217 +245 @@
 }}}
 
+||= QUERY PLAN =||
+||Update on "Seat"  (cost\=0.00..391602.10 rows\=0 width\=0) (actual time\=308799.033..308799.035 rows\=0 loops\=1)||
+||  ->  Seq Scan on "Seat"  (cost\=0.00..391602.10 rows\=11492 width\=10) (actual time\=308792.032..308792.034 rows\=1 loops\=1)||
+||        Filter: (seat_number \= 999999)||
+||        Rows Removed by Filter: 20753209||
+||Planning Time: 0.114 ms||
+||JIT:||
+||  Functions: 4||
+||  Options: Inlining false, Optimization false, Expressions true, Deforming true||
+||  Timing: Generation 0.405 ms (Deform 0.071 ms), Inlining 0.000 ms, Optimization 0.595 ms, Emission 6.566 ms, Total 7.565 ms||
+||Execution Time: 312239.424 ms||
+
 Времето за ажурирање од 312s е неприфатливо за интеракција со мапа на седишта во реално време. Со поставување индекси на seat_number и venue_id, пребарувањето и промената на статусот на седиштата се извршуваат за милисекунди наместо за неколку минути.
 
@@ -245 +285 @@
 }}}
 
+||= QUERY PLAN =||
+||Nested Loop  (cost\=1.01..3060.48 rows\=2264 width\=55) (actual time\=0.184..0.512 rows\=776 loops\=1)||
+||  ->  Nested Loop  (cost\=0.57..16.76 rows\=6 width\=43) (actual time\=0.140..0.144 rows\=5 loops\=1)||
+||        ->  Index Scan using "Venue_pkey" on "Venue" v  (cost\=0.29..8.30 rows\=1 width\=28) (actual time\=0.077..0.078 rows\=1 loops\=1)||
+||              Index Cond: (venue_id \= 1)||
+||        ->  Index Scan using idx_section_venue_id on "Section" s  (cost\=0.29..8.39 rows\=6 width\=23) (actual time\=0.058..0.060 rows\=5 loops\=1)||
+||              Index Cond: (venue_id \= 1)||
+||  ->  Index Scan using idx_seat_section_id on "Seat" st  (cost\=0.44..498.24 rows\=905 width\=20) (actual time\=0.012..0.053 rows\=155 loops\=5)||
+||        Index Cond: (section_id \= s.section_id)||
+||Planning Time: 1.235 ms||
+||Execution Time: 0.572 ms||
+
  * '''INSERT'''
 
@@ -254 +306 @@
 
 }}}
+
+||= QUERY PLAN =||
+||Insert on "Seat"  (cost\=0.67..0.69 rows\=0 width\=0) (actual time\=0.493..0.494 rows\=0 loops\=1)||
+||  ->  Subquery Scan on "*SELECT*"  (cost\=0.67..0.69 rows\=1 width\=20) (actual time\=0.169..0.171 rows\=1 loops\=1)||
+||        ->  Result  (cost\=0.67..0.69 rows\=1 width\=16) (actual time\=0.167..0.168 rows\=1 loops\=1)||
+||              InitPlan 1||
+||                ->  Limit  (cost\=0.56..0.67 rows\=1 width\=8) (actual time\=0.162..0.163 rows\=1 loops\=1)||
+||                      ->  Index Only Scan Backward using "Seat_pkey" on "Seat" "Seat_1"  (cost\=0.56..2330912.84 rows\=20753210 width\=8) (actual time\=0.161..0.161 rows\=1 loops\=1)||
+||                            Heap Fetches: 1||
+||Planning Time: 0.219 ms||
+||Trigger for constraint fk_seat_section: time\=0.300 calls\=1||
+||Execution Time: 0.839 ms||
 
  * '''UPDATE'''
@@ -265 +329 @@
 
 }}}
+
+||= QUERY PLAN =||
+||Update on "Seat"  (cost\=0.44..44297.09 rows\=0 width\=0) (actual time\=0.395..0.396 rows\=0 loops\=1)||
+||  ->  Index Scan using idx_seat_number on "Seat"  (cost\=0.44..44297.09 rows\=11492 width\=10) (actual time\=0.193..0.195 rows\=1 loops\=1)||
+||        Index Cond: (seat_number \= 111222)||
+||Planning Time: 0.141 ms||
+||Execution Time: 0.461 ms||
 
 == Анализа и оптимизација на `User_Tickets`