| 5 | | ... |
| | 5 | Ќе ја разгледуваме процедурата yesterdays_most_popular(), првата од [[AdvancedDatabaseDevelopment|тука]]. |
| | 6 | |
| | 7 | Бидејќи не можеме директно да извршуваме `explain analyze` на процедурата, тоа ќе го правиме на прашалниците кои се повикуваат во неа. |
| | 8 | Со цел да видиме поголема разлика во резултатите, прво генерираме дополнителни податоци. |
| | 9 | |
| | 10 | Додаваме 1000000 нови редови во `listens` табелата. |
| | 11 | {{{ |
| | 12 | insert into listens (listener_id, song_id, timestamp) |
| | 13 | SELECT |
| | 14 | (SELECT l.user_id FROM listeners l ORDER BY random() LIMIT 1), |
| | 15 | (SELECT s.id FROM songs s ORDER BY random() LIMIT 1), |
| | 16 | NOW() - (random() * INTERVAL '8 months') |
| | 17 | FROM generate_series(1, 1000000) |
| | 18 | ON CONFLICT DO NOTHING; |
| | 19 | }}} |
| | 20 | |
| | 21 | Додаваме индекс на timestamp |
| | 22 | {{{ |
| | 23 | CREATE INDEX ON listens(timestamp); |
| | 24 | }}} |
| | 25 | |
| | 26 | Ќе ги споредуваме времињата на извршување пред и по додавање на индексот. Сите прашалници ги извршуваме со `explain analyze`. |
| | 27 | |
| | 28 | Прашалник 1: |
| | 29 | |
| | 30 | {{{ |
| | 31 | EXPLAIN ANALYZE SELECT 1 |
| | 32 | FROM listens |
| | 33 | WHERE timestamp BETWEEN CURRENT_DATE - 1 AND CURRENT_DATE; |
| | 34 | }}} |
| | 35 | |
| | 36 | Без индекс добиваме: |
| | 37 | |
| | 38 | {{{ |
| | 39 | +------------------------------------------------------------------------------------------------------------------------------+ |
| | 40 | |QUERY PLAN | |
| | 41 | +------------------------------------------------------------------------------------------------------------------------------+ |
| | 42 | |Gather (cost=1000.00..17155.54 rows=4428 width=4) (actual time=0.342..58.768 rows=4139 loops=1) | |
| | 43 | | Workers Planned: 2 | |
| | 44 | | Workers Launched: 2 | |
| | 45 | | -> Parallel Seq Scan on listens (cost=0.00..15712.74 rows=1845 width=4) (actual time=0.080..47.575 rows=1380 loops=3) | |
| | 46 | | Filter: (("timestamp" <= CURRENT_DATE) AND ("timestamp" >= (CURRENT_DATE - 1))) | |
| | 47 | | Rows Removed by Filter: 331269 | |
| | 48 | |Planning Time: 0.116 ms | |
| | 49 | |Execution Time: 58.965 ms | |
| | 50 | +------------------------------------------------------------------------------------------------------------------------------+ |
| | 51 | }}} |
| | 52 | |
| | 53 | Просечното `Execution Time` од 10 извршувања е 67.14 ms. |
| | 54 | |
| | 55 | Со индекс добиваме: |
| | 56 | |
| | 57 | {{{ |
| | 58 | +----------------------------------------------------------------------------------------------------------------------------------------------------+ |
| | 59 | |QUERY PLAN | |
| | 60 | +----------------------------------------------------------------------------------------------------------------------------------------------------+ |
| | 61 | |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)| |
| | 62 | | Index Cond: (("timestamp" >= (CURRENT_DATE - 1)) AND ("timestamp" <= CURRENT_DATE)) | |
| | 63 | | Heap Fetches: 0 | |
| | 64 | |Planning Time: 0.177 ms | |
| | 65 | |Execution Time: 0.793 ms | |
| | 66 | +----------------------------------------------------------------------------------------------------------------------------------------------------+ |
| | 67 | }}} |
| | 68 | |
| | 69 | Можеме да забележиме користење на индекс преку `Index Only Scan`, сега просечното време за 10 извршувања е 0.83 ms. Добиваме подобрување на перформансите од ~80x. |
| | 70 | |
| | 71 | |
| | 72 | Прашалник 2: |
| | 73 | |
| | 74 | {{{ |
| | 75 | EXPLAIN ANALYZE SELECT CURRENT_DATE - 1, s.id, COUNT(*) |
| | 76 | FROM songs s |
| | 77 | JOIN listens l ON l.song_id = s.id |
| | 78 | WHERE l.timestamp BETWEEN CURRENT_DATE - 1 AND NOW() |
| | 79 | GROUP BY s.id |
| | 80 | ORDER BY COUNT(*) DESC |
| | 81 | LIMIT 1; |
| | 82 | }}} |
| | 83 | |
| | 84 | Без индекс добиваме: |
| | 85 | {{{ |
| | 86 | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
| | 87 | |QUERY PLAN | |
| | 88 | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
| | 89 | |Limit (cost=17116.49..17116.49 rows=1 width=20) (actual time=86.532..92.337 rows=1 loops=1) | |
| | 90 | | -> Sort (cost=17116.49..17119.16 rows=1070 width=20) (actual time=86.530..92.334 rows=1 loops=1) | |
| | 91 | | Sort Key: (count(*)) DESC | |
| | 92 | | Sort Method: quicksort Memory: 25kB | |
| | 93 | | -> Finalize GroupAggregate (cost=16834.70..17111.14 rows=1070 width=20) (actual time=86.503..92.307 rows=1 loops=1) | |
| | 94 | | Group Key: s.id | |
| | 95 | | -> Gather Merge (cost=16834.70..17084.39 rows=2140 width=16) (actual time=86.492..92.297 rows=3 loops=1) | |
| | 96 | | Workers Planned: 2 | |
| | 97 | | Workers Launched: 2 | |
| | 98 | | -> Sort (cost=15834.68..15837.35 rows=1070 width=16) (actual time=81.267..81.270 rows=1 loops=3) | |
| | 99 | | Sort Key: s.id | |
| | 100 | | Sort Method: quicksort Memory: 25kB | |
| | 101 | | Worker 0: Sort Method: quicksort Memory: 25kB | |
| | 102 | | Worker 1: Sort Method: quicksort Memory: 25kB | |
| | 103 | | -> Partial HashAggregate (cost=15770.14..15780.84 rows=1070 width=16) (actual time=81.226..81.232 rows=1 loops=3) | |
| | 104 | | Group Key: s.id | |
| | 105 | | Batches: 1 Memory Usage: 73kB | |
| | 106 | | Worker 0: Batches: 1 Memory Usage: 73kB | |
| | 107 | | Worker 1: Batches: 1 Memory Usage: 73kB | |
| | 108 | | -> Hash Join (cost=34.08..15754.87 rows=3054 width=8) (actual time=0.138..80.491 rows=2356 loops=3) | |
| | 109 | | Hash Cond: (l.song_id = s.id) | |
| | 110 | | -> 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) | |
| | 111 | | Filter: (("timestamp" <= now()) AND ("timestamp" >= (CURRENT_DATE - 1))) | |
| | 112 | | Rows Removed by Filter: 330293 | |
| | 113 | | -> Hash (cost=20.70..20.70 rows=1070 width=8) (actual time=0.042..0.043 rows=15 loops=3) | |
| | 114 | | Buckets: 2048 Batches: 1 Memory Usage: 17kB | |
| | 115 | | -> Seq Scan on songs s (cost=0.00..20.70 rows=1070 width=8) (actual time=0.029..0.032 rows=15 loops=3) | |
| | 116 | |Planning Time: 0.668 ms | |
| | 117 | |Execution Time: 92.596 ms | |
| | 118 | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
| | 119 | }}} |
| | 120 | |
| | 121 | Просечното `Execution Time` од 10 извршувања е 88.65 ms. |
| | 122 | |
| | 123 | |
| | 124 | Со индекс добиваме: |
| | 125 | {{{ |
| | 126 | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
| | 127 | |QUERY PLAN | |
| | 128 | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
| | 129 | |Limit (cost=13275.20..13275.21 rows=1 width=20) (actual time=10.802..15.147 rows=1 loops=1) | |
| | 130 | | -> Sort (cost=13275.20..13277.88 rows=1070 width=20) (actual time=10.801..15.145 rows=1 loops=1) | |
| | 131 | | Sort Key: (count(*)) DESC | |
| | 132 | | Sort Method: quicksort Memory: 25kB | |
| | 133 | | -> Finalize GroupAggregate (cost=12993.42..13269.85 rows=1070 width=20) (actual time=10.797..15.141 rows=1 loops=1) | |
| | 134 | | Group Key: s.id | |
| | 135 | | -> Gather Merge (cost=12993.42..13243.10 rows=2140 width=16) (actual time=10.787..15.131 rows=3 loops=1) | |
| | 136 | | Workers Planned: 2 | |
| | 137 | | Workers Launched: 2 | |
| | 138 | | -> Sort (cost=11993.40..11996.07 rows=1070 width=16) (actual time=6.038..6.041 rows=1 loops=3) | |
| | 139 | | Sort Key: s.id | |
| | 140 | | Sort Method: quicksort Memory: 25kB | |
| | 141 | | Worker 0: Sort Method: quicksort Memory: 25kB | |
| | 142 | | Worker 1: Sort Method: quicksort Memory: 25kB | |
| | 143 | | -> Partial HashAggregate (cost=11928.86..11939.56 rows=1070 width=16) (actual time=6.008..6.012 rows=1 loops=3) | |
| | 144 | | Group Key: s.id | |
| | 145 | | Batches: 1 Memory Usage: 73kB | |
| | 146 | | Worker 0: Batches: 1 Memory Usage: 73kB | |
| | 147 | | Worker 1: Batches: 1 Memory Usage: 73kB | |
| | 148 | | -> Hash Join (cost=194.78..11913.36 rows=3100 width=8) (actual time=0.937..5.574 rows=2356 loops=3) | |
| | 149 | | Hash Cond: (l.song_id = s.id) | |
| | 150 | | -> 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) | |
| | 151 | | Recheck Cond: (("timestamp" >= (CURRENT_DATE - 1)) AND ("timestamp" <= now())) | |
| | 152 | | Heap Blocks: exact=3438 | |
| | 153 | | -> 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) | |
| | 154 | | Index Cond: (("timestamp" >= (CURRENT_DATE - 1)) AND ("timestamp" <= now())) | |
| | 155 | | -> Hash (cost=20.70..20.70 rows=1070 width=8) (actual time=0.039..0.040 rows=15 loops=3) | |
| | 156 | | Buckets: 2048 Batches: 1 Memory Usage: 17kB | |
| | 157 | | -> Seq Scan on songs s (cost=0.00..20.70 rows=1070 width=8) (actual time=0.028..0.030 rows=15 loops=3) | |
| | 158 | |Planning Time: 0.320 ms | |
| | 159 | |Execution Time: 15.232 ms | |
| | 160 | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
| | 161 | }}} |
| | 162 | |
| | 163 | Просечно време на извршување е 15.04ms -> ~6x подобри перформанси. |
| | 164 | |
| | 165 | Очекувано е перформансите на процедурата да бидат многу подобри со додавањето на овој индекс. |
