Changes between Version 5 and Version 6 of OtherTopics

Timestamp:

02/13/26 17:50:43 (2 weeks ago)

Author:

231017

Comment:

--

OtherTopics

@@ -173 +173 @@
 
 Заклучуваме дека перформансите на процедурата ќе бидат многу подобри со овој индекс и го додаваме истиот.
+
+== Сценарио 2
+
+Ќе го разгледуваме прашалникот кој се извршува при преземање на сите песни/албуми објавени од артистот.
+
+{{{
+EXPLAIN ANALYZE SELECT
+    me.id,
+    me.title,
+    me.genre,
+    CASE
+        WHEN s.id IS NOT NULL AND s.album_id IS NULL THEN 'SONG'
+        WHEN a.id IS NOT NULL THEN 'ALBUM'
+        ELSE NULL
+        END AS type,
+    me.cover,
+    me.release_date
+FROM musical_entities me
+         LEFT JOIN songs s ON s.id = me.id
+         LEFT JOIN albums a ON a.id = me.id
+WHERE me.released_by = :artistId
+ORDER BY me.release_date DESC
+}}}
+
+Додаваме 1M нови записи во musical_entities табелата:
+
+{{{ 
+}}}
+
+Креираме композитен индекс на `released_by` и `release_date`
+
+{{{
+CREATE INDEX me_released_by_release_date_idx
+    ON musical_entities (released_by, release_date DESC);
+}}}
+
+Без индекс добиваме: 
+
+{{{
++--------------------------------------------------------------------------------------------------------------------------------------------------------+
+|QUERY PLAN                                                                                                                                              |
++--------------------------------------------------------------------------------------------------------------------------------------------------------+
+|Gather Merge  (cost=48470.13..48470.59 rows=4 width=82) (actual time=100.621..107.725 rows=3 loops=1)                                                   |
+|  Workers Planned: 2                                                                                                                                    |
+|  Workers Launched: 2                                                                                                                                   |
+|  ->  Sort  (cost=47470.10..47470.11 rows=2 width=82) (actual time=76.312..76.315 rows=1 loops=3)                                                       |
+|        Sort Key: me.release_date DESC                                                                                                                  |
+|        Sort Method: quicksort  Memory: 25kB                                                                                                            |
+|        Worker 0:  Sort Method: quicksort  Memory: 25kB                                                                                                 |
+|        Worker 1:  Sort Method: quicksort  Memory: 25kB                                                                                                 |
+|        ->  Nested Loop Left Join  (cost=0.84..47470.09 rows=2 width=82) (actual time=55.582..76.259 rows=1 loops=3)                                    |
+|              ->  Nested Loop Left Join  (cost=0.42..47461.22 rows=2 width=66) (actual time=55.561..76.234 rows=1 loops=3)                              |
+|                    ->  Parallel Seq Scan on musical_entities_temp me  (cost=0.00..47444.33 rows=2 width=50) (actual time=55.513..76.174 rows=1 loops=3)|
+|                          Filter: (released_by = 123456)                                                                                                |
+|                          Rows Removed by Filter: 333332                                                                                                |
+|                    ->  Index Scan using temp_songs_pkey on temp_songs s  (cost=0.42..8.44 rows=1 width=16) (actual time=0.049..0.049 rows=1 loops=3)   |
+|                          Index Cond: (id = me.id)                                                                                                      |
+|              ->  Index Only Scan using temp_albums_pkey on temp_albums a  (cost=0.42..4.44 rows=1 width=8) (actual time=0.020..0.020 rows=0 loops=3)   |
+|                    Index Cond: (id = me.id)                                                                                                            |
+|                    Heap Fetches: 0                                                                                                                     |
+|Planning Time: 0.525 ms                                                                                                                                 |
+|Execution Time: 107.796 ms                                                                                                                              |
++--------------------------------------------------------------------------------------------------------------------------------------------------------+
+}}}
+
+Просечното `Execution Time` од 10 извршувања е **100,59 ms**
+
+Со индекс добиваме:
+
+{{{
++----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
+|QUERY PLAN                                                                                                                                                            |
++----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
+|Nested Loop Left Join  (cost=1.27..88.91 rows=5 width=82) (actual time=0.061..0.084 rows=3 loops=1)                                                                   |
+|  ->  Nested Loop Left Join  (cost=0.85..66.72 rows=5 width=66) (actual time=0.026..0.041 rows=3 loops=1)                                                             |
+|        ->  Index Scan using me_released_by_release_date_idx on musical_entities_temp me  (cost=0.42..24.51 rows=5 width=50) (actual time=0.013..0.018 rows=3 loops=1)|
+|              Index Cond: (released_by = 123456)                                                                                                                      |
+|        ->  Index Scan using temp_songs_pkey on temp_songs s  (cost=0.42..8.44 rows=1 width=16) (actual time=0.005..0.005 rows=1 loops=3)                             |
+|              Index Cond: (id = me.id)                                                                                                                                |
+|  ->  Index Only Scan using temp_albums_pkey on temp_albums a  (cost=0.42..4.44 rows=1 width=8) (actual time=0.013..0.013 rows=0 loops=3)                             |
+|        Index Cond: (id = me.id)                                                                                                                                      |
+|        Heap Fetches: 0                                                                                                                                               |
+|Planning Time: 0.491 ms                                                                                                                                               |
+|Execution Time: 0.127 ms                                                                                                                                              |
++----------------------------------------------------------------------------------------------------------------------------------------------------------------------++
+}}}
+
+
+Можеме да забележиме користење на новокреираниот индекс преку `Index Scan using me_released_by_index on musical_entities me`, сега просечното време за 10 извршувања е **0,105 ms**, 
+што претставува значително подобрување на перформансите.
+
 
 == Безбедност и заштита