= Други развојни активности == Анализа на перформанси, додавање индекси За да направиме анализа на перформансите на нашите прашалници ќе разгледаме неколку различни употреби. Начинот на тестирање е следниот: - Додаваме голем број на нови записи во табелите (пр. 1М записи) со цел користењето на индекс да биде исплатливо, доколку истиот е искористен соодветно. Јасно е дека за табела со 20 записи нема да има голема предност доколку се користи индекс. - Пред додавање на индексот ги извршуваме прашалниците 10 пати користејќи `explain analyze`. Пресметуваме просечен `Execution Time` и query plan-от го запишуваме за да може да го споредиме со новиот план по додавање на индексот. - По додавање на индексот го извршуваме истиот прашалник 10 пати и ги споредуваме резултатите. === Сценарио 1 Ќе ја разгледуваме процедурата yesterdays_most_popular() - прва од [[AdvancedDatabaseDevelopment|Напреден развој на базата]]. Бидејќи не можеме директно да извршуваме `explain analyze` на процедурата, тоа ќе го правиме на прашалниците кои се повикуваат во неа. Додаваме 1М нови редови во `listens` табелата со случајна вредност за `timestamp` во интервал од изминатите 8 месеци. {{{ INSERT INTO listens (listener_id, song_id, timestamp) SELECT (SELECT l.user_id FROM listeners l ORDER BY random() LIMIT 1), (SELECT s.id FROM songs s ORDER BY random() LIMIT 1), NOW() - (random() * INTERVAL '8 months') FROM generate_series(1, 1000000) ON CONFLICT DO NOTHING; }}} Додаваме индекс на `timestamp` {{{ CREATE INDEX ON listens(timestamp); }}} Ќе тестираме 2 прашалници. Прашалник 1: {{{ EXPLAIN ANALYZE SELECT 1 FROM listens WHERE timestamp BETWEEN CURRENT_DATE - 1 AND CURRENT_DATE; }}} Без индекс добиваме: {{{ +------------------------------------------------------------------------------------------------------------------------------+ |QUERY PLAN | +------------------------------------------------------------------------------------------------------------------------------+ |Gather (cost=1000.00..17155.54 rows=4428 width=4) (actual time=0.342..58.768 rows=4139 loops=1) | | Workers Planned: 2 | | Workers Launched: 2 | | -> Parallel Seq Scan on listens (cost=0.00..15712.74 rows=1845 width=4) (actual time=0.080..47.575 rows=1380 loops=3) | | Filter: (("timestamp" <= CURRENT_DATE) AND ("timestamp" >= (CURRENT_DATE - 1))) | | Rows Removed by Filter: 331269 | |Planning Time: 0.116 ms | |Execution Time: 58.965 ms | +------------------------------------------------------------------------------------------------------------------------------+ }}} Просечното `Execution Time` од 10 извршувања е **67,14ms**. Со индекс добиваме: {{{ +----------------------------------------------------------------------------------------------------------------------------------------------------+ |QUERY PLAN | +----------------------------------------------------------------------------------------------------------------------------------------------------+ |Index Only Scan using listens_timestamp_idx on listens (cost=0.43..140.59 rows=4408 width=4) (actual time=0.034..0.670 rows=4139 loops=1) | | Index Cond: (("timestamp" >= (CURRENT_DATE - 1)) AND ("timestamp" <= CURRENT_DATE)) | | Heap Fetches: 0 | |Planning Time: 0.177ms | |Execution Time: 0.793ms | +----------------------------------------------------------------------------------------------------------------------------------------------------+ }}} Можеме да забележиме користење на индекс преку `Index Only Scan`, сега просечното време за 10 извршувања е **0,83ms**. Прашалник 2: {{{ EXPLAIN ANALYZE SELECT CURRENT_DATE - 1, s.id, COUNT(*) FROM songs s JOIN listens l ON l.song_id = s.id WHERE l.timestamp BETWEEN CURRENT_DATE - 1 AND NOW() GROUP BY s.id ORDER BY COUNT(*) DESC LIMIT 1; }}} Без индекс добиваме: {{{ +--------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |QUERY PLAN | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |Limit (cost=17116.49..17116.49 rows=1 width=20) (actual time=86.532..92.337 rows=1 loops=1) | | -> Sort (cost=17116.49..17119.16 rows=1070 width=20) (actual time=86.530..92.334 rows=1 loops=1) | | Sort Key: (count(*)) DESC | | Sort Method: quicksort Memory: 25kB | | -> Finalize GroupAggregate (cost=16834.70..17111.14 rows=1070 width=20) (actual time=86.503..92.307 rows=1 loops=1) | | Group Key: s.id | | -> Gather Merge (cost=16834.70..17084.39 rows=2140 width=16) (actual time=86.492..92.297 rows=3 loops=1) | | Workers Planned: 2 | | Workers Launched: 2 | | -> Sort (cost=15834.68..15837.35 rows=1070 width=16) (actual time=81.267..81.270 rows=1 loops=3) | | Sort Key: s.id | | Sort Method: quicksort Memory: 25kB | | Worker 0: Sort Method: quicksort Memory: 25kB | | Worker 1: Sort Method: quicksort Memory: 25kB | | -> Partial HashAggregate (cost=15770.14..15780.84 rows=1070 width=16) (actual time=81.226..81.232 rows=1 loops=3) | | Group Key: s.id | | Batches: 1 Memory Usage: 73kB | | Worker 0: Batches: 1 Memory Usage: 73kB | | Worker 1: Batches: 1 Memory Usage: 73kB | | -> Hash Join (cost=34.08..15754.87 rows=3054 width=8) (actual time=0.138..80.491 rows=2356 loops=3) | | Hash Cond: (l.song_id = s.id) | | -> Parallel Seq Scan on listens l (cost=0.00..15712.74 rows=3054 width=8) (actual time=0.061..79.620 rows=2356 loops=3) | | Filter: (("timestamp" <= now()) AND ("timestamp" >= (CURRENT_DATE - 1))) | | Rows Removed by Filter: 330293 | | -> Hash (cost=20.70..20.70 rows=1070 width=8) (actual time=0.042..0.043 rows=15 loops=3) | | Buckets: 2048 Batches: 1 Memory Usage: 17kB | | -> Seq Scan on songs s (cost=0.00..20.70 rows=1070 width=8) (actual time=0.029..0.032 rows=15 loops=3) | |Planning Time: 0.668 ms | |Execution Time: 92.596 ms | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------+ }}} Просечното `Execution Time` од 10 извршувања е **88,65ms**. Со индекс добиваме: {{{ +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |QUERY PLAN | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |Limit (cost=13275.20..13275.21 rows=1 width=20) (actual time=10.802..15.147 rows=1 loops=1) | | -> Sort (cost=13275.20..13277.88 rows=1070 width=20) (actual time=10.801..15.145 rows=1 loops=1) | | Sort Key: (count(*)) DESC | | Sort Method: quicksort Memory: 25kB | | -> Finalize GroupAggregate (cost=12993.42..13269.85 rows=1070 width=20) (actual time=10.797..15.141 rows=1 loops=1) | | Group Key: s.id | | -> Gather Merge (cost=12993.42..13243.10 rows=2140 width=16) (actual time=10.787..15.131 rows=3 loops=1) | | Workers Planned: 2 | | Workers Launched: 2 | | -> Sort (cost=11993.40..11996.07 rows=1070 width=16) (actual time=6.038..6.041 rows=1 loops=3) | | Sort Key: s.id | | Sort Method: quicksort Memory: 25kB | | Worker 0: Sort Method: quicksort Memory: 25kB | | Worker 1: Sort Method: quicksort Memory: 25kB | | -> Partial HashAggregate (cost=11928.86..11939.56 rows=1070 width=16) (actual time=6.008..6.012 rows=1 loops=3) | | Group Key: s.id | | Batches: 1 Memory Usage: 73kB | | Worker 0: Batches: 1 Memory Usage: 73kB | | Worker 1: Batches: 1 Memory Usage: 73kB | | -> Hash Join (cost=194.78..11913.36 rows=3100 width=8) (actual time=0.937..5.574 rows=2356 loops=3) | | Hash Cond: (l.song_id = s.id) | | -> Parallel Bitmap Heap Scan on listens l (cost=160.70..11871.11 rows=3100 width=8) (actual time=0.879..4.934 rows=2356 loops=3) | | Recheck Cond: (("timestamp" >= (CURRENT_DATE - 1)) AND ("timestamp" <= now())) | | Heap Blocks: exact=3438 | | -> Bitmap Index Scan on listens_timestamp_idx (cost=0.00..158.84 rows=7441 width=0) (actual time=1.735..1.735 rows=7067 loops=1) | | Index Cond: (("timestamp" >= (CURRENT_DATE - 1)) AND ("timestamp" <= now())) | | -> Hash (cost=20.70..20.70 rows=1070 width=8) (actual time=0.039..0.040 rows=15 loops=3) | | Buckets: 2048 Batches: 1 Memory Usage: 17kB | | -> Seq Scan on songs s (cost=0.00..20.70 rows=1070 width=8) (actual time=0.028..0.030 rows=15 loops=3) | |Planning Time: 0.320 ms | |Execution Time: 15.232 ms | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ }}} Просечно време на извршување е **15,04ms**. Во планот забележуваме замена на секвенцијално скенирање со скенирање на индекс. Заклучуваме дека перформансите на процедурата ќе бидат многу подобри со овој индекс и го додаваме истиот. == Сценарио 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 табелата, преку извршување на следниот прашалник повеќепати: {{{ INSERT INTO musical_entities (released_by) SELECT id FROM users ORDER BY random() LIMIT 50000; }}} Креираме композитен индекс на `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 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 songs_pkey on 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 albums_pkey on 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,5 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 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 songs_pkey on 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 albums_pkey on 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`, како и отсуството на ` Sort (cost=47470.10..47470.11 rows=2 width=82) (actual time=76.312..76.315 rows=1 loops=3)`, сега просечното време за 10 извршувања е **0,105ms**, што претставува значително подобрување на перформансите. === Сценарио 3 Ќе разгледуваме прашалник кој ги наоѓа сите песни кои припаѓаат на одреден албум. {{{ SELECT * FROM songs s LEFT JOIN albums a ON a.id = s.album_id WHERE s.album_id = :albumId; }}} Од претходните примери веќе имаме генерирано доволно записи во потребните табели. Додаваме индекс на `album_id` {{{ CREATE INDEX ON songs(album_id); }}} Без индекс добиваме: {{{ +-------------------------------------------------------------------------------------------------------------------------------------+ |QUERY PLAN | +-------------------------------------------------------------------------------------------------------------------------------------+ |Nested Loop Left Join (cost=1000.15..11058.40 rows=5 width=36) (actual time=5.216..57.666 rows=3 loops=1) | | -> Gather (cost=1000.00..11050.17 rows=5 width=28) (actual time=5.196..57.634 rows=3 loops=1) | | Workers Planned: 2 | | Workers Launched: 2 | | -> Parallel Seq Scan on songs s (cost=0.00..10049.67 rows=2 width=28) (actual time=11.406..27.456 rows=1 loops=3) | | Filter: (album_id = 5494023) | | Rows Removed by Filter: 266666 | | -> Materialize (cost=0.15..8.18 rows=1 width=8) (actual time=0.006..0.006 rows=0 loops=3) | | -> Index Only Scan using albums_pkey on albums a (cost=0.15..8.17 rows=1 width=8) (actual time=0.012..0.013 rows=0 loops=1)| | Index Cond: (id = 5494023) | | Heap Fetches: 0 | |Planning Time: 0.191 ms | |Execution Time: 57.726 ms | +-------------------------------------------------------------------------------------------------------------------------------------+ }}} Просечно `Execution time` е **56,24ms**. По додавање на индекс: {{{ +-------------------------------------------------------------------------------------------------------------------------------------+ |QUERY PLAN | +-------------------------------------------------------------------------------------------------------------------------------------+ |Nested Loop Left Join (cost=4.62..32.33 rows=5 width=36) (actual time=0.048..0.054 rows=3 loops=1) | | -> Bitmap Heap Scan on songs s (cost=4.46..24.09 rows=5 width=28) (actual time=0.036..0.041 rows=3 loops=1) | | Recheck Cond: (album_id = 5494023) | | Heap Blocks: exact=3 | | -> Bitmap Index Scan on idx_songs_album (cost=0.00..4.46 rows=5 width=0) (actual time=0.024..0.024 rows=3 loops=1) | | Index Cond: (album_id = 5494023) | | -> Materialize (cost=0.15..8.18 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=3) | | -> Index Only Scan using albums_pkey on albums a (cost=0.15..8.17 rows=1 width=8) (actual time=0.007..0.007 rows=0 loops=1)| | Index Cond: (id = 5494023) | | Heap Fetches: 0 | |Planning Time: 0.155 ms | |Execution Time: 0.090 ms | +-------------------------------------------------------------------------------------------------------------------------------------+ }}} Просечно `Execution time` е сега **0.092ms**. Можеме да забележиме користење на индекс преку `Bitmap Index Scan on idx_songs_album`. === Сценарио 4 Последно, ќе го разгледаме извештај број 4 од [[AdvancedReports|Напредни извештаи]] - Детален извештај за најпопуларниот артист според месечни слушања за изминатата година. Од претходните примери веќе имаме генерирано доволно записи во потребните табели. Додаваме сложен индекс на `song_id` и `timestamp` во табелата `listens`. {{{ CREATE INDEX listens_song_timestamp_idx ON listens (song_id, timestamp); }}} Без индекс добиваме: {{{ +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |QUERY PLAN | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |Sort (cost=51421.16..51421.23 rows=28 width=76) (actual time=2315.566..2315.578 rows=12 loops=1) | | Sort Key: g.n | | Sort Method: quicksort Memory: 25kB | | CTE one_year | | -> Result (cost=0.00..0.03 rows=1 width=16) (actual time=0.009..0.010 rows=1 loops=1) | | CTE monthly_artist_listens | | -> GroupAggregate (cost=37772.47..40267.47 rows=110889 width=48) (actual time=1931.028..2315.007 rows=21 loops=1) | | Group Key: (EXTRACT(month FROM l."timestamp")), a.user_id | | -> Sort (cost=37772.47..38049.69 rows=110889 width=48) (actual time=1931.015..2130.522 rows=820263 loops=1) | | Sort Key: (EXTRACT(month FROM l."timestamp")), a.user_id | | Sort Method: external merge Disk: 27352kB | | -> Hash Join (cost=2875.38..21655.66 rows=110889 width=48) (actual time=92.684..909.499 rows=820263 loops=1) | | Hash Cond: (me.released_by = a.user_id) | | -> Hash Join (cost=2814.53..21025.96 rows=110889 width=16) (actual time=92.643..618.359 rows=820263 loops=1) | | Hash Cond: (l.song_id = me.id) | | -> Nested Loop (cost=2813.04..20677.31 rows=110889 width=16) (actual time=92.618..444.931 rows=820263 loops=1) | | -> CTE Scan on one_year oy_1 (cost=0.00..0.02 rows=1 width=16) (actual time=0.000..0.003 rows=1 loops=1) | | -> Bitmap Heap Scan on listens l (cost=2813.04..19568.40 rows=110889 width=16) (actual time=92.615..348.352 rows=820263 loops=1) | | Recheck Cond: (("timestamp" >= oy_1.year_start) AND ("timestamp" <= oy_1.year_end)) | | Rows Removed by Index Recheck: 62048 | | Heap Blocks: exact=4139 lossy=2218 | | -> Bitmap Index Scan on listens_timestamp_idx (cost=0.00..2785.32 rows=110889 width=0) (actual time=91.946..91.946 rows=820263 loops=1)| | Index Cond: (("timestamp" >= oy_1.year_start) AND ("timestamp" <= oy_1.year_end)) | | -> Hash (cost=1.22..1.22 rows=22 width=16) (actual time=0.016..0.017 rows=23 loops=1) | | Buckets: 1024 Batches: 1 Memory Usage: 10kB | | -> Seq Scan on musical_entities me (cost=0.00..1.22 rows=22 width=16) (actual time=0.008..0.011 rows=23 loops=1) | | -> Hash (cost=32.60..32.60 rows=2260 width=8) (actual time=0.027..0.027 rows=10 loops=1) | | Buckets: 4096 Batches: 1 Memory Usage: 33kB | | -> Seq Scan on artists a (cost=0.00..32.60 rows=2260 width=8) (actual time=0.022..0.023 rows=10 loops=1) | | CTE max_per_month | | -> GroupAggregate (cost=0.00..2774.22 rows=200 width=40) (actual time=1931.044..2315.071 rows=9 loops=1) | | Group Key: mal_1.month | | -> CTE Scan on monthly_artist_listens mal_1 (cost=0.00..2217.78 rows=110889 width=40) (actual time=1931.030..2315.036 rows=21 loops=1) | | -> Nested Loop (cost=0.27..8378.76 rows=28 width=76) (actual time=1987.811..2315.549 rows=12 loops=1) | | -> CTE Scan on one_year oy (cost=0.00..0.02 rows=1 width=8) (actual time=0.011..0.012 rows=1 loops=1) | | -> Nested Loop Left Join (cost=0.27..8378.11 rows=28 width=25) (actual time=1987.766..2315.391 rows=12 loops=1) | | Join Filter: (mal.user_id = u.user_id) | | Rows Removed by Join Filter: 166 | | -> Hash Right Join (cost=0.27..8361.41 rows=28 width=20) (actual time=1987.723..2315.283 rows=12 loops=1) | | Hash Cond: (mal.month = (g.n)::numeric) | | -> Merge Anti Join (cost=0.00..8353.92 rows=462 width=48) (actual time=1987.698..2315.225 rows=9 loops=1) | | Merge Cond: (mal.month = mal1.month) | | Join Filter: (mal.user_id > mal1.user_id) | | Rows Removed by Join Filter: 10 | | -> Merge Join (cost=0.00..4162.84 rows=554 width=48) (actual time=1931.055..1931.092 rows=10 loops=1) | | Merge Cond: (mpm.month = mal.month) | | Join Filter: (mal.count = mpm.max_month_counter) | | Rows Removed by Join Filter: 11 | | -> CTE Scan on max_per_month mpm (cost=0.00..4.00 rows=200 width=40) (actual time=1931.045..1931.048 rows=9 loops=1) | | -> Materialize (cost=0.00..2495.00 rows=110889 width=48) (actual time=0.003..0.015 rows=21 loops=1) | | -> CTE Scan on monthly_artist_listens mal (cost=0.00..2217.78 rows=110889 width=48) (actual time=0.001..0.006 rows=21 loops=1) | | -> Materialize (cost=0.00..4164.22 rows=554 width=40) (actual time=0.006..384.106 rows=12 loops=1) | | -> Merge Join (cost=0.00..4162.84 rows=554 width=40) (actual time=0.004..384.097 rows=10 loops=1) | | Merge Cond: (mpm1.month = mal1.month) | | Join Filter: (mal1.count = mpm1.max_month_counter) | | Rows Removed by Join Filter: 11 | | -> CTE Scan on max_per_month mpm1 (cost=0.00..4.00 rows=200 width=40) (actual time=0.000..384.037 rows=9 loops=1) | | -> Materialize (cost=0.00..2495.00 rows=110889 width=48) (actual time=0.001..0.013 rows=21 loops=1) | | -> CTE Scan on monthly_artist_listens mal1 (cost=0.00..2217.78 rows=110889 width=48) (actual time=0.000..0.006 rows=21 loops=1) | | -> Hash (cost=0.12..0.12 rows=12 width=4) (actual time=0.012..0.013 rows=12 loops=1) | | Buckets: 1024 Batches: 1 Memory Usage: 9kB | | -> Function Scan on generate_series g (cost=0.00..0.12 rows=12 width=4) (actual time=0.005..0.006 rows=12 loops=1) | | -> Materialize (cost=0.00..2.51 rows=34 width=21) (actual time=0.003..0.006 rows=15 loops=12) | | -> Seq Scan on users u (cost=0.00..2.34 rows=34 width=21) (actual time=0.033..0.046 rows=20 loops=1) | |Planning Time: 0.873 ms | |Execution Time: 2321.677 ms | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ }}} Просечното `Execution Time` од 10 извршувања е **2248ms**. Можеме да забележиме дека тука се користи индексот кој претходно го додадовме на `timestamp`, но веднаш потоа е потребен атрибутот `song_id` на табелата `listens` за да се направи join, па за тоа се користи `Bitmap Heap Scan`. Додавање на индексот треба да го реши овој проблем. По додавање на индекс: {{{ +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |QUERY PLAN | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |Sort (cost=55060.92..55060.99 rows=28 width=76) (actual time=1452.660..1452.672 rows=12 loops=1) | | Sort Key: g.n | | Sort Method: quicksort Memory: 25kB | | CTE one_year | | -> Result (cost=0.00..0.03 rows=1 width=16) (actual time=0.009..0.010 rows=1 loops=1) | | CTE monthly_artist_listens | | -> GroupAggregate (cost=16.41..35570.90 rows=110889 width=48) (actual time=0.248..1452.324 rows=21 loops=1) | | Group Key: a.user_id, (EXTRACT(month FROM l."timestamp")) | | -> Incremental Sort (cost=16.41..33353.12 rows=110889 width=48) (actual time=0.242..1287.613 rows=820263 loops=1) | | Sort Key: a.user_id, (EXTRACT(month FROM l."timestamp")) | | Presorted Key: a.user_id | | Full-sort Groups: 1 Sort Method: quicksort Average Memory: 27kB Peak Memory: 27kB | | Pre-sorted Groups: 5 Sort Methods: quicksort, external merge Average Memory: 20kB Peak Memory: 25kB Average Disk: 5464kB Peak Disk: 27320kB | | -> Nested Loop (cost=2.29..28807.69 rows=110889 width=48) (actual time=0.080..393.816 rows=820263 loops=1) | | -> Merge Join (cost=1.87..89.75 rows=22 width=16) (actual time=0.048..0.072 rows=23 loops=1) | | Merge Cond: (a.user_id = me.released_by) | | -> Index Only Scan using artists_pkey on artists a (cost=0.15..82.06 rows=2260 width=8) (actual time=0.019..0.027 rows=10 loops=1) | | Heap Fetches: 10 | | -> Sort (cost=1.71..1.77 rows=22 width=16) (actual time=0.026..0.030 rows=23 loops=1) | | Sort Key: me.released_by | | Sort Method: quicksort Memory: 25kB | | -> Seq Scan on musical_entities me (cost=0.00..1.22 rows=22 width=16) (actual time=0.013..0.017 rows=23 loops=1) | | -> Nested Loop (cost=0.42..1070.98 rows=22178 width=16) (actual time=0.005..8.666 rows=35664 loops=23) | | -> CTE Scan on one_year oy_1 (cost=0.00..0.02 rows=1 width=16) (actual time=0.000..0.000 rows=1 loops=23) | | -> Index Only Scan using listens_song_timestamp_idx on listens l (cost=0.42..849.18 rows=22178 width=16) (actual time=0.005..5.489 rows=35664 loops=23)| | Index Cond: ((song_id = me.id) AND ("timestamp" >= oy_1.year_start) AND ("timestamp" <= oy_1.year_end)) | | Heap Fetches: 62 | | CTE max_per_month | | -> Sort (cost=2781.87..2782.37 rows=200 width=40) (actual time=1452.185..1452.186 rows=9 loops=1) | | Sort Key: mal_1.month | | Sort Method: quicksort Memory: 25kB | | -> HashAggregate (cost=2772.22..2774.22 rows=200 width=40) (actual time=1452.171..1452.175 rows=9 loops=1) | | Group Key: mal_1.month | | Batches: 1 Memory Usage: 40kB | | -> CTE Scan on monthly_artist_listens mal_1 (cost=0.00..2217.78 rows=110889 width=40) (actual time=0.000..1452.107 rows=21 loops=1) | | -> Hash Left Join (cost=16698.15..16706.94 rows=28 width=76) (actual time=1452.627..1452.654 rows=12 loops=1) | | Hash Cond: (mal.user_id = u.user_id) | | -> Hash Right Join (cost=16695.39..16703.75 rows=28 width=28) (actual time=1452.560..1452.574 rows=12 loops=1) | | Hash Cond: (mal.month = (g.n)::numeric) | | -> Sort (cost=16694.98..16696.13 rows=462 width=48) (actual time=1452.522..1452.526 rows=9 loops=1) | | Sort Key: mal.month | | Sort Method: quicksort Memory: 25kB | | -> Hash Anti Join (cost=8343.14..16674.53 rows=462 width=48) (actual time=1452.502..1452.517 rows=9 loops=1) | | Hash Cond: (mal.month = mal1.month) | | Join Filter: (mal.user_id > mal1.user_id) | | Rows Removed by Join Filter: 11 | | -> Hash Join (cost=7.00..8329.22 rows=554 width=48) (actual time=1452.458..1452.468 rows=10 loops=1) | | Hash Cond: ((mal.month = mpm.month) AND (mal.count = mpm.max_month_counter)) | | -> CTE Scan on monthly_artist_listens mal (cost=0.00..2217.78 rows=110889 width=48) (actual time=0.249..0.252 rows=21 loops=1) | | -> Hash (cost=4.00..4.00 rows=200 width=40) (actual time=1452.199..1452.199 rows=9 loops=1) | | Buckets: 1024 Batches: 1 Memory Usage: 9kB | | -> CTE Scan on max_per_month mpm (cost=0.00..4.00 rows=200 width=40) (actual time=1452.187..1452.191 rows=9 loops=1) | | -> Hash (cost=8329.22..8329.22 rows=554 width=40) (actual time=0.028..0.029 rows=10 loops=1) | | Buckets: 1024 Batches: 1 Memory Usage: 9kB | | -> Hash Join (cost=7.00..8329.22 rows=554 width=40) (actual time=0.015..0.025 rows=10 loops=1) | | Hash Cond: ((mal1.month = mpm1.month) AND (mal1.count = mpm1.max_month_counter)) | | -> CTE Scan on monthly_artist_listens mal1 (cost=0.00..2217.78 rows=110889 width=48) (actual time=0.001..0.003 rows=21 loops=1) | | -> Hash (cost=4.00..4.00 rows=200 width=40) (actual time=0.006..0.006 rows=9 loops=1) | | Buckets: 1024 Batches: 1 Memory Usage: 9kB | | -> CTE Scan on max_per_month mpm1 (cost=0.00..4.00 rows=200 width=40) (actual time=0.001..0.002 rows=9 loops=1) | | -> Hash (cost=0.26..0.26 rows=12 width=12) (actual time=0.028..0.029 rows=12 loops=1) | | Buckets: 1024 Batches: 1 Memory Usage: 9kB | | -> Nested Loop (cost=0.00..0.26 rows=12 width=12) (actual time=0.019..0.023 rows=12 loops=1) | | -> CTE Scan on one_year oy (cost=0.00..0.02 rows=1 width=8) (actual time=0.012..0.012 rows=1 loops=1) | | -> Function Scan on generate_series g (cost=0.00..0.12 rows=12 width=4) (actual time=0.005..0.006 rows=12 loops=1) | | -> Hash (cost=2.34..2.34 rows=34 width=21) (actual time=0.038..0.038 rows=20 loops=1) | | Buckets: 1024 Batches: 1 Memory Usage: 10kB | | -> Seq Scan on users u (cost=0.00..2.34 rows=34 width=21) (actual time=0.027..0.032 rows=20 loops=1) | |Planning Time: 0.821 ms | |Execution Time: 1458.530 ms | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ }}} Просечното време е сега **1412ms**. Можеме да забележиме дека engine-от во едно поминување на индексот ги собира сите потребни редици. {{{ -> Index Only Scan using listens_song_timestamp_idx on listens l (cost=0.42..849.18 rows=22178 width=16) (actual time=0.005..5.489 rows=35664 loops=23) Index Cond: ((song_id = me.id) AND ("timestamp" >= oy_1.year_start) AND ("timestamp" <= oy_1.year_end)) }}} [[br]] **Забелешка**: Додавање на индекс за `listens(timestamp)` и `listens(song_id, timestamp)` може да се смета за делумно редундантно бидејќи во нашите случаи можат да се заменат со единствен сложен индекс на `listens(timestamp, song_id)`. Секако, редоследот на колоните во сложениот индекс има влијание врз перформансите. Во прашалникот кој го тестираме во сценарио 4 и двата пристапи покажаа подобрување во споредба со случајот без индекс, но перформансите со индексот `listens(song_id, timestamp)` беа подобри. Од друга страна, чување на два одделни индекси наместо еден троши повеќе меморија и го зголемува потребното време за INSERT операции. Станува збор за trade-off кој може дополнително да се дискутира. Ние одлучивме да чуваме два посебни индекси. == Безбедност и заштита === JWT На најавени корисници на нашата апликација серверот им издава access token и refresh token. Access токенот е валиден многу кратко (10 минути) со цел дури и да е превземен од малициозен корисник истиот да стане бескорисен за брзо време. Овој токен е потребен за пристап до содржини и податоци коишто не се достапни за други корисници (пр. преглед/промена на лични податоци) Refresh токенот е валиден подолго време (14 дена) и се чува во база, за кога на корисникот ќе му истече access токенот тој да може да го докаже својот идентитет на серверот преку refresh токенот, и тогаш серверот да му издаде нов access токен. {{{ public String refreshAccessToken(String refreshTokenString) { RefreshToken refreshToken = refreshTokenService.validateRefreshToken(refreshTokenString); User user = refreshToken.getUser(); return jwtService.generateToken(user.getUsername(), user.getRole().name()); } }}} Проверка за валидност на refresh токен {{{ public RefreshToken validateRefreshToken(String token){ RefreshToken refreshToken = findByToken(token) .orElseThrow(() -> new InvalidTokenException("Invalid refresh token.")); if (refreshToken.isRevoked()){ throw new InvalidTokenException("Refresh token has been revoked."); } if (refreshToken.getExpiresAt().isBefore(Instant.now())){ throw new InvalidTokenException("Refresh token has expired."); } return refreshToken; } }}} Токенот е потпишан од серверот користејќи таен клуч со цел да не може малициозен корисник сам да си издаде свој токен. Метода за генерирање токен: {{{ public String generateToken(String username, String role){ SecretKey key = Keys.hmacShaKeyFor(authProperties.getSecret().getBytes(StandardCharsets.UTF_8)); return Jwts.builder() .setSubject(username) .claim("role", "ROLE_" + role) .setExpiration(new Date(System.currentTimeMillis() + (long) authProperties.getAccessTokenMaxAge() * 1000)) .signWith(key) .compact(); } }}} Дополнително, имплементираме наш филтер кој ќе го проверува постоењето и валидноста на горенаведениот access токен: {{{ @Component @RequiredArgsConstructor public class JwtFilter extends OncePerRequestFilter { private final CustomUserDetailsService userDetailsService; private final JwtService jwtService; @Override protected void doFilterInternal( @NonNull HttpServletRequest request, @NonNull HttpServletResponse response, @NonNull FilterChain filterChain) throws ServletException, IOException { String token = null; if (request.getCookies() != null){ for (Cookie cookie: request.getCookies()){ if ("accessToken".equals(cookie.getName())){ token = cookie.getValue(); break; } } } if (token != null && !token.isEmpty()){ try { Claims claims = jwtService.extractClaims(token); String username = claims.getSubject(); if (username != null && SecurityContextHolder.getContext().getAuthentication() == null){ UserDetails userDetails = userDetailsService.loadUserByUsername(username); UsernamePasswordAuthenticationToken authToken = new UsernamePasswordAuthenticationToken(username, null, userDetails.getAuthorities()); SecurityContextHolder.getContext().setAuthentication(authToken); } } catch (ExpiredJwtException e){ System.out.println("Expired jwt token."); response.setStatus(HttpStatus.UNAUTHORIZED.value()); return; } catch (JwtException e){ System.out.println("Invalid jwt token."); response.setStatus(HttpStatus.UNAUTHORIZED.value()); return; } catch (Exception e){ System.out.println(e.getMessage()); response.setStatus(HttpStatus.UNAUTHORIZED.value()); return; } } filterChain.doFilter(request, response); } } }}} Потоа, истиот треба да го додадеме во security конфигурацијата {{{ @Bean public SecurityFilterChain securityFilterChain(HttpSecurity http) throws Exception { http .cors(cors -> cors.configurationSource(corsConfigurationSource())) .csrf(AbstractHttpConfigurer::disable) .authorizeHttpRequests(auth -> auth .requestMatchers("/auth/user").authenticated() .anyRequest().permitAll()) .sessionManagement(session -> session.sessionCreationPolicy(SessionCreationPolicy.STATELESS)) .addFilterBefore(jwtFilter, UsernamePasswordAuthenticationFilter.class) // <---- оваа линија е битна !!!! .headers((headers) -> headers.frameOptions(HeadersConfigurer.FrameOptionsConfig::sameOrigin)); return http.build(); } }}} === Чување лозинки Бидејќи чуваме лозинки во нашата база не смееме истите да ги чуваме како plaintext. Корстиме `BCryptPasswordEncoder` за хеширање на истите: {{{ @Bean public PasswordEncoder passwordEncoder(){ return new BCryptPasswordEncoder(10); } // понатаму кога создаваме нов корисник User.UserBuilder userBuilder = User.builder() .username(authRequestDto.username()) .password(passwordEncoder.encode(authRequestDto.password())) // ... останата логика }}} === SQL Injection За да намалиме можност за SQL injection користиме параметаризирани прашалници, наместо да ги додаваме параметрите со конкатенација. Всушност, бидејќи користиме Spring Data JPA / JPQL овие работи не треба експлицитно да ги пишуваме, туку се веќе имплементирани: пример: {{{ @Query(""" SELECT CASE WHEN COUNT (l)>0 THEN true ELSE false END FROM MusicalEntity me JOIN Like l on l.musicalEntity.id=me.id WHERE l.listener.id=:userId """) boolean isLikedByUser(@Param("userId") Long userId); }}} === CORS Конфигурација за CORS {{{ @Bean public CorsConfigurationSource corsConfigurationSource() { CorsConfiguration configuration = new CorsConfiguration(); configuration.setAllowedOriginPatterns(List.of("http://localhost:*")); configuration.setAllowedMethods(List.of("GET", "POST", "PUT", "DELETE", "OPTIONS")); configuration.setAllowedHeaders(List.of("*")); configuration.setAllowCredentials(true); UrlBasedCorsConfigurationSource source = new UrlBasedCorsConfigurationSource(); source.registerCorsConfiguration("/**", configuration); return source; } }}} Во продукциска околина, секако дека ќе треба `localhost` да се смени со соодветниот домен/и, а дополнително може и да се ограничат и дозволените методи и заглавја, но за рамките на овој проект сметаме дека ова е доволно.