| Version 13 (modified by , 7 days ago) ( diff ) |
|---|
Други развојни активности
Анализа на перформанси, додавање индекси
За да направиме анализа на перформансите на нашите прашалници ќе разгледаме неколку различни употреби.
Начинот на тестирање е следниот:
- Додаваме голем број на нови записи во табелите (пр. 1М записи) со цел користењето на индекс да биде исплатливо, доколку истиот е искористен соодветно. Јасно е дека за табела со 20 записи нема да има голема предност доколку се користи индекс.
- Пред додавање на индексот ги извршуваме прашалниците 10 пати користејќи
explain analyze. Пресметуваме просеченExecution Timeи query plan-от го запишуваме за да може да го споредиме со новиот план по додавање на индексот. - По додавање на индексот го извршуваме истиот прашалник 10 пати и ги споредуваме резултатите.
Сценарио 1
Ќе ја разгледуваме процедурата yesterdays_most_popular() - прва од Напреден развој на базата.
Бидејќи не можеме директно да извршуваме 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 од Напредни извештаи - Детален извештај за најпопуларниот артист според месечни слушања за изминатата година.
Од претходните примери веќе имаме генерирано доволно записи во потребните табели.
Додаваме сложен индекс на 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))
Забелешка: Додавање на индекс за 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 да се смени со соодветниот домен/и, а дополнително може и да се ограничат и дозволените методи и заглавја, но за рамките на овој проект сметаме дека ова е доволно.
Note:
See TracWiki
for help on using the wiki.
