Changes between Version 76 and Version 77 of QueryOptimization

Timestamp:

06/30/26 00:28:55 (6 days ago)

Author:

231027

Comment:

--

QueryOptimization

@@ -419 +419 @@
 ||Planning Time: 0.120 ms||
 ||Execution Time: 0.135 ms||
+
+
+
+
+
+
+
+== Анализа и оптимизација на `Future_Events`
+
+Овој поглед служи за динамично генерирање на репертоарот, прикажувајќи ги исклучиво претстојните настани преку филтрирање на изминатите термини во однос на моменталното време на системот. Дополнително, тој ја прикажува комплетната географска адреса и локација на објектот каде ќе се одржи настанот.
+
+{{{
+
+CREATE OR REPLACE VIEW "Future_Events" AS
+SELECT
+    e.event_id,
+    e.name AS event_name,
+    eh.event_happening_id,
+    eh.event_time,
+    v.venue_id,
+    v.name AS venue_name,
+    v.address_street AS street,
+    v.address_city AS city,
+    v.address_country AS country
+FROM "Event" e
+JOIN "Event_Happening" eh ON e.event_id = eh.event_id
+JOIN "Venue" v ON eh.venue_id = v.venue_id
+WHERE eh.event_time > CURRENT_TIMESTAMP;
+
+}}}
+
+==== 1. Примарен филтер:
+
+Примарниот филтер е колоната `event_time` во табелата `Event_Happening`. Погледот постојано го бара само подмножеството на записи кои се во иднина.
+
+==== 2. Случај на употреба:
+
+Овој поглед е „лицето“ на платформата. Се користи при вчитување на листата на претстојни настани за корисниците. Ефикасноста овде директно го дефинира времето на првично вчитување на апликацијата.
+
+==== 3. Иницијално време:
+
+ * '''SELECT:''' 100.080 ms (Бавно поради '''Seq Scan''' низ илјадници записи во `Event_Happening` за проверка на времето).
+ * '''INSERT:''' 12.452 ms (Процесирањето на тригерите и запишувањето на податокот).
+ * '''UPDATE:''' 0.104 ms (Инстантна брзина преку '''primary key''').
+
+==== 4. Анализа на планот на извршување (без индекси):
+
+Без индекс на времето, базата мораше да прави '''Sequential Scan''' низ целата табела `Event_Happening` (12,973 записи), што резултираше со бавно филтрирање во меморијата ('''Filter: `event_time > CURRENT_TIMESTAMP`'''). Дополнително, базата трошеше непотребни 466 читања од диск за да ја провери секоја редица.
+
+ * '''SELECT'''
+
+{{{
+
+EXPLAIN ANALYZE
+SELECT * FROM "Future_Events"
+WHERE city = 'London';
+
+}}}
+
+||= QUERY PLAN =||
+||Nested Loop  (cost\=263.49..1377.68 rows\=332 width\=121) (actual time\=3.243..100.080 rows\=340.00 loops\=1)||
+||  Buffers: shared hit\=778 read\=844||
+||  ->  Hash Join  (cost\=263.20..1234.31 rows\=332 width\=90) (actual time\=2.695..18.025 rows\=340.00 loops\=1)||
+||        Hash Cond: (eh.venue_id \= v.venue_id)||
+||        Buffers: shared hit\=2 read\=600||
+||        ->  Seq Scan on ""Event_Happening"" eh  (cost\=0.00..937.09 rows\=12957 width\=32) (actual time\=0.154..12.105 rows\=12973.00 loops\=1)||
+||              Filter: (event_time > CURRENT_TIMESTAMP)||
+||              Rows Removed by Filter: 18365||
+||              Buffers: shared hit\=1 read\=466||
+||        ->  Hash  (cost\=260.00..260.00 rows\=256 width\=66) (actual time\=2.516..2.517 rows\=256.00 loops\=1)||
+||              Buckets: 1024  Batches: 1  Memory Usage: 33kB||
+||              Buffers: shared hit\=1 read\=134||
+||              ->  Seq Scan on ""Venue"" v  (cost\=0.00..260.00 rows\=256 width\=66) (actual time\=0.222..2.393 rows\=256.00 loops\=1)||
+||                    Filter: ((address_city)::text \= 'London'::text)||
+||                    Rows Removed by Filter: 9744||
+||                    Buffers: shared hit\=1 read\=134||
+||  ->  Index Scan using ""Event_pkey"" on ""Event"" e  (cost\=0.29..0.43 rows\=1 width\=39) (actual time\=0.237..0.237 rows\=1.00 loops\=340)||
+||        Index Cond: (event_id \= eh.event_id)||
+||        Index Searches: 340||
+||        Buffers: shared hit\=776 read\=244||
+||Planning:||
+||  Buffers: shared hit\=31 read\=8||
+||Planning Time: 0.659 ms||
+||Execution Time: 100.249 ms||
+
+ * '''INSERT'''
+
+{{{
+
+EXPLAIN ANALYZE
+INSERT INTO "Event_Happening" (event_happening_id, event_id, venue_id, event_time, duration_minutes, organizers)
+VALUES (99999999, 1, 1, '2026-12-31 20:00:00', 120, 'Avalon Production');
+
+}}}
+
+||= QUERY PLAN =||
+||Insert on ""Event_Happening""  (cost\=0.00..0.01 rows\=0 width\=0) (actual time\=12.004..12.005 rows\=0.00 loops\=1)||
+||  Buffers: shared hit\=4 read\=5 dirtied\=1||
+||  ->  Result  (cost\=0.00..0.01 rows\=1 width\=100) (actual time\=0.002..0.003 rows\=1.00 loops\=1)||
+||Planning Time: 0.047 ms||
+||Trigger for constraint fk_happening_event: time\=0.276 calls\=1||
+||Trigger for constraint fk_happening_venue: time\=0.134 calls\=1||
+||Execution Time: 12.452 ms||
+
+ * '''UPDATE'''
+
+{{{
+
+EXPLAIN ANALYZE
+UPDATE "Event_Happening"
+SET event_time = '2027-01-01 21:00:00'
+WHERE event_happening_id = 99999999;
+
+}}}
+
+||= QUERY PLAN =||
+||Update on ""Event_Happening""  (cost\=0.29..8.30 rows\=0 width\=0) (actual time\=0.072..0.072 rows\=0.00 loops\=1)||
+||  Buffers: shared hit\=12||
+||  ->  Index Scan using ""Event_Happening_pkey"" on ""Event_Happening""  (cost\=0.29..8.30 rows\=1 width\=14) (actual time\=0.023..0.024 rows\=1.00 loops\=1)||
+||        Index Cond: (event_happening_id \= 99999999)||
+||        Index Searches: 1||
+||        Buffers: shared hit\=3||
+||Planning Time: 0.089 ms||
+||Execution Time: 0.104 ms||
+
+==== 5. Оптимизација и индексирање:
+
+За да се елиминира потребата од пребарување на целата табела, креиравме '''B-tree''' индекс врз колоната `event_time`. Ова му овозможува на планерот да го лоцира временскиот „праг“ моментално.
+
+{{{
+
+CREATE INDEX CONCURRENTLY idx_event_happening_time
+ON "Event_Happening" (event_time);
+
+
+ANALYZE "Event_Happening";
+
+}}}
+
+==== 6. Резултат по оптимизација:
+
+Со воведувањето на индексот, базата премина на '''Bitmap Index Scan''', што значи дека ја чита само релевантната мапа на записи наместо целиот диск.
+
+ * Времето за '''SELECT''' падна на 8.451 ms, што е приближно 12 пати побрзо.
+ * Времето за '''INSERT''' падна на 0.458 ms (околу 27 пати побрзо), бидејќи индексната структура се ажурира ефикасно.
+
+ * '''SELECT'''
+
+{{{
+
+EXPLAIN ANALYZE
+SELECT * FROM "Future_Events"
+WHERE city = 'Berlin';
+
+}}}
+
+||= QUERY PLAN =||
+||Nested Loop  (cost\=509.90..1246.42 rows\=96 width\=121) (actual time\=2.527..8.451 rows\=114.00 loops\=1)||
+||  Buffers: shared hit\=981||
+||  ->  Hash Join  (cost\=509.62..1204.96 rows\=96 width\=90) (actual time\=2.504..7.880 rows\=114.00 loops\=1)||
+||        Hash Cond: (eh.venue_id \= v.venue_id)||
+||        Buffers: shared hit\=639||
+||        ->  Bitmap Heap Scan on ""Event_Happening"" eh  (cost\=248.69..910.02 rows\=12955 width\=32) (actual time\=0.722..3.795 rows\=12973.00 loops\=1)||
+||              Recheck Cond: (event_time > CURRENT_TIMESTAMP)||
+||              Heap Blocks: exact\=467||
+||              Buffers: shared hit\=504||
+||              ->  Bitmap Index Scan on idx_event_happening_time  (cost\=0.00..245.45 rows\=12955 width\=0) (actual time\=0.656..0.656 rows\=12976.00 loops\=1)||
+||                    Index Cond: (event_time > CURRENT_TIMESTAMP)||
+||                    Index Searches: 1||
+||                    Buffers: shared hit\=37||
+||        ->  Hash  (cost\=260.00..260.00 rows\=74 width\=66) (actual time\=1.770..1.771 rows\=74.00 loops\=1)||
+||              Buckets: 1024  Batches: 1  Memory Usage: 15kB||
+||              Buffers: shared hit\=135||
+||              ->  Seq Scan on ""Venue"" v  (cost\=0.00..260.00 rows\=74 width\=66) (actual time\=0.011..1.739 rows\=74.00 loops\=1)||
+||                    Filter: ((address_city)::text \= 'Berlin'::text)||
+||                    Rows Removed by Filter: 9926||
+||                    Buffers: shared hit\=135||
+||  ->  Index Scan using ""Event_pkey"" on ""Event"" e  (cost\=0.29..0.43 rows\=1 width\=39) (actual time\=0.004..0.004 rows\=1.00 loops\=114)||
+||        Index Cond: (event_id \= eh.event_id)||
+||        Index Searches: 114||
+||        Buffers: shared hit\=342||
+||Planning:||
+||  Buffers: shared hit\=40||
+||Planning Time: 0.547 ms||
+||Execution Time: 8.502 ms||
+
+ * '''INSERT'''
+
+{{{
+
+EXPLAIN ANALYZE
+INSERT INTO "Event_Happening" (event_happening_id, event_id, venue_id, event_time, duration_minutes, organizers)
+VALUES (88888888, 1, 1, '2028-05-15 19:30:00', 90, 'Berlin Concerts GmbH');
+
+}}}
+
+||= QUERY PLAN =||
+||Insert on ""Event_Happening""  (cost\=0.00..0.01 rows\=0 width\=0) (actual time\=0.275..0.275 rows\=0.00 loops\=1)||
+||  Buffers: shared hit\=12 dirtied\=2||
+||  ->  Result  (cost\=0.00..0.01 rows\=1 width\=100) (actual time\=0.001..0.001 rows\=1.00 loops\=1)||
+||Planning Time: 0.031 ms||
+||Trigger for constraint fk_happening_event: time\=0.109 calls\=1||
+||Trigger for constraint fk_happening_venue: time\=0.058 calls\=1||
+||Execution Time: 0.458 ms||
+
+ * '''UPDATE'''
+
+{{{
+
+EXPLAIN ANALYZE
+UPDATE "Event_Happening"
+SET event_time = '2028-05-16 20:00:00'
+WHERE event_happening_id = 88888888;
+
+}}}
+
+||= QUERY PLAN =||
+||Update on ""Event_Happening""  (cost\=0.29..8.30 rows\=0 width\=0) (actual time\=0.073..0.073 rows\=0.00 loops\=1)||
+||  Buffers: shared hit\=14||
+||  ->  Index Scan using ""Event_Happening_pkey"" on ""Event_Happening""  (cost\=0.29..8.30 rows\=1 width\=14) (actual time\=0.022..0.023 rows\=1.00 loops\=1)||
+||        Index Cond: (event_happening_id \= 88888888)||
+||        Index Searches: 1||
+||        Buffers: shared hit\=3||
+||Planning Time: 0.107 ms||
+||Execution Time: 0.119 ms||