Оптимизација на прашалници
Анализа и оптимизација на Performer_Events
Овој поглед дава преглед на сите изведувачи и настаните на кои тие учествуваат, заедно со прецизниот термин на нивниот настап.
CREATE VIEW "Performer_Events" AS
SELECT p.performer_id,
p.name AS performer_name,
e.event_id,
e.name AS event_name,
eh.event_time
FROM "Performer" p
JOIN "Event_Happening_Performer" ehp ON p.performer_id = ehp.performer_id
JOIN "Event_Happening" eh ON ehp.event_happening_id = eh.event_happening_id
JOIN "Event" e ON eh.event_id = e.event_id;
1. Примарен филтер:
Примарен филтер за овој поглед ќе биде според performer_id (ID на изведувач), бидејќи најчестото пребарување е за да се видат настапите на конкретен изведувач. Дополнително, ќе може да се пребарува и по event_id.
2. Случај на употреба:
Овој поглед е клучен за корисничкиот дел од апликацијата, каде што корисниците пребаруваат каде и кога настапува нивниот омилен изведувач. Бидејќи ова е акција што често се повторува од многу корисници истовремено, перформансите мора да бидат на високо ниво за да се обезбеди добро корисничко искуство.
3. Иницијално време:
Иницијалното време за извршување на погледот изнесува 3.249 s (3249 ms). Ова е исклучително бавно и неприфатливо за една веб-апликација, каде што секој одзив над 500ms се смета за критичен.
4. Анализа на планот на извршување (без индекси):
Иако базата се обидува да користи постоечки Primary Key индекси, главниот проблем се јавува во спојувањето на четирите табели Performer, Event_Happening_Performer, Event_Happening, Event.
Најбавниот дел е кај Nested Loop операциите и проверката на Foreign Keys при INSERT (1.424 s).
Времето на планирање е исто така високо (1.8 s), што укажува на комплексност во резолвирањето на релациите без соодветни патеки.
- SELECT
EXPLAIN ANALYZE
SELECT * FROM "Performer_Events" WHERE performer_id = 10;
| QUERY PLAN |
|---|
| Nested Loop (cost\=1.14..73.38 rows\=7 width\=68) (actual time\=1505.862..3249.242 rows\=4 loops\=1) |
| -> Nested Loop (cost\=0.86..70.92 rows\=7 width\=37) (actual time\=1079.648..2259.465 rows\=4 loops\=1) |
| -> Index Scan using "Performer_pkey" on "Performer" p (cost\=0.28..8.30 rows\=1 width\=21) (actual time\=701.003..701.005 rows\=1 loops\=1) |
| Index Cond: (performer_id \= 10) |
| -> Nested Loop (cost\=0.57..62.55 rows\=7 width\=24) (actual time\=378.637..1558.442 rows\=4 loops\=1) |
| -> Index Only Scan using uq_performer_at_time on "Event_Happening_Performer" ehp (cost\=0.29..4.41 rows\=7 width\=16) (actual time\=90.817..90.838 rows\=4 loops\=1) |
| Index Cond: (performer_id \= 10) |
| Heap Fetches: 0 |
| -> Index Scan using "Event_Happening_pkey" on "Event_Happening" eh (cost\=0.29..8.30 rows\=1 width\=24) (actual time\=366.888..366.889 rows\=1 loops\=4) |
| Index Cond: (event_happening_id \= ehp.event_happening_id) |
| -> Index Scan using "Event_pkey" on "Event" e (cost\=0.29..0.35 rows\=1 width\=39) (actual time\=247.432..247.432 rows\=1 loops\=4) |
| Index Cond: (event_id \= eh.event_id) |
| Planning Time: 1816.633 ms |
| Execution Time: 3249.314 ms |
- INSERT
EXPLAIN ANALYZE
INSERT INTO "Event_Happening_Performer" (event_happening_id, performer_id)
VALUES (1, 10);
| QUERY PLAN |
|---|
| Insert on "Event_Happening_Performer" (cost\=0.00..0.01 rows\=0 width\=0) (actual time\=558.243..558.244 rows\=0 loops\=1) |
| -> Result (cost\=0.00..0.01 rows\=1 width\=16) (actual time\=0.001..0.002 rows\=1 loops\=1) |
| Planning Time: 0.042 ms |
| Trigger for constraint fk_ehp_event_happening: time\=826.105 calls\=1 |
| Trigger for constraint fk_ehp_performer: time\=40.075 calls\=1 |
| Execution Time: 1424.451 ms |
- UPDATE
EXPLAIN ANALYZE
UPDATE "Event_Happening_Performer"
SET performer_id = 11
WHERE event_happening_id = 1 AND performer_id = 10;
| QUERY PLAN |
|---|
| Update on "Event_Happening_Performer" (cost\=0.29..8.31 rows\=0 width\=0) (actual time\=0.219..0.219 rows\=0 loops\=1) |
| -> Index Scan using uq_performer_at_time on "Event_Happening_Performer" (cost\=0.29..8.31 rows\=1 width\=14) (actual time\=0.120..0.121 rows\=1 loops\=1) |
| Index Cond: ((performer_id \= 10) AND (event_happening_id \= 1)) |
| Planning Time: 0.139 ms |
| Trigger for constraint fk_ehp_performer: time\=0.279 calls\=1 |
| Execution Time: 23.621 ms |
5. Оптимизација и индексирање:
За да го решиме проблемот, воведуваме индекси на колоните кои служат за поврзување во JOIN условите и во WHERE филтрите:
idx_ehp_performer_idиidx_ehp_happening_idна табелата што ги поврзува изведувачите со термините.
idx_event_happening_event_idза побрзо поврзување на конкретниот термин со името на настанот.
-- indexes for linking performers with specific event occurrences (M:N relationship) CREATE INDEX idx_ehp_performer_id ON "Event_Happening_Performer"(performer_id); CREATE INDEX idx_ehp_happening_id ON "Event_Happening_Performer"(event_happening_id); -- index for optimizing event lookups within scheduled event happenings CREATE INDEX idx_event_happening_event_id ON "Event_Happening"(event_id);
6. Резултат по оптимизација:
По додавањето на индексите, времето на извршување на SELECT се намали на 0.533 ms.
Подобрување: Ова е забрзување од над 6000 пати.
Операциите за INSERT и UPDATE сега се извршуваат за помалку од 1ms, што значи дека индексите не го забавуваат системот, туку помагаат дури и кај тригерите за Foreign Keys.
- SELECT
EXPLAIN ANALYZE
SELECT * FROM "Performer_Events" WHERE performer_id = 10;
| QUERY PLAN |
|---|
| Nested Loop (cost\=1.14..77.38 rows\=7 width\=68) (actual time\=0.251..0.479 rows\=4 loops\=1) |
| -> Nested Loop (cost\=0.86..74.92 rows\=7 width\=37) (actual time\=0.201..0.306 rows\=4 loops\=1) |
| -> Index Scan using "Performer_pkey" on "Performer" p (cost\=0.28..8.30 rows\=1 width\=21) (actual time\=0.093..0.094 rows\=1 loops\=1) |
| Index Cond: (performer_id \= 10) |
| -> Nested Loop (cost\=0.57..66.55 rows\=7 width\=24) (actual time\=0.105..0.207 rows\=4 loops\=1) |
| -> Index Only Scan using uq_performer_at_time on "Event_Happening_Performer" ehp (cost\=0.29..8.41 rows\=7 width\=16) (actual time\=0.062..0.065 rows\=4 loops\=1) |
| Index Cond: (performer_id \= 10) |
| Heap Fetches: 1 |
| -> Index Scan using "Event_Happening_pkey" on "Event_Happening" eh (cost\=0.29..8.30 rows\=1 width\=24) (actual time\=0.032..0.032 rows\=1 loops\=4) |
| Index Cond: (event_happening_id \= ehp.event_happening_id) |
| -> Index Scan using "Event_pkey" on "Event" e (cost\=0.29..0.35 rows\=1 width\=39) (actual time\=0.041..0.041 rows\=1 loops\=4) |
| Index Cond: (event_id \= eh.event_id) |
| Planning Time: 1.600 ms |
| Execution Time: 0.533 ms |
- INSERT
EXPLAIN ANALYZE
INSERT INTO "Event_Happening_Performer" (event_happening_id, performer_id)
VALUES (2, 10);
| QUERY PLAN |
|---|
| Insert on "Event_Happening_Performer" (cost\=0.00..0.01 rows\=0 width\=0) (actual time\=0.307..0.308 rows\=0 loops\=1) |
| -> Result (cost\=0.00..0.01 rows\=1 width\=16) (actual time\=0.001..0.001 rows\=1 loops\=1) |
| Planning Time: 0.046 ms |
| Trigger for constraint fk_ehp_event_happening: time\=0.332 calls\=1 |
| Trigger for constraint fk_ehp_performer: time\=0.169 calls\=1 |
| Execution Time: 0.833 ms |
- UPDATE
EXPLAIN ANALYZE
UPDATE "Event_Happening_Performer"
SET performer_id = 12
WHERE event_happening_id = 2 AND performer_id = 10;
| QUERY PLAN |
|---|
| Update on "Event_Happening_Performer" (cost\=0.29..8.31 rows\=0 width\=0) (actual time\=0.437..0.438 rows\=0 loops\=1) |
| -> Index Scan using idx_ehp_happening_id on "Event_Happening_Performer" (cost\=0.29..8.31 rows\=1 width\=14) (actual time\=0.174..0.175 rows\=1 loops\=1) |
| Index Cond: (event_happening_id \= 2) |
| Filter: (performer_id \= 10) |
| Rows Removed by Filter: 1 |
| Planning Time: 0.161 ms |
| Trigger for constraint fk_ehp_performer: time\=0.271 calls\=1 |
| Execution Time: 0.763 ms |
Анализа и оптимизација на Venue_Layout
Овој поглед ја прикажува целосната структура на секој објект (сала), поврзувајќи ги поединечните седишта со соодветните сектори и името на објектот.
CREATE VIEW "Venue_Layout" AS
SELECT v.venue_id,
v.name AS venue_name,
s.section_id,
s.name AS section_name,
st.seat_id,
st.seat_number
FROM "Venue" v
JOIN "Section" s ON v.venue_id = s.venue_id
JOIN "Seat" st ON s.section_id = st.section_id;
1. Примарен филтер:
Примарен филтер за овој поглед е venue_id. Ова е логично бидејќи корисникот или администраторот најчесто бараат преглед на седиштата за една специфична сала. Секундарен филтер е seat_number при проверка или промена на конкретно седиште.
2. Случај на употреба:
Погледот се користи за прикажување на графичката мапа на седишта при процесот на резервација. Корисникот избира настан, па сала, и системот мора моментално да ја вчита структурата на салата. Бавноста тука директно влијае на продажбата на билети - ако мапата се вчитува со секунди, корисникот може да се откаже.
3. Иницијално време:
SELECT: 2.164 s (2164 ms)
UPDATE: 312.239 s (околу 5.2 минути) Времето за UPDATE е критично. Ова значи дека ако системот треба да ажурира статус на седиште, целата база би била под огромен притисок.
4. Анализа на планот на извршување (без индекси):
Кај SELECT операцијата, базата користи Parallel Index Scan врз табелата Section и троши многу време на филтрирање на редови кои не одговараат (Rows Removed by Filter: 27502).
Кај UPDATE операцијата се случува најлошото сценарио: Seq Scan. Базата мора да ги прочита сите 20,753,209 редови во табелата Seat за да го најде седиштето со број 999999. Тоа е причината за времето од 312 s.
- SELECT
EXPLAIN ANALYZE
SELECT * FROM "Venue_Layout" WHERE venue_id = 1;
| QUERY PLAN |
|---|
| Nested Loop (cost\=1001.14..10398.19 rows\=2264 width\=55) (actual time\=447.576..2164.258 rows\=775 loops\=1) |
| -> Index Scan using "Venue_pkey" on "Venue" v (cost\=0.29..8.30 rows\=1 width\=28) (actual time\=68.551..68.555 rows\=1 loops\=1) |
| Index Cond: (venue_id \= 1) |
| -> Gather (cost\=1000.85..10367.25 rows\=2264 width\=35) (actual time\=379.017..2095.610 rows\=775 loops\=1) |
| Workers Planned: 1 |
| Workers Launched: 1 |
| -> Nested Loop (cost\=0.85..9140.85 rows\=1332 width\=35) (actual time\=932.279..1787.769 rows\=388 loops\=2) |
| -> Parallel Index Scan using "Section_pkey" on "Section" s (cost\=0.29..1691.80 rows\=4 width\=23) (actual time\=771.383..1395.534 rows\=2 loops\=2) |
| Filter: (venue_id \= 1) |
| Rows Removed by Filter: 27502 |
| -> Index Scan using uq_seat_section_number on "Seat" st (cost\=0.56..1853.21 rows\=905 width\=20) (actual time\=64.373..156.866 rows\=155 loops\=5) |
| Index Cond: (section_id \= s.section_id) |
| Planning Time: 1874.048 ms |
| Execution Time: 2164.361 ms |
- INSERT
EXPLAIN ANALYZE
INSERT INTO "Seat" (seat_id, section_id, seat_number)
SELECT COALESCE(MAX(seat_id), 0) + 1, 1, 999999 FROM "Seat";
| QUERY PLAN |
|---|
| Insert on "Seat" (cost\=0.67..0.69 rows\=0 width\=0) (actual time\=745.461..745.464 rows\=0 loops\=1) |
| -> Subquery Scan on "*SELECT*" (cost\=0.67..0.69 rows\=1 width\=20) (actual time\=481.921..481.925 rows\=1 loops\=1) |
| -> Result (cost\=0.67..0.69 rows\=1 width\=16) (actual time\=481.918..481.920 rows\=1 loops\=1) |
| InitPlan 1 |
| -> Limit (cost\=0.56..0.67 rows\=1 width\=8) (actual time\=481.909..481.910 rows\=1 loops\=1) |
| -> Index Only Scan Backward using "Seat_pkey" on "Seat" "Seat_1" (cost\=0.56..2330912.81 rows\=20753208 width\=8) (actual time\=481.907..481.908 rows\=1 loops\=1) |
| Heap Fetches: 0 |
| Planning Time: 0.339 ms |
| Trigger for constraint fk_seat_section: time\=0.533 calls\=1 |
| Execution Time: 746.039 ms |
- UPDATE
EXPLAIN ANALYZE
UPDATE "Seat"
SET seat_number = 888888
WHERE seat_number = 999999;
| QUERY PLAN |
|---|
| Update on "Seat" (cost\=0.00..391602.10 rows\=0 width\=0) (actual time\=308799.033..308799.035 rows\=0 loops\=1) |
| -> Seq Scan on "Seat" (cost\=0.00..391602.10 rows\=11492 width\=10) (actual time\=308792.032..308792.034 rows\=1 loops\=1) |
| Filter: (seat_number \= 999999) |
| Rows Removed by Filter: 20753209 |
| Planning Time: 0.114 ms |
| JIT: |
| Functions: 4 |
| Options: Inlining false, Optimization false, Expressions true, Deforming true |
| Timing: Generation 0.405 ms (Deform 0.071 ms), Inlining 0.000 ms, Optimization 0.595 ms, Emission 6.566 ms, Total 7.565 ms |
| Execution Time: 312239.424 ms |
5. Оптимизација и индексирање:
За да се елиминира целосното скенирање на табелите, се воведуваат:
idx_section_venue_id: Овозможува веднаш да се најдат само секциите што му припаѓаат на соодветната сала.
idx_seat_section_id: Го забрзува поврзувањето на седиштата со нивните секции.
idx_seat_number: Клучен индекс кој го претвора петминутното пребарување во инстантно пронаоѓање на седиштето.
-- index for linking seats to their respective sections CREATE INDEX idx_seat_section_id ON "Seat"(section_id); -- index for linking sections to venues CREATE INDEX idx_section_venue_id ON "Section"(venue_id); -- index for optimizing search and update operations on specific seat numbers CREATE INDEX idx_seat_number ON "Seat"(seat_number);
6. Резултат по оптимизација:
SELECT: Намалено на 0.572 ms.
UPDATE: Намалено на 0.461 ms.
- SELECT
EXPLAIN ANALYZE
SELECT * FROM "Venue_Layout" WHERE venue_id = 1;
| QUERY PLAN |
|---|
| Nested Loop (cost\=1.01..3060.48 rows\=2264 width\=55) (actual time\=0.184..0.512 rows\=776 loops\=1) |
| -> Nested Loop (cost\=0.57..16.76 rows\=6 width\=43) (actual time\=0.140..0.144 rows\=5 loops\=1) |
| -> Index Scan using "Venue_pkey" on "Venue" v (cost\=0.29..8.30 rows\=1 width\=28) (actual time\=0.077..0.078 rows\=1 loops\=1) |
| Index Cond: (venue_id \= 1) |
| -> Index Scan using idx_section_venue_id on "Section" s (cost\=0.29..8.39 rows\=6 width\=23) (actual time\=0.058..0.060 rows\=5 loops\=1) |
| Index Cond: (venue_id \= 1) |
| -> Index Scan using idx_seat_section_id on "Seat" st (cost\=0.44..498.24 rows\=905 width\=20) (actual time\=0.012..0.053 rows\=155 loops\=5) |
| Index Cond: (section_id \= s.section_id) |
| Planning Time: 1.235 ms |
| Execution Time: 0.572 ms |
- INSERT
EXPLAIN ANALYZE
INSERT INTO "Seat" (seat_id, section_id, seat_number)
SELECT COALESCE(MAX(seat_id), 0) + 1, 1, 111222 FROM "Seat";
| QUERY PLAN |
|---|
| Insert on "Seat" (cost\=0.67..0.69 rows\=0 width\=0) (actual time\=0.493..0.494 rows\=0 loops\=1) |
| -> Subquery Scan on "*SELECT*" (cost\=0.67..0.69 rows\=1 width\=20) (actual time\=0.169..0.171 rows\=1 loops\=1) |
| -> Result (cost\=0.67..0.69 rows\=1 width\=16) (actual time\=0.167..0.168 rows\=1 loops\=1) |
| InitPlan 1 |
| -> Limit (cost\=0.56..0.67 rows\=1 width\=8) (actual time\=0.162..0.163 rows\=1 loops\=1) |
| -> Index Only Scan Backward using "Seat_pkey" on "Seat" "Seat_1" (cost\=0.56..2330912.84 rows\=20753210 width\=8) (actual time\=0.161..0.161 rows\=1 loops\=1) |
| Heap Fetches: 1 |
| Planning Time: 0.219 ms |
| Trigger for constraint fk_seat_section: time\=0.300 calls\=1 |
| Execution Time: 0.839 ms |
- UPDATE
EXPLAIN ANALYZE
UPDATE "Seat"
SET seat_number = 333444
WHERE seat_number = 111222;
| QUERY PLAN |
|---|
| Update on "Seat" (cost\=0.44..44297.09 rows\=0 width\=0) (actual time\=0.395..0.396 rows\=0 loops\=1) |
| -> Index Scan using idx_seat_number on "Seat" (cost\=0.44..44297.09 rows\=11492 width\=10) (actual time\=0.193..0.195 rows\=1 loops\=1) |
| Index Cond: (seat_number \= 111222) |
| Planning Time: 0.141 ms |
| Execution Time: 0.461 ms |
Анализа и оптимизација на User_Tickets
Овој поглед дава детален преглед на сите купени билети по корисник, вклучувајќи го соодветниот настан, QR-кодот за влез и информациите за евентуална рефундација.
CREATE VIEW "User_Tickets" AS
SELECT u.user_id,
u.username,
t.ticket_id,
e.event_id,
e.name AS event_name,
tp.purchase_id,
tp.qr_code,
tr.refund_id,
tr.refund_time
FROM "User" u
JOIN "Ticket_Purchase" tp ON u.user_id = tp.user_id
JOIN "Ticket" t ON tp.ticket_id = t.ticket_id
JOIN "Event_Happening" eh ON t.event_happening_id = eh.event_happening_id
JOIN "Event" e ON eh.event_id = e.event_id
LEFT JOIN "Ticket_Refund" tr ON tp.purchase_id = tr.purchase_id;
1. Примарен филтер:
Примарен филтер за овој поглед е user_id. Ова е најчестото сценарио бидејќи погледот служи за корисникот да ја види сопствената историја на купени билети и нивниот статус (дали се рефундирани или не).
2. Случај на употреба:
Погледот се користи во делот My Tickets на профилот на секој регистриран корисник. Ова е критична точка на интеракција; корисникот очекува веднаш да ги добие своите билети за да може да го прикаже QR-кодот при влез на настан. Секое доцнење тука предизвикува директен застој на влезните капии.
3. Иницијално време:
Иницијалното време за извршување изнесува 443.784 ms. Иако ова изгледа брзо во споредба со претходните погледи, треба да се земе предвид дека во реална околина со илјадници истовремени корисници, ова време ќе ескалира и ќе ја преоптовари базата.
Анализа на планот на извршување (без индекси):
Главниот проблем е идентификуван кај табелата Ticket_Purchase:
- Се извршува Parallel Seq Scan врз 3.2 милиони записи.
- Базата троши 389 ms само за да ги прелиста сите трансакции и да ги најде оние што му припаѓаат на
user_id = 1.
- SELECT
EXPLAIN ANALYZE
SELECT * FROM "User_Tickets" WHERE user_id = 1;
| QUERY PLAN |
|---|
| Nested Loop Left Join (cost\=1002.00..232798.26 rows\=1 width\=115) (actual time\=431.345..441.975 rows\=1 loops\=1) |
| -> Nested Loop (cost\=1001.56..232789.81 rows\=1 width\=99) (actual time\=431.312..441.941 rows\=1 loops\=1) |
| -> Nested Loop (cost\=1001.28..232789.45 rows\=1 width\=68) (actual time\=431.253..441.878 rows\=1 loops\=1) |
| -> Nested Loop (cost\=1000.99..232789.15 rows\=1 width\=68) (actual time\=431.200..441.822 rows\=1 loops\=1) |
| -> Nested Loop (cost\=1000.43..232780.57 rows\=1 width\=60) (actual time\=431.038..441.657 rows\=1 loops\=1) |
| -> Index Scan using "User_pkey" on "User" u (cost\=0.43..8.45 rows\=1 width\=26) (actual time\=0.114..0.118 rows\=1 loops\=1) |
| Index Cond: (user_id \= 1) |
| -> Gather (cost\=1000.00..232772.11 rows\=1 width\=42) (actual time\=430.826..441.440 rows\=1 loops\=1) |
| Workers Planned: 4 |
| Workers Launched: 4 |
| -> Parallel Seq Scan on "Ticket_Purchase" tp (cost\=0.00..231772.01 rows\=1 width\=42) (actual time\=389.356..389.357 rows\=0 loops\=5) |
| Filter: (user_id \= 1) |
| Rows Removed by Filter: 3200000 |
| -> Index Scan using "Ticket_pkey" on "Ticket" t (cost\=0.56..8.58 rows\=1 width\=16) (actual time\=0.104..0.105 rows\=1 loops\=1) |
| Index Cond: (ticket_id \= tp.ticket_id) |
| -> Index Scan using "Event_Happening_pkey" on "Event_Happening" eh (cost\=0.29..0.31 rows\=1 width\=16) (actual time\=0.033..0.034 rows\=1 loops\=1) |
| Index Cond: (event_happening_id \= t.event_happening_id) |
| -> Index Scan using "Event_pkey" on "Event" e (cost\=0.29..0.35 rows\=1 width\=39) (actual time\=0.039..0.040 rows\=1 loops\=1) |
| Index Cond: (event_id \= eh.event_id) |
| -> Index Scan using "Ticket_Refund_purchase_id_key" on "Ticket_Refund" tr (cost\=0.43..8.45 rows\=1 width\=24) (actual time\=0.015..0.016 rows\=0 loops\=1) |
| Index Cond: (purchase_id \= tp.purchase_id) |
| Planning Time: 15.360 ms |
| JIT: |
| Functions: 44 |
| Options: Inlining false, Optimization false, Expressions true, Deforming true |
| Timing: Generation 3.367 ms (Deform 1.376 ms), Inlining 0.000 ms, Optimization 2.363 ms, Emission 42.753 ms, Total 48.484 ms |
| Execution Time: 443.784 ms |
- INSERT
EXPLAIN ANALYZE
INSERT INTO "Ticket_Purchase" (purchase_id, ticket_id, user_id, qr_code, purchase_amount)
SELECT COALESCE(MAX(purchase_id), 0) + 1, 1, 1, 'QR-TEST-CODE-001', 1200.00
FROM "Ticket_Purchase";
| QUERY PLAN |
|---|
| Insert on "Ticket_Purchase" (cost\=0.47..0.50 rows\=0 width\=0) (actual time\=0.217..0.218 rows\=0 loops\=1) |
| -> Subquery Scan on "*SELECT*" (cost\=0.47..0.50 rows\=1 width\=552) (actual time\=0.055..0.057 rows\=1 loops\=1) |
| -> Result (cost\=0.47..0.48 rows\=1 width\=80) (actual time\=0.043..0.044 rows\=1 loops\=1) |
| InitPlan 1 |
| -> Limit (cost\=0.43..0.47 rows\=1 width\=8) (actual time\=0.038..0.039 rows\=1 loops\=1) |
| -> Index Only Scan Backward using "Ticket_Purchase_pkey" on "Ticket_Purchase" "Ticket_Purchase_1" (cost\=0.43..484152.95 rows\=16000004 width\=8) (actual time\=0.037..0.038 rows\=1 loops\=1) |
| Heap Fetches: 3 |
| Planning Time: 0.207 ms |
| Trigger for constraint fk_purchase_ticket: time\=0.190 calls\=1 |
| Trigger for constraint fk_purchase_user: time\=0.136 calls\=1 |
| Execution Time: 0.600 ms |
- UPDATE
EXPLAIN ANALYZE
UPDATE "Ticket_Purchase"
SET purchase_amount = 1500.00
WHERE user_id = 1 AND qr_code = 'QR-TEST-CODE-001';
| QUERY PLAN |
|---|
| Update on "Ticket_Purchase" (cost\=0.56..8.58 rows\=0 width\=0) (actual time\=0.121..0.122 rows\=0 loops\=1) |
| -> Index Scan using "Ticket_Purchase_qr_code_key" on "Ticket_Purchase" (cost\=0.56..8.58 rows\=1 width\=10) (actual time\=0.048..0.050 rows\=1 loops\=1) |
| Index Cond: ((qr_code)::text \= 'QR-TEST-CODE-001'::text) |
| Filter: (user_id \= 1) |
| Planning Time: 0.181 ms |
| Execution Time: 0.157 ms |
5. Оптимизација и индексирање:
Со цел да се избегне целосното скенирање, се предлагаат следните индекси:
idx_ticket_purchase_user_id: За директен пристап до билетите на корисникот.
idx_ticket_refund_purchase_id: За побрзо извршување на LEFT JOIN операцијата со табелата за рефундации.
idx_ticket_purchase_ticket_idиidx_event_happening_event_id: За побрзо поврзување на релациите.
-- index for linking ticket purchases to the specific tickets CREATE INDEX idx_ticket_purchase_ticket_id ON "Ticket_Purchase"(ticket_id); -- index for linking purchases to users CREATE INDEX idx_ticket_purchase_user_id ON "Ticket_Purchase"(user_id); -- index for the LEFT JOIN with refunds CREATE INDEX idx_ticket_refund_purchase_id ON "Ticket_Refund"(purchase_id); -- index for optimizing event lookups within scheduled event happenings CREATE INDEX idx_event_happening_event_id ON "Event_Happening"(event_id);
6. Резултат по оптимизација:
Времето по оптимизација остана речиси идентично (450.880 ms).
Планот покажува дека базата се уште избира Parallel Seq Scan наместо новокреираниот индекс.
Сепак, кај INSERT и UPDATE операциите се гледа стабилност и екстремна брзина (под 1 ms), што потврдува дека индексите се правилно поставени за интегритетот на податоците.
- SELECT
EXPLAIN ANALYZE
SELECT * FROM "User_Tickets" WHERE user_id = 1;
| QUERY PLAN |
|---|
| Nested Loop Left Join (cost\=1002.00..232798.25 rows\=1 width\=115) (actual time\=438.622..449.169 rows\=2 loops\=1) |
| -> Nested Loop (cost\=1001.56..232789.80 rows\=1 width\=99) (actual time\=438.588..449.128 rows\=2 loops\=1) |
| -> Gather (cost\=1001.13..232781.35 rows\=1 width\=81) (actual time\=438.383..448.900 rows\=2 loops\=1) |
| Workers Planned: 4 |
| Workers Launched: 4 |
| -> Nested Loop (cost\=1.14..231781.25 rows\=1 width\=81) (actual time\=397.225..397.235 rows\=0 loops\=5) |
| -> Nested Loop (cost\=0.85..231780.89 rows\=1 width\=50) (actual time\=397.203..397.211 rows\=0 loops\=5) |
| -> Nested Loop (cost\=0.56..231780.59 rows\=1 width\=50) (actual time\=397.176..397.182 rows\=0 loops\=5) |
| -> Parallel Seq Scan on "Ticket_Purchase" tp (cost\=0.00..231772.01 rows\=1 width\=42) (actual time\=397.114..397.115 rows\=0 loops\=5) |
| Filter: (user_id \= 1) |
| Rows Removed by Filter: 3200000 |
| -> Index Scan using "Ticket_pkey" on "Ticket" t (cost\=0.56..8.58 rows\=1 width\=16) (actual time\=0.107..0.107 rows\=1 loops\=2) |
| Index Cond: (ticket_id \= tp.ticket_id) |
| -> Index Scan using "Event_Happening_pkey" on "Event_Happening" eh (cost\=0.29..0.31 rows\=1 width\=16) (actual time\=0.055..0.055 rows\=1 loops\=2) |
| Index Cond: (event_happening_id \= t.event_happening_id) |
| -> Index Scan using "Event_pkey" on "Event" e (cost\=0.29..0.35 rows\=1 width\=39) (actual time\=0.044..0.044 rows\=1 loops\=2) |
| Index Cond: (event_id \= eh.event_id) |
| -> Index Scan using "User_pkey" on "User" u (cost\=0.43..8.45 rows\=1 width\=26) (actual time\=0.086..0.087 rows\=1 loops\=2) |
| Index Cond: (user_id \= 1) |
| -> Index Scan using idx_ticket_refund_purchase_id on "Ticket_Refund" tr (cost\=0.43..8.45 rows\=1 width\=24) (actual time\=0.010..0.010 rows\=0 loops\=2) |
| Index Cond: (purchase_id \= tp.purchase_id) |
| Planning Time: 2.459 ms |
| JIT: |
| Functions: 99 |
| Options: Inlining false, Optimization false, Expressions true, Deforming true |
| Timing: Generation 7.544 ms (Deform 3.336 ms), Inlining 0.000 ms, Optimization 3.874 ms, Emission 71.829 ms, Total 83.247 ms |
| Execution Time: 450.880 ms |
- INSERT
EXPLAIN ANALYZE
INSERT INTO "Ticket_Purchase" (purchase_id, ticket_id, user_id, qr_code, purchase_amount)
SELECT COALESCE(MAX(purchase_id), 0) + 1, 1, 1, 'QR-TEST-CODE-002', 1200.00
FROM "Ticket_Purchase";
| QUERY PLAN |
|---|
| Insert on "Ticket_Purchase" (cost\=0.47..0.50 rows\=0 width\=0) (actual time\=0.452..0.453 rows\=0 loops\=1) |
| -> Subquery Scan on "*SELECT*" (cost\=0.47..0.50 rows\=1 width\=552) (actual time\=0.049..0.051 rows\=1 loops\=1) |
| -> Result (cost\=0.47..0.48 rows\=1 width\=80) (actual time\=0.038..0.039 rows\=1 loops\=1) |
| InitPlan 1 |
| -> Limit (cost\=0.43..0.47 rows\=1 width\=8) (actual time\=0.033..0.033 rows\=1 loops\=1) |
| -> Index Only Scan Backward using "Ticket_Purchase_pkey" on "Ticket_Purchase" "Ticket_Purchase_1" (cost\=0.43..484152.93 rows\=16000003 width\=8) (actual time\=0.032..0.032 rows\=1 loops\=1) |
| Heap Fetches: 2 |
| Planning Time: 0.207 ms |
| Trigger for constraint fk_purchase_ticket: time\=0.194 calls\=1 |
| Trigger for constraint fk_purchase_user: time\=0.130 calls\=1 |
| Execution Time: 0.828 ms |
- UPDATE
EXPLAIN ANALYZE
UPDATE "Ticket_Purchase"
SET purchase_amount = 1350.00
WHERE user_id = 1 AND qr_code = 'QR-TEST-CODE-002';
| QUERY PLAN |
|---|
| Update on "Ticket_Purchase" (cost\=0.56..8.58 rows\=0 width\=0) (actual time\=0.207..0.207 rows\=0 loops\=1) |
| -> Index Scan using "Ticket_Purchase_qr_code_key" on "Ticket_Purchase" (cost\=0.56..8.58 rows\=1 width\=10) (actual time\=0.124..0.126 rows\=1 loops\=1) |
| Index Cond: ((qr_code)::text \= 'QR-TEST-CODE-002'::text) |
| Filter: (user_id \= 1) |
| Planning Time: 0.150 ms |
| Execution Time: 0.267 ms |
Анализа и оптимизација на Event_User_Ratings
Овој поглед овозможува детален пристап до поединечните коментари и оценки што секој корисник ги оставил за одреден термин на настан.
CREATE VIEW "Event_User_Ratings" AS
SELECT eh.event_happening_id,
e.event_id,
e.name AS event_name,
u.user_id,
u.username,
ehr.rating_id,
ehr.rating,
ehr.comment
FROM "Event" e
JOIN "Event_Happening" eh ON e.event_id = eh.event_id
JOIN "Event_Happening_Rating" ehr ON eh.event_happening_id = ehr.event_happening_id
JOIN "User" u ON ehr.user_id = u.user_id;
1. Примарен филтер:
Примарен филтер за овој поглед е user_id, бидејќи најчесто корисниците сакаат да ги видат сопствените претходни оценки и коментари. Секундарен филтер е event_happening_id, кога администраторот сака да ги види сите рецензии за конкретен термин на настан.
2. Случај на употреба:
Погледот се користи во делот на корисничкиот профил (историја на рецензии) и на страната на самиот настан за приказ на рецензии од други корисници. Ова влијае на довербата на потенцијалните купувачи и на транспарентноста на платформата.
3. Иницијално време:
Иницијалното време за извршување е многу ниско (0.187 ms). Ова се должи на фактот што табелата Event_Happening_Rating е релативно мала (околу 17,800 редови), па базата може многу брзо да ја процесира дури и без екстремна оптимизација.
4. Анализа на планот на извршување (без индекси):
Иако времето е мало, планот без индекси покажува дека базата веќе користи некои постоечки индекси (idx_ehr_user_id).
При UPDATE операцијата, базата користи Index Scan, што значи дека веќе постои ефикасна патека за пронаоѓање на записот.
- SELECT
EXPLAIN ANALYZE
SELECT * FROM "Event_User_Ratings"
WHERE user_id = 1;
| QUERY PLAN |
|---|
| Nested Loop (cost\=1.29..21.42 rows\=1 width\=131) (actual time\=0.104..0.105 rows\=0 loops\=1) |
| -> Nested Loop (cost\=0.86..12.96 rows\=1 width\=113) (actual time\=0.103..0.105 rows\=0 loops\=1) |
| -> Nested Loop (cost\=0.57..12.61 rows\=1 width\=82) (actual time\=0.103..0.104 rows\=0 loops\=1) |
| -> Index Scan using idx_ehr_user_id on "Event_Happening_Rating" ehr (cost\=0.29..4.30 rows\=1 width\=74) (actual time\=0.103..0.103 rows\=0 loops\=1) |
| Index Cond: (user_id \= 1) |
| -> Index Scan using "Event_Happening_pkey" on "Event_Happening" eh (cost\=0.29..8.30 rows\=1 width\=16) (never executed) |
| Index Cond: (event_happening_id \= ehr.event_happening_id) |
| -> Index Scan using "Event_pkey" on "Event" e (cost\=0.29..0.35 rows\=1 width\=39) (never executed) |
| Index Cond: (event_id \= eh.event_id) |
| -> Index Scan using "User_pkey" on "User" u (cost\=0.43..8.45 rows\=1 width\=26) (never executed) |
| Index Cond: (user_id \= 1) |
| Planning Time: 464.594 ms |
| Execution Time: 0.187 ms |
- INSERT
EXPLAIN ANALYZE
INSERT INTO "Event_Happening_Rating" (rating_id, event_happening_id, user_id, rating, comment)
SELECT COALESCE(MAX(rating_id), 0) + 1, 1, 1, 5, 'Test rating'
FROM "Event_Happening_Rating";
| QUERY PLAN |
|---|
| Insert on "Event_Happening_Rating" (cost\=0.31..0.34 rows\=0 width\=0) (actual time\=0.521..0.522 rows\=0 loops\=1) |
| -> Subquery Scan on "*SELECT*" (cost\=0.31..0.34 rows\=1 width\=60) (actual time\=0.122..0.123 rows\=1 loops\=1) |
| -> Result (cost\=0.31..0.33 rows\=1 width\=52) (actual time\=0.119..0.120 rows\=1 loops\=1) |
| InitPlan 1 |
| -> Limit (cost\=0.29..0.31 rows\=1 width\=8) (actual time\=0.114..0.115 rows\=1 loops\=1) |
| -> Index Only Scan Backward using "Event_Happening_Rating_pkey" on "Event_Happening_Rating" "Event_Happening_Rating_1" (cost\=0.29..478.37 rows\=17872 width\=8) (actual time\=0.113..0.113 rows\=1 loops\=1) |
| Heap Fetches: 6 |
| Planning Time: 0.297 ms |
| Trigger for constraint fk_event_happening_rating_event_happening: time\=0.490 calls\=1 |
| Trigger for constraint fk_event_happening_rating_user: time\=0.370 calls\=1 |
| Execution Time: 1.444 ms |
- UPDATE
EXPLAIN ANALYZE
UPDATE "Event_Happening_Rating"
SET rating = 4, comment = 'New test rating'
WHERE event_happening_id = 1 AND user_id = 1;
| QUERY PLAN |
|---|
| Update on "Event_Happening_Rating" (cost\=0.29..4.31 rows\=0 width\=0) (actual time\=0.176..0.177 rows\=0 loops\=1) |
| -> Index Scan using idx_ehr_user_id on "Event_Happening_Rating" (cost\=0.29..4.31 rows\=1 width\=42) (actual time\=0.039..0.042 rows\=1 loops\=1) |
| Index Cond: (user_id \= 1) |
| Filter: (event_happening_id \= 1) |
| Planning Time: 0.153 ms |
| Execution Time: 0.218 ms |
5. Оптимизација и индексирање:
За да се осигураме дека погледот ќе остане брз и кога бројот на рецензии ќе порасне на милиони, се додаваат:
idx_ehr_happening_id: За брзо филтрирање на рецензии по настан.
idx_ehr_user_id: За брзо филтрирање на рецензии по корисник.
-- index for linking ratings to event happenings CREATE INDEX idx_ehr_happening_id ON "Event_Happening_Rating"(event_happening_id); -- index for linking ratings to users CREATE INDEX idx_ehr_user_id ON "Event_Happening_Rating"(user_id);
6. Резултат по оптимизација:
По „оптимизацијата“, времето на SELECT се зголемило на 359.600 ms.
Анализа на аномалијата: Планот покажува дека базата во овој случај одбрала Seq Scan наместо Index Scan.
- SELECT
EXPLAIN ANALYZE
SELECT * FROM "Event_User_Ratings" WHERE user_id = 1;
| QUERY PLAN |
|---|
| Nested Loop (cost\=1.00..476.51 rows\=1 width\=131) (actual time\=359.514..359.524 rows\=1 loops\=1) |
| -> Nested Loop (cost\=0.57..468.06 rows\=1 width\=113) (actual time\=359.389..359.396 rows\=1 loops\=1) |
| -> Nested Loop (cost\=0.29..467.71 rows\=1 width\=82) (actual time\=2.104..2.110 rows\=1 loops\=1) |
| -> Seq Scan on "Event_Happening_Rating" ehr (cost\=0.00..459.40 rows\=1 width\=74) (actual time\=2.038..2.041 rows\=1 loops\=1) |
| Filter: (user_id \= 1) |
| Rows Removed by Filter: 17871 |
| -> Index Scan using "Event_Happening_pkey" on "Event_Happening" eh (cost\=0.29..8.30 rows\=1 width\=16) (actual time\=0.061..0.062 rows\=1 loops\=1) |
| Index Cond: (event_happening_id \= ehr.event_happening_id) |
| -> Index Scan using "Event_pkey" on "Event" e (cost\=0.29..0.35 rows\=1 width\=39) (actual time\=357.279..357.280 rows\=1 loops\=1) |
| Index Cond: (event_id \= eh.event_id) |
| -> Index Scan using "User_pkey" on "User" u (cost\=0.43..8.45 rows\=1 width\=26) (actual time\=0.120..0.121 rows\=1 loops\=1) |
| Index Cond: (user_id \= 1) |
| Planning Time: 31.049 ms |
| Execution Time: 359.600 ms |
- INSERT
EXPLAIN ANALYZE
INSERT INTO "Event_Happening_Rating" (rating_id, event_happening_id, user_id, rating, comment)
SELECT COALESCE(MAX(rating_id), 0) + 1, 2, 1, 5, 'Test rating'
FROM "Event_Happening_Rating";
| QUERY PLAN |
|---|
| Insert on "Event_Happening_Rating" (cost\=0.31..0.34 rows\=0 width\=0) (actual time\=0.482..0.483 rows\=0 loops\=1) |
| -> Subquery Scan on "*SELECT*" (cost\=0.31..0.34 rows\=1 width\=60) (actual time\=0.117..0.119 rows\=1 loops\=1) |
| -> Result (cost\=0.31..0.33 rows\=1 width\=52) (actual time\=0.115..0.115 rows\=1 loops\=1) |
| InitPlan 1 |
| -> Limit (cost\=0.29..0.31 rows\=1 width\=8) (actual time\=0.110..0.111 rows\=1 loops\=1) |
| -> Index Only Scan Backward using "Event_Happening_Rating_pkey" on "Event_Happening_Rating" "Event_Happening_Rating_1" (cost\=0.29..484.37 rows\=17872 width\=8) (actual time\=0.108..0.108 rows\=1 loops\=1) |
| Heap Fetches: 4 |
| Planning Time: 0.203 ms |
| Trigger for constraint fk_event_happening_rating_event_happening: time\=0.197 calls\=1 |
| Trigger for constraint fk_event_happening_rating_user: time\=0.114 calls\=1 |
| Execution Time: 0.842 ms |
- UPDATE
EXPLAIN ANALYZE
UPDATE "Event_Happening_Rating"
SET rating = 6, comment = 'New test rating'
WHERE event_happening_id = 1 AND user_id = 2;
| QUERY PLAN |
|---|
| Update on "Event_Happening_Rating" (cost\=0.29..8.31 rows\=0 width\=0) (actual time\=0.021..0.022 rows\=0 loops\=1) |
| -> Index Scan using uq_rating_happening_user on "Event_Happening_Rating" (cost\=0.29..8.31 rows\=1 width\=42) (actual time\=0.020..0.021 rows\=0 loops\=1) |
| Index Cond: ((event_happening_id \= 1) AND (user_id \= 2)) |
| Planning Time: 0.132 ms |
| Execution Time: 0.081 ms |
Анализа и оптимизација на Event_Overall_Ratings
Овој поглед врши статистичка анализа на задоволството на публиката преку пресметување на просечната оцена и вкупниот број на рецензии за секој поединечен настан.
CREATE VIEW "Event_Overall_Ratings" AS
SELECT
e.event_id,
e.name AS event_name,
eh.event_happening_id,
eh.event_time,
COUNT(ehr.rating_id) AS total_reviews,
AVG(ehr.rating) AS average_rating
FROM "Event" e
JOIN "Event_Happening" eh ON e.event_id = eh.event_id
JOIN "Event_Happening_Rating" ehr ON eh.event_happening_id = ehr.event_happening_id
GROUP BY e.event_id, e.name, eh.event_happening_id, eh.event_time;
1. Примарен филтер:
Примарен филтер за овој поглед е event_id. Најчесто пребаруваме сумарна статистика за еден конкретен настан за да ги прикажеме ѕвездичките (рејтингот) на неговата насловна страна.
2. Случај на употреба:
Овој поглед е клучен за почетната страна на апликацијата и пребарувањето на настани. Корисниците често ги филтрираат настаните според нивната популарност и просечна оценка. Бидејќи овие податоци се агрегираат (COUNT и AVG) од илјадници рецензии, пресметката може да стане тешка операција.
3. Иницијално време:
Иницијалното време за извршување е 1.029 s (1029 ms). Ова е на самата граница на прифатливост. Ако имаме илјадници настани со милиони рецензии, ова време би пораснало експоненцијално.
4. Анализа на планот на извршување (без индекси):
Базата троши најмногу време на Bitmap Heap Scan врз Event_Happening и Index Scan врз рецензиите за да ги собере сите потребни редови за агрегација.
Потоа користи GroupAggregate операција која мора да ги процесира сите вчитани редови за да ги пресмета просекот и вкупниот број.
- SELECT
EXPLAIN ANALYZE
SELECT * FROM "Event_Overall_Ratings" WHERE event_id = 1;
| QUERY PLAN |
|---|
| GroupAggregate (cost\=72.25..72.32 rows\=3 width\=95) (actual time\=1029.642..1029.647 rows\=1 loops\=1) |
| Group Key: eh.event_happening_id |
| -> Sort (cost\=72.25..72.26 rows\=3 width\=67) (actual time\=1029.557..1029.604 rows\=1 loops\=1) |
| Sort Key: eh.event_happening_id |
| Sort Method: quicksort Memory: 25kB |
| -> Nested Loop (cost\=4.90..72.22 rows\=3 width\=67) (actual time\=223.616..1029.548 rows\=1 loops\=1) |
| -> Index Scan using "Event_pkey" on "Event" e (cost\=0.29..8.30 rows\=1 width\=39) (actual time\=0.046..0.053 rows\=1 loops\=1) |
| Index Cond: (event_id \= 1) |
| -> Nested Loop (cost\=4.61..63.89 rows\=3 width\=36) (actual time\=223.566..1029.488 rows\=1 loops\=1) |
| -> Bitmap Heap Scan on "Event_Happening" eh (cost\=4.33..22.32 rows\=5 width\=24) (actual time\=0.054..805.890 rows\=5 loops\=1) |
| Recheck Cond: (event_id \= 1) |
| Heap Blocks: exact\=5 |
| -> Bitmap Index Scan on idx_event_happening_event_id (cost\=0.00..4.32 rows\=5 width\=0) (actual time\=0.029..0.029 rows\=5 loops\=1) |
| Index Cond: (event_id \= 1) |
| -> Index Scan using uq_rating_happening_user on "Event_Happening_Rating" ehr (cost\=0.29..8.30 rows\=1 width\=20) (actual time\=44.710..44.711 rows\=0 loops\=5) |
| Index Cond: (event_happening_id \= eh.event_happening_id) |
| Planning Time: 1.025 ms |
| Execution Time: 1029.756 ms |
- INSERT
EXPLAIN ANALYZE
INSERT INTO "Event_Happening_Rating" (rating_id, event_happening_id, user_id, rating, comment)
SELECT COALESCE(MAX(rating_id), 0) + 1, 1, 15, 9, 'Test rating'
FROM "Event_Happening_Rating";
| QUERY PLAN |
|---|
| Insert on "Event_Happening_Rating" (cost\=0.31..0.34 rows\=0 width\=0) (actual time\=0.405..0.407 rows\=0 loops\=1) |
| -> Subquery Scan on "*SELECT*" (cost\=0.31..0.34 rows\=1 width\=60) (actual time\=0.156..0.158 rows\=1 loops\=1) |
| -> Result (cost\=0.31..0.33 rows\=1 width\=52) (actual time\=0.154..0.155 rows\=1 loops\=1) |
| InitPlan 1 |
| -> Limit (cost\=0.29..0.31 rows\=1 width\=8) (actual time\=0.149..0.149 rows\=1 loops\=1) |
| -> Index Only Scan Backward using "Event_Happening_Rating_pkey" on "Event_Happening_Rating" "Event_Happening_Rating_1" (cost\=0.29..484.37 rows\=17872 width\=8) (actual time\=0.148..0.148 rows\=1 loops\=1) |
| Heap Fetches: 2 |
| Planning Time: 0.199 ms |
| Trigger for constraint fk_event_happening_rating_event_happening: time\=0.232 calls\=1 |
| Trigger for constraint fk_event_happening_rating_user: time\=0.234 calls\=1 |
| Execution Time: 0.918 ms |
- UPDATE
EXPLAIN ANALYZE
UPDATE "Event_Happening_Rating"
SET rating = 8, comment = 'New test rating'
WHERE event_happening_id = 1 AND user_id = 15;
| QUERY PLAN |
|---|
| Update on "Event_Happening_Rating" (cost\=0.29..8.31 rows\=0 width\=0) (actual time\=0.458..0.459 rows\=0 loops\=1) |
| -> Index Scan using uq_rating_happening_user on "Event_Happening_Rating" (cost\=0.29..8.31 rows\=1 width\=42) (actual time\=0.131..0.133 rows\=1 loops\=1) |
| Index Cond: ((event_happening_id \= 1) AND (user_id \= 15)) |
| Planning Time: 0.136 ms |
| Execution Time: 0.500 ms |
5. Оптимизација и индексирање:
За да се забрза пресметката, воведуваме композитен индекс:
- idx_ehr_happening_id_rating: Овој индекс ги содржи и
event_happening_idи самата оценка (rating). Ова овозможува базата да ги пресмета AVG и COUNT директно од структурата на индексот, без да мора да ги чита редовите од самата табела.
-- composite index to speed up grouping and aggregate calculations (AVG, COUNT) CREATE INDEX idx_ehr_happening_id_rating ON "Event_Happening_Rating"(event_happening_id, rating);
6. Резултат по оптимизација:
Времето на извршување се намали на 0.459 ms.
Подобрување: Забрзување од над 2200 пати.
Операциите за внесување и ажурирање остануваат под 1 ms, што значи дека композитниот индекс не додава голем товар при запишување.
- SELECT
EXPLAIN ANALYZE
SELECT * FROM "Event_Overall_Ratings" WHERE event_id = 1;
| QUERY PLAN |
|---|
| GroupAggregate (cost\=72.25..72.32 rows\=3 width\=95) (actual time\=0.387..0.389 rows\=1 loops\=1) |
| Group Key: eh.event_happening_id |
| -> Sort (cost\=72.25..72.26 rows\=3 width\=67) (actual time\=0.374..0.375 rows\=1 loops\=1) |
| Sort Key: eh.event_happening_id |
| Sort Method: quicksort Memory: 25kB |
| -> Nested Loop (cost\=4.90..72.22 rows\=3 width\=67) (actual time\=0.271..0.367 rows\=1 loops\=1) |
| -> Index Scan using "Event_pkey" on "Event" e (cost\=0.29..8.30 rows\=1 width\=39) (actual time\=0.126..0.128 rows\=1 loops\=1) |
| Index Cond: (event_id \= 1) |
| -> Nested Loop (cost\=4.61..63.89 rows\=3 width\=36) (actual time\=0.142..0.235 rows\=1 loops\=1) |
| -> Bitmap Heap Scan on "Event_Happening" eh (cost\=4.33..22.32 rows\=5 width\=24) (actual time\=0.067..0.144 rows\=5 loops\=1) |
| Recheck Cond: (event_id \= 1) |
| Heap Blocks: exact\=5 |
| -> Bitmap Index Scan on idx_event_happening_event_id (cost\=0.00..4.32 rows\=5 width\=0) (actual time\=0.038..0.038 rows\=5 loops\=1) |
| Index Cond: (event_id \= 1) |
| -> Index Scan using uq_rating_happening_user on "Event_Happening_Rating" ehr (cost\=0.29..8.30 rows\=1 width\=20) (actual time\=0.016..0.016 rows\=0 loops\=5) |
| Index Cond: (event_happening_id \= eh.event_happening_id) |
| Planning Time: 1.134 ms |
| Execution Time: 0.459 ms |
- INSERT
EXPLAIN ANALYZE
INSERT INTO "Event_Happening_Rating" (rating_id, event_happening_id, user_id, rating, comment)
SELECT COALESCE(MAX(rating_id), 0) + 1, 1, 20, 7, 'Test rating'
FROM "Event_Happening_Rating";
| QUERY PLAN |
|---|
| Insert on "Event_Happening_Rating" (cost\=0.31..0.34 rows\=0 width\=0) (actual time\=0.538..0.539 rows\=0 loops\=1) |
| -> Subquery Scan on "*SELECT*" (cost\=0.31..0.34 rows\=1 width\=60) (actual time\=0.111..0.112 rows\=1 loops\=1) |
| -> Result (cost\=0.31..0.33 rows\=1 width\=52) (actual time\=0.109..0.109 rows\=1 loops\=1) |
| InitPlan 1 |
| -> Limit (cost\=0.29..0.31 rows\=1 width\=8) (actual time\=0.104..0.105 rows\=1 loops\=1) |
| -> Index Only Scan Backward using "Event_Happening_Rating_pkey" on "Event_Happening_Rating" "Event_Happening_Rating_1" (cost\=0.29..485.40 rows\=17874 width\=8) (actual time\=0.102..0.103 rows\=1 loops\=1) |
| Heap Fetches: 1 |
| Planning Time: 0.195 ms |
| Trigger for constraint fk_event_happening_rating_event_happening: time\=0.223 calls\=1 |
| Trigger for constraint fk_event_happening_rating_user: time\=0.172 calls\=1 |
| Execution Time: 0.980 ms |
- UPDATE
EXPLAIN ANALYZE
UPDATE "Event_Happening_Rating"
SET rating = 8, comment = 'New test rating'
WHERE event_happening_id = 1 AND user_id = 15;
| QUERY PLAN |
|---|
| Update on "Event_Happening_Rating" (cost\=0.29..8.31 rows\=0 width\=0) (actual time\=0.482..0.483 rows\=0 loops\=1) |
| -> Index Scan using uq_rating_happening_user on "Event_Happening_Rating" (cost\=0.29..8.31 rows\=1 width\=42) (actual time\=0.096..0.098 rows\=1 loops\=1) |
| Index Cond: ((event_happening_id \= 1) AND (user_id \= 20)) |
| Planning Time: 0.163 ms |
| Execution Time: 0.548 ms |
Анализа и оптимизација на Event_Financial_Summary
Овој поглед ги сумира финансиските резултати за секој настан, прикажувајќи го вкупниот број на продадени билети, нето приходот по одбивање на рефундациите и посебно издвоената заработка од административните такси при враќање на влезниците.
CREATE VIEW "Event_Financial_Summary" AS
SELECT
e.event_id,
e.name AS event_name,
eh.event_happening_id,
eh.event_time,
COUNT(tp.purchase_id) AS total_tickets_sold,
-- total revenue
SUM(tp.purchase_amount) - SUM(CASE WHEN tr.refund_amount IS NOT NULL THEN tr.refund_amount ELSE 0 END) AS net_revenue,
-- refund taxes
SUM(CASE WHEN tr.refund_id IS NOT NULL THEN tp.purchase_amount - tr.refund_amount ELSE 0 END) AS refund_tax_profit
FROM "Event" e
JOIN "Event_Happening" eh ON e.event_id = eh.event_id
JOIN "Ticket" t ON eh.event_happening_id = t.event_happening_id
JOIN "Ticket_Purchase" tp ON t.ticket_id = tp.ticket_id
LEFT JOIN "Ticket_Refund" tr ON tp.purchase_id = tr.purchase_id
GROUP BY e.event_id, e.name, eh.event_happening_id, eh.event_time;
1. Примарен филтер:
Примарен филтер за овој поглед е event_id. Овој поглед е наменет за организаторите на настани кои сакаат да видат прецизен финансиски извештај за одреден настан во реално време.
2. Случај на употреба:
Овој поглед се користи за пресметка на чистата заработка откако ќе се одбијат сите рефундирани средства, како и за следење на профитот остварен од административните такси при рефундација. Поради комплексноста на агрегациите врз милиони редови, оптимизацијата тука е критична за да не се блокира работата на финансискиот сектор.
3. Иницијално време:
Иницијалното време за извршување изнесува 319.320 s (околу 5.3 минути). Ова е најбавното време во целиот проект, што го прави погледот неупотреблив во реални услови без индексирање.
4. Анализа на планот на извршување (без индекси):
Најголемиот товар паѓа на Parallel Seq Scan врз табелата Ticket_Purchase (над 315 s). Базата мора да ги помине сите 16 милиони записи за да ги пресмета сумите.
Се користи Parallel Hash Join кој бара огромна количина на меморија и процесорска моќ за да ги поврзе трансакциите со билетите и нивните соодветни настани.
- SELECT
EXPLAIN ANALYZE
SELECT * FROM "Event_Financial_Summary" WHERE event_id = 1;
| QUERY PLAN |
|---|
| Finalize GroupAggregate (cost\=300491.02..300511.03 rows\=5 width\=71) (actual time\=319308.288..319318.376 rows\=1 loops\=1) |
| Group Key: eh.event_happening_id |
| -> Gather Merge (cost\=300491.02..300510.84 rows\=10 width\=75) (actual time\=319308.204..319318.298 rows\=1 loops\=1) |
| Workers Planned: 2 |
| Workers Launched: 2 |
| -> Partial GroupAggregate (cost\=299491.00..299509.67 rows\=5 width\=75) (actual time\=319135.885..319135.894 rows\=0 loops\=3) |
| Group Key: eh.event_happening_id |
| -> Sort (cost\=299491.00..299493.66 rows\=1064 width\=79) (actual time\=319135.736..319135.767 rows\=388 loops\=3) |
| Sort Key: eh.event_happening_id |
| Sort Method: quicksort Memory: 25kB |
| Worker 0: Sort Method: quicksort Memory: 25kB |
| Worker 1: Sort Method: quicksort Memory: 160kB |
| -> Nested Loop Left Join (cost\=17569.44..299437.50 rows\=1064 width\=79) (actual time\=257215.363..319135.341 rows\=388 loops\=3) |
| -> Parallel Hash Join (cost\=17569.00..291013.94 rows\=1064 width\=67) (actual time\=257187.356..319052.018 rows\=388 loops\=3) |
| Hash Cond: (tp.ticket_id \= t.ticket_id) |
| -> Parallel Seq Scan on "Ticket_Purchase" tp (cost\=0.00..248438.67 rows\=6666668 width\=20) (actual time\=39.675..315154.157 rows\=5333334 loops\=3) |
| -> Parallel Hash (cost\=17536.03..17536.03 rows\=2638 width\=63) (actual time\=2787.389..2787.393 rows\=1442 loops\=3) |
| Buckets: 8192 Batches: 1 Memory Usage: 576kB |
| -> Nested Loop (cost\=1.14..17536.03 rows\=2638 width\=63) (actual time\=1483.997..2465.151 rows\=1442 loops\=3) |
| -> Nested Loop (cost\=0.57..1029.33 rows\=3 width\=55) (actual time\=632.603..753.210 rows\=2 loops\=3) |
| -> Parallel Index Scan using "Event_Happening_pkey" on "Event_Happening" eh (cost\=0.29..1004.39 rows\=3 width\=24) (actual time\=632.410..752.992 rows\=2 loops\=3) |
| Filter: (event_id \= 1) |
| Rows Removed by Filter: 10444 |
| -> Index Scan using "Event_pkey" on "Event" e (cost\=0.29..8.30 rows\=1 width\=39) (actual time\=0.094..0.100 rows\=1 loops\=5) |
| Index Cond: (event_id \= 1) |
| -> Index Scan using uq_ticket_event_happening_seat on "Ticket" t (cost\=0.56..5486.81 rows\=1542 width\=16) (actual time\=652.636..1027.008 rows\=865 loops\=5) |
| Index Cond: (event_happening_id \= eh.event_happening_id) |
| -> Index Scan using idx_ticket_refund_purchase_id on "Ticket_Refund" tr (cost\=0.43..7.92 rows\=1 width\=20) (actual time\=0.214..0.214 rows\=0 loops\=1163) |
| Index Cond: (purchase_id \= tp.purchase_id) |
| Planning Time: 2770.897 ms |
| JIT: |
| Functions: 96 |
| Options: Inlining false, Optimization false, Expressions true, Deforming true |
| Timing: Generation 9.017 ms (Deform 4.245 ms), Inlining 0.000 ms, Optimization 4.730 ms, Emission 79.393 ms, Total 93.140 ms |
| Execution Time: 319320.792 ms |
- INSERT
EXPLAIN ANALYZE
INSERT INTO "Ticket_Purchase" (purchase_id, ticket_id, user_id, qr_code, purchase_time, purchase_amount)
SELECT COALESCE(MAX(purchase_id), 0) + 1, 1, 1, 'QR-TEST-CODE-003', CURRENT_TIMESTAMP, 1500.00
FROM "Ticket_Purchase";
| QUERY PLAN |
|---|
| Insert on "Ticket_Purchase" (cost\=0.70..0.73 rows\=0 width\=0) (actual time\=1391.459..1391.461 rows\=0 loops\=1) |
| -> Subquery Scan on "*SELECT*" (cost\=0.70..0.73 rows\=1 width\=552) (actual time\=0.132..0.137 rows\=1 loops\=1) |
| -> Result (cost\=0.70..0.71 rows\=1 width\=88) (actual time\=0.106..0.109 rows\=1 loops\=1) |
| InitPlan 1 |
| -> Limit (cost\=0.43..0.70 rows\=1 width\=8) (actual time\=0.101..0.102 rows\=1 loops\=1) |
| -> Index Only Scan Backward using "Ticket_Purchase_pkey" on "Ticket_Purchase" "Ticket_Purchase_1" (cost\=0.43..4162966.51 rows\=16000002 width\=8) (actual time\=0.099..0.099 rows\=1 loops\=1) |
| Heap Fetches: 1 |
| Planning Time: 0.232 ms |
| Trigger for constraint fk_purchase_ticket: time\=1.278 calls\=1 |
| Trigger for constraint fk_purchase_user: time\=0.358 calls\=1 |
| Execution Time: 1393.154 ms |
- UPDATE
EXPLAIN ANALYZE
UPDATE "Ticket_Purchase"
SET purchase_amount = 1800.00
WHERE qr_code = 'QR-TEST-CODE-003';
| QUERY PLAN |
|---|
| Update on "Ticket_Purchase" (cost\=0.56..8.58 rows\=0 width\=0) (actual time\=0.266..0.267 rows\=0 loops\=1) |
| -> Index Scan using "Ticket_Purchase_qr_code_key" on "Ticket_Purchase" (cost\=0.56..8.58 rows\=1 width\=10) (actual time\=0.117..0.119 rows\=1 loops\=1) |
| Index Cond: ((qr_code)::text \= 'QR-TEST-CODE-003'::text) |
| Planning Time: 0.188 ms |
| Execution Time: 0.364 ms |
5. Оптимизација и индексирање:
За да се овозможи побрзо филтрирање и поврзување на трансакциите, воведени се:
idx_ticket_purchase_ticket_id: Клучен за брзо поврзување на трансакциите со табелата на билети.
idx_ticket_event_happening_id: Овозможува базата веднаш да ги групира билетите според термините на настанот.
idx_ticket_refund_purchase_id: Го забрзува LEFT JOIN делот каде што се проверува дали за одредена трансакција постои рефундација.
-- index for linking ticket purchases to the specific tickets CREATE INDEX idx_ticket_purchase_ticket_id ON "Ticket_Purchase"(ticket_id); -- index for the LEFT JOIN with refunds to calculate net revenue accurately CREATE INDEX idx_ticket_refund_purchase_id ON "Ticket_Refund"(purchase_id); -- index for linking tickets to scheduled events CREATE INDEX idx_ticket_event_happening_id ON "Ticket"(event_happening_id);
6. Резултат по оптимизација:
Времето на извршување е намалено на 1.510 s (1510 ms).
Подобрување: Ова е забрзување од 211 пати.
Иако 1.5 s се уште изгледа како „многу“ во споредба со претходните погледи, за анализа на 16 милиони трансакции со комплексни SUM и CASE операции, ова е извонреден резултат.
- SELECT
EXPLAIN ANALYZE
SELECT * FROM "Event_Financial_Summary" WHERE event_id = 1;
| QUERY PLAN |
|---|
| Finalize GroupAggregate (cost\=298955.06..298975.08 rows\=5 width\=71) (actual time\=1499.323..1508.016 rows\=1 loops\=1) |
| Group Key: eh.event_happening_id |
| -> Gather Merge (cost\=298955.06..298974.89 rows\=10 width\=75) (actual time\=1499.269..1507.962 rows\=1 loops\=1) |
| Workers Planned: 2 |
| Workers Launched: 2 |
| -> Partial GroupAggregate (cost\=297955.04..297973.71 rows\=5 width\=75) (actual time\=1463.528..1463.535 rows\=0 loops\=3) |
| Group Key: eh.event_happening_id |
| -> Sort (cost\=297955.04..297957.70 rows\=1064 width\=79) (actual time\=1463.377..1463.407 rows\=388 loops\=3) |
| Sort Key: eh.event_happening_id |
| Sort Method: quicksort Memory: 25kB |
| Worker 0: Sort Method: quicksort Memory: 25kB |
| Worker 1: Sort Method: quicksort Memory: 160kB |
| -> Nested Loop Left Join (cost\=16033.48..297901.55 rows\=1064 width\=79) (actual time\=1201.975..1463.060 rows\=388 loops\=3) |
| -> Parallel Hash Join (cost\=16033.05..289477.99 rows\=1064 width\=67) (actual time\=1201.913..1460.100 rows\=388 loops\=3) |
| Hash Cond: (tp.ticket_id \= t.ticket_id) |
| -> Parallel Seq Scan on "Ticket_Purchase" tp (cost\=0.00..248438.67 rows\=6666668 width\=20) (actual time\=0.091..663.287 rows\=5333334 loops\=3) |
| -> Parallel Hash (cost\=16000.08..16000.08 rows\=2638 width\=63) (actual time\=13.806..13.809 rows\=1442 loops\=3) |
| Buckets: 8192 Batches: 1 Memory Usage: 512kB |
| -> Nested Loop (cost\=1.01..16000.08 rows\=2638 width\=63) (actual time\=30.720..39.526 rows\=4325 loops\=1) |
| -> Nested Loop (cost\=0.57..1029.33 rows\=3 width\=55) (actual time\=30.641..37.392 rows\=5 loops\=1) |
| -> Parallel Index Scan using "Event_Happening_pkey" on "Event_Happening" eh (cost\=0.29..1004.39 rows\=3 width\=24) (actual time\=30.536..37.242 rows\=5 loops\=1) |
| Filter: (event_id \= 1) |
| Rows Removed by Filter: 31332 |
| -> Index Scan using "Event_pkey" on "Event" e (cost\=0.29..8.30 rows\=1 width\=39) (actual time\=0.021..0.023 rows\=1 loops\=5) |
| Index Cond: (event_id \= 1) |
| -> Index Scan using idx_ticket_event_happening_id on "Ticket" t (cost\=0.44..4974.83 rows\=1542 width\=16) (actual time\=0.052..0.324 rows\=865 loops\=5) |
| Index Cond: (event_happening_id \= eh.event_happening_id) |
| -> Index Scan using idx_ticket_refund_purchase_id on "Ticket_Refund" tr (cost\=0.43..7.92 rows\=1 width\=20) (actual time\=0.007..0.007 rows\=0 loops\=1163) |
| Index Cond: (purchase_id \= tp.purchase_id) |
| Planning Time: 1.604 ms |
| JIT: |
| Functions: 96 |
| Options: Inlining false, Optimization false, Expressions true, Deforming true |
| Timing: Generation 8.629 ms (Deform 3.959 ms), Inlining 0.000 ms, Optimization 3.603 ms, Emission 79.354 ms, Total 91.585 ms |
| Execution Time: 1510.555 ms |
- INSERT
EXPLAIN ANALYZE
INSERT INTO "Ticket_Purchase" (purchase_id, ticket_id, user_id, qr_code, purchase_time, purchase_amount)
SELECT COALESCE(MAX(purchase_id), 0) + 1, 1, 2, 'QR-TEST-CODE-004', CURRENT_TIMESTAMP, 1400.00
FROM "Ticket_Purchase";
| QUERY PLAN |
|---|
| Insert on "Ticket_Purchase" (cost\=0.70..0.73 rows\=0 width\=0) (actual time\=0.351..0.352 rows\=0 loops\=1) |
| -> Subquery Scan on "*SELECT*" (cost\=0.70..0.73 rows\=1 width\=552) (actual time\=0.145..0.147 rows\=1 loops\=1) |
| -> Result (cost\=0.70..0.71 rows\=1 width\=88) (actual time\=0.132..0.133 rows\=1 loops\=1) |
| InitPlan 1 |
| -> Limit (cost\=0.43..0.70 rows\=1 width\=8) (actual time\=0.126..0.127 rows\=1 loops\=1) |
| -> Index Only Scan Backward using "Ticket_Purchase_pkey" on "Ticket_Purchase" "Ticket_Purchase_1" (cost\=0.43..4162966.51 rows\=16000002 width\=8) (actual time\=0.124..0.124 rows\=1 loops\=1) |
| Heap Fetches: 2 |
| Planning Time: 0.208 ms |
| Trigger for constraint fk_purchase_ticket: time\=0.225 calls\=1 |
| Trigger for constraint fk_purchase_user: time\=0.176 calls\=1 |
| Execution Time: 0.802 ms |
- UPDATE
EXPLAIN ANALYZE
UPDATE "Ticket_Purchase"
SET purchase_amount = 1700.00
WHERE qr_code = 'QR-TEST-CODE-004';
| QUERY PLAN |
|---|
| Update on "Ticket_Purchase" (cost\=0.56..8.58 rows\=0 width\=0) (actual time\=0.161..0.162 rows\=0 loops\=1) |
| -> Index Scan using "Ticket_Purchase_qr_code_key" on "Ticket_Purchase" (cost\=0.56..8.58 rows\=1 width\=10) (actual time\=0.115..0.116 rows\=1 loops\=1) |
| Index Cond: ((qr_code)::text \= 'QR-TEST-CODE-004'::text) |
| Planning Time: 0.134 ms |
| Execution Time: 0.195 ms |
Анализа и оптимизација на Future_Events
Овој поглед служи за динамично генерирање на репертоарот, прикажувајќи ги исклучиво претстојните настани во реално време преку филтрирање на изминатите термини.
CREATE 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_city AS city
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 > CURRENT_TIMESTAMP во комбинација со venue_id. Корисниците најчесто сакаат да видат што се случува во нивниот град или во конкретен објект во блиска иднина.
2. Случај на употреба:
Овој поглед е „срцето“ на насловната страница. Секој пат кога корисникот ќе ја отвори апликацијата или ќе филтрира настани по локација, овој прашалник се извршува. Тука перформансите се клучни за корисничкото искуство - ако репертоарот се вчита побавно од 2 s, корисникот веројатно ќе ја напушти страницата.
3. Иницијално време:
Иницијалното време за извршување е многу ниско (0.193 ms), но забележливо е дека базата веќе користи постоечки уникатен индекс (uq_happening_time_venue). Проблемот тука не е брзината на еден прашалник, туку времето на планирање кое при комплексни филтри може да биде поголемо од самото извршување.
4. Анализа на планот на извршување (без индекси):
Базата користи Index Scan врз постоечко уникатно ограничување.
Сепак, при INSERT операцијата се појавува време од 573.660 ms, што е релативно високо за едноставно додавање на термин. Ова укажува на тоа дека базата троши време на проверка на интегритетот и пресметка на екстремните вредности (MAX).
- SELECT
EXPLAIN ANALYZE
SELECT * FROM "Future_Events"
WHERE venue_id = 1;
| QUERY PLAN |
|---|
| Nested Loop (cost\=0.86..26.26 rows\=1 width\=92) (actual time\=0.055..0.056 rows\=0 loops\=1) |
| -> Nested Loop (cost\=0.57..17.94 rows\=1 width\=63) (actual time\=0.054..0.055 rows\=0 loops\=1) |
| -> Index Scan using uq_happening_time_venue on "Event_Happening" eh (cost\=0.29..9.64 rows\=1 width\=32) (actual time\=0.054..0.054 rows\=0 loops\=1) |
| Index Cond: ((event_time > CURRENT_TIMESTAMP) AND (venue_id \= 1)) |
| -> Index Scan using "Event_pkey" on "Event" e (cost\=0.29..8.30 rows\=1 width\=39) (never executed) |
| Index Cond: (event_id \= eh.event_id) |
| -> Index Scan using "Venue_pkey" on "Venue" v (cost\=0.29..8.30 rows\=1 width\=37) (never executed) |
| Index Cond: (venue_id \= 1) |
| Planning Time: 4.258 ms |
| Execution Time: 0.193 ms |
- INSERT
EXPLAIN ANALYZE
INSERT INTO "Event_Happening" (event_happening_id, event_id, venue_id, event_time, duration)
VALUES ((SELECT MAX(event_happening_id) + 1 FROM "Event_Happening"), 1, 1, '2026-12-31 20:00:00', 120);
| QUERY PLAN |
|---|
| Insert on "Event_Happening" (cost\=0.33..0.34 rows\=0 width\=0) (actual time\=572.079..572.083 rows\=0 loops\=1) |
| InitPlan 2 |
| -> Result (cost\=0.31..0.33 rows\=1 width\=8) (actual time\=0.260..0.262 rows\=1 loops\=1) |
| InitPlan 1 |
| -> Limit (cost\=0.29..0.31 rows\=1 width\=8) (actual time\=0.256..0.258 rows\=1 loops\=1) |
| -> Index Only Scan Backward using "Event_Happening_pkey" on "Event_Happening" "Event_Happening_1" (cost\=0.29..822.34 rows\=31337 width\=8) (actual time\=0.255..0.256 rows\=1 loops\=1) |
| Heap Fetches: 0 |
| -> Result (cost\=0.00..0.01 rows\=1 width\=36) (actual time\=0.263..0.264 rows\=1 loops\=1) |
| Planning Time: 0.199 ms |
| Trigger for constraint fk_event_happening_event: time\=1.080 calls\=1 |
| Trigger for constraint fk_event_happening_venue: time\=0.448 calls\=1 |
| Execution Time: 573.660 ms |
- UPDATE
EXPLAIN ANALYZE
UPDATE "Event_Happening"
SET event_time = '2027-01-01 21:00:00'
WHERE event_happening_id = (SELECT MAX(event_happening_id) FROM "Event_Happening");
| QUERY PLAN |
|---|
| Update on "Event_Happening" (cost\=0.61..8.63 rows\=0 width\=0) (actual time\=0.569..0.570 rows\=0 loops\=1) |
| InitPlan 2 |
| -> Result (cost\=0.31..0.32 rows\=1 width\=8) (actual time\=0.292..0.293 rows\=1 loops\=1) |
| InitPlan 1 |
| -> Limit (cost\=0.29..0.31 rows\=1 width\=8) (actual time\=0.288..0.288 rows\=1 loops\=1) |
| -> Index Only Scan Backward using "Event_Happening_pkey" on "Event_Happening" "Event_Happening_1" (cost\=0.29..822.34 rows\=31337 width\=8) (actual time\=0.287..0.287 rows\=1 loops\=1) |
| Heap Fetches: 1 |
| -> Index Scan using "Event_Happening_pkey" on "Event_Happening" (cost\=0.29..8.30 rows\=1 width\=14) (actual time\=0.310..0.312 rows\=1 loops\=1) |
| Index Cond: (event_happening_id \= (InitPlan 2).col1) |
| Planning Time: 0.240 ms |
| Execution Time: 0.662 ms |
5. Оптимизација и индексирање:
За да се овозможи максимална ефикасност при различни комбинации на филтрирање (само по време, само по локација или двете заедно), се предлагаат:
idx_event_happening_time: За брзо хронолошко подредување.
idx_event_happening_venue_id: За брзо филтрирање по објект.
idx_event_happening_venue_time: Композитен индекс кој е идеален за прашалници кои бараат „претстојни настани во конкретна сала“.
-- index for optimizing chronological filtering of future events CREATE INDEX idx_event_happening_time ON "Event_Happening"(event_time); -- index for accelerating lookups of events scheduled at specific venues CREATE INDEX idx_event_happening_venue_id ON "Event_Happening"(venue_id); -- composite index to optimize queries filtering both by venue and upcoming time slots CREATE INDEX idx_event_happening_venue_time ON "Event_Happening"(venue_id, event_time);
6. Резултат по оптимизација:
Времето на извршување останува стабилно ниско (0.215 ms).
Најголемото подобрување се гледа кај INSERT операцијата, каде времето се намали од 573 ms на 1.278 ms. Ова е клучно бидејќи администраторите често додаваат нови термини во пакети, па ова забрзување значително го олеснува нивниот процес.
- SELECT
EXPLAIN ANALYZE
SELECT * FROM "Future_Events"
WHERE venue_id = 1;
| QUERY PLAN |
|---|
| Nested Loop (cost\=0.86..24.93 rows\=1 width\=92) (actual time\=0.153..0.157 rows\=1 loops\=1) |
| -> Nested Loop (cost\=0.57..16.61 rows\=1 width\=63) (actual time\=0.081..0.083 rows\=1 loops\=1) |
| -> Index Scan using idx_event_happening_venue_time on "Event_Happening" eh (cost\=0.29..8.31 rows\=1 width\=32) (actual time\=0.067..0.068 rows\=1 loops\=1) |
| Index Cond: ((venue_id \= 1) AND (event_time > CURRENT_TIMESTAMP)) |
| -> Index Scan using "Event_pkey" on "Event" e (cost\=0.29..8.30 rows\=1 width\=39) (actual time\=0.009..0.009 rows\=1 loops\=1) |
| Index Cond: (event_id \= eh.event_id) |
| -> Index Scan using "Venue_pkey" on "Venue" v (cost\=0.29..8.30 rows\=1 width\=37) (actual time\=0.070..0.071 rows\=1 loops\=1) |
| Index Cond: (venue_id \= 1) |
| Planning Time: 203.237 ms |
| Execution Time: 0.215 ms |
- INSERT
EXPLAIN ANALYZE
INSERT INTO "Event_Happening" (event_happening_id, event_id, venue_id, event_time, duration)
VALUES ((SELECT MAX(event_happening_id) + 1 FROM "Event_Happening"), 1, 1, '2026-11-15 19:00:00', 90);
| QUERY PLAN |
|---|
| Insert on "Event_Happening" (cost\=0.33..0.34 rows\=0 width\=0) (actual time\=0.726..0.728 rows\=0 loops\=1) |
| InitPlan 2 |
| -> Result (cost\=0.31..0.33 rows\=1 width\=8) (actual time\=0.281..0.282 rows\=1 loops\=1) |
| InitPlan 1 |
| -> Limit (cost\=0.29..0.31 rows\=1 width\=8) (actual time\=0.278..0.279 rows\=1 loops\=1) |
| -> Index Only Scan Backward using "Event_Happening_pkey" on "Event_Happening" "Event_Happening_1" (cost\=0.29..826.36 rows\=31338 width\=8) (actual time\=0.276..0.277 rows\=1 loops\=1) |
| Heap Fetches: 1 |
| -> Result (cost\=0.00..0.01 rows\=1 width\=36) (actual time\=0.284..0.284 rows\=1 loops\=1) |
| Planning Time: 0.235 ms |
| Trigger for constraint fk_event_happening_event: time\=0.252 calls\=1 |
| Trigger for constraint fk_event_happening_venue: time\=0.255 calls\=1 |
| Execution Time: 1.278 ms |
- UPDATE
EXPLAIN ANALYZE
UPDATE "Event_Happening"
SET event_time = '2026-11-20 20:00:00'
WHERE event_happening_id = (SELECT MAX(event_happening_id) FROM "Event_Happening");
| QUERY PLAN |
|---|
| Update on "Event_Happening" (cost\=0.61..8.63 rows\=0 width\=0) (actual time\=0.543..0.544 rows\=0 loops\=1) |
| InitPlan 2 |
| -> Result (cost\=0.31..0.32 rows\=1 width\=8) (actual time\=0.169..0.170 rows\=1 loops\=1) |
| InitPlan 1 |
| -> Limit (cost\=0.29..0.31 rows\=1 width\=8) (actual time\=0.166..0.166 rows\=1 loops\=1) |
| -> Index Only Scan Backward using "Event_Happening_pkey" on "Event_Happening" "Event_Happening_1" (cost\=0.29..826.36 rows\=31338 width\=8) (actual time\=0.165..0.165 rows\=1 loops\=1) |
| Heap Fetches: 1 |
| -> Index Scan using "Event_Happening_pkey" on "Event_Happening" (cost\=0.29..8.30 rows\=1 width\=14) (actual time\=0.183..0.185 rows\=1 loops\=1) |
| Index Cond: (event_happening_id \= (InitPlan 2).col1) |
| Planning Time: 0.283 ms |
| Execution Time: 0.616 ms |
Анализа и оптимизација на Available_Tickets
Овој поглед овозможува моментален увид во инвентарот на достапни седишта и автоматско враќање на рефундираните билети во понуда.
CREATE VIEW "Available_Tickets" AS
SELECT
t.ticket_id,
t.ticket_type,
t.base_price,
e.event_id,
e.name AS event_name,
eh.event_happening_id,
eh.event_time,
s.name AS section_name,
st.seat_number
FROM "Ticket" t
JOIN "Event_Happening" eh ON t.event_happening_id = eh.event_happening_id
JOIN "Event" e ON eh.event_id = e.event_id
JOIN "Seat" st ON t.seat_id = st.seat_id
JOIN "Section" s ON st.section_id = s.section_id
WHERE t.is_available = TRUE;
1. Примарен филтер:
Примарен филтер за овој поглед е is_available = TRUE во комбинација со event_id. Ова е најфреквентниот прашалник на платформата, бидејќи секој корисник што прегледува настан сака да знае кои седишта се слободни за продажба.
2. Случај на употреба:
Овој поглед го претставува инвентарот во реално време. Поради големиот волумен на податоци во табелата Ticket (околу 30 милиони записи), секое доцнење тука може да доведе до Race Condition (двајца корисници да го видат истото седиште како слободно). Ефикасноста тука не е само прашање на UX, туку и на интегритет на продажбата.
3. Иницијално време:
Иницијалното време за извршување изнесува 948.253 ms. Иако е под една секунда, ова е премногу бавно за систем со голем сообраќај каде илјадници корисници истовремено го повикуваат овој поглед.
4. Анализа на планот на извршување (без индекси):
Најголем проблем е Merge Join операцијата која мора да ги спои табелите Ticket и Seat. Бидејќи базата нема соодветен индекс за достапните карти, таа троши многу ресурси на сортирање и пребарување низ милиони редови.
Се појавуваат високи трошоци за читање од диск бидејќи базата не може веднаш да ги „отфрли“ веќе продадените билети.
- SELECT
EXPLAIN ANALYZE
SELECT * FROM "Available_Tickets"
WHERE event_id = 1;
| QUERY PLAN |
|---|
| Merge Join (cost\=27256.32..27455.75 rows\=811 width\=85) (actual time\=299.567..948.130 rows\=347 loops\=1) |
| Merge Cond: (st.seat_id \= t.seat_id) |
| -> Nested Loop (cost\=0.86..2908890.51 rows\=20753360 width\=19) (actual time\=19.526..667.820 rows\=1163 loops\=1) |
| -> Index Scan using "Seat_pkey" on "Seat" st (cost\=0.56..2383051.22 rows\=20753360 width\=20) (actual time\=0.716..648.333 rows\=1163 loops\=1) |
| -> Memoize (cost\=0.30..0.32 rows\=1 width\=15) (actual time\=0.016..0.016 rows\=1 loops\=1163) |
| Cache Key: st.section_id |
| Cache Mode: logical |
| Hits: 1156 Misses: 7 Evictions: 0 Overflows: 0 Memory Usage: 1kB |
| -> Index Scan using "Section_pkey" on "Section" s (cost\=0.29..0.31 rows\=1 width\=15) (actual time\=2.600..2.600 rows\=1 loops\=7) |
| Index Cond: (section_id \= st.section_id) |
| -> Sort (cost\=27253.12..27255.15 rows\=811 width\=82) (actual time\=280.018..280.056 rows\=347 loops\=1) |
| Sort Key: t.seat_id |
| Sort Method: quicksort Memory: 62kB |
| -> Nested Loop (cost\=5.05..27213.93 rows\=811 width\=82) (actual time\=0.827..279.855 rows\=347 loops\=1) |
| -> Nested Loop (cost\=4.61..30.67 rows\=5 width\=55) (actual time\=0.243..0.408 rows\=7 loops\=1) |
| -> Index Scan using "Event_pkey" on "Event" e (cost\=0.29..8.30 rows\=1 width\=39) (actual time\=0.141..0.153 rows\=1 loops\=1) |
| Index Cond: (event_id \= 1) |
| -> Bitmap Heap Scan on "Event_Happening" eh (cost\=4.33..22.32 rows\=5 width\=24) (actual time\=0.095..0.239 rows\=7 loops\=1) |
| Recheck Cond: (event_id \= 1) |
| Heap Blocks: exact\=6 |
| -> Bitmap Index Scan on idx_event_happening_event_id (cost\=0.00..4.32 rows\=5 width\=0) (actual time\=0.066..0.066 rows\=11 loops\=1) |
| Index Cond: (event_id \= 1) |
| -> Index Scan using idx_ticket_event_happening_id on "Ticket" t (cost\=0.44..5433.49 rows\=316 width\=35) (actual time\=39.884..39.910 rows\=50 loops\=7) |
| Index Cond: (event_happening_id \= eh.event_happening_id) |
| Filter: is_available |
| Rows Removed by Filter: 568 |
| Planning Time: 3.990 ms |
| Execution Time: 948.253 ms |
- INSERT
EXPLAIN ANALYZE
INSERT INTO "Ticket" (ticket_id, ticket_type, base_price, is_available, event_happening_id, seat_id)
VALUES ((SELECT MAX(ticket_id) + 1 FROM "Ticket"), 'Standard', 500.00, TRUE, 1, 999999);
| QUERY PLAN |
|---|
| Insert on "Ticket" (cost\=0.61..0.62 rows\=0 width\=0) (actual time\=0.429..0.430 rows\=0 loops\=1) |
| InitPlan 2 |
| -> Result (cost\=0.59..0.61 rows\=1 width\=8) (actual time\=0.215..0.216 rows\=1 loops\=1) |
| InitPlan 1 |
| -> Limit (cost\=0.56..0.59 rows\=1 width\=8) (actual time\=0.212..0.213 rows\=1 loops\=1) |
| -> Index Only Scan Backward using "Ticket_pkey" on "Ticket" "Ticket_1" (cost\=0.56..892367.26 rows\=28748980 width\=8) (actual time\=0.211..0.212 rows\=1 loops\=1) |
| Heap Fetches: 1 |
| -> Result (cost\=0.00..0.01 rows\=1 width\=147) (actual time\=0.218..0.218 rows\=1 loops\=1) |
| Planning Time: 0.211 ms |
| Trigger for constraint fk_ticket_event_happening: time\=0.325 calls\=1 |
| Trigger for constraint fk_ticket_seat: time\=0.243 calls\=1 |
| Execution Time: 1.045 ms |
- UPDATE
EXPLAIN ANALYZE
UPDATE "Ticket"
SET base_price = 600.00
WHERE ticket_id = (SELECT MAX(ticket_id) FROM "Ticket");
| QUERY PLAN |
|---|
| Update on "Ticket" (cost\=1.17..9.18 rows\=0 width\=0) (actual time\=0.340..0.341 rows\=0 loops\=1) |
| InitPlan 2 |
| -> Result (cost\=0.59..0.60 rows\=1 width\=8) (actual time\=0.270..0.271 rows\=1 loops\=1) |
| InitPlan 1 |
| -> Limit (cost\=0.56..0.59 rows\=1 width\=8) (actual time\=0.267..0.268 rows\=1 loops\=1) |
| -> Index Only Scan Backward using "Ticket_pkey" on "Ticket" "Ticket_1" (cost\=0.56..892367.26 rows\=28748980 width\=8) (actual time\=0.266..0.266 rows\=1 loops\=1) |
| Heap Fetches: 1 |
| -> Index Scan using "Ticket_pkey" on "Ticket" (cost\=0.56..8.58 rows\=1 width\=10) (actual time\=0.291..0.292 rows\=1 loops\=1) |
| Index Cond: (ticket_id \= (InitPlan 2).col1) |
| Planning Time: 0.244 ms |
| Execution Time: 0.393 ms |
5. Оптимизација и индексирање:
За овој поглед го воведуваме специјализираниот делумен индекс:
idx_ticket_is_available_true: Овој индекс ги содржи само редовите кадеis_availableе TRUE. Наместо да пребарува низ 30 милиони записи, базата сега гледа само во мал подмножество (на пр. 50,000 слободни билети).
- idx_ticket_event_happening_id: Овозможува моментално поврзување на билетите со конкретниот настан.
- idx_ticket_seat_id: Го забрзува поврзувањето со табелата
Seatза да се прикажат броевите на седиштата и секторите.
-- partial index for rapid retrieval of only unsold tickets by filtering out sold ones CREATE INDEX idx_ticket_is_available_true ON "Ticket"(is_available) WHERE is_available = TRUE; -- index for linking tickets to scheduled events to accelerate event-based filtering CREATE INDEX idx_ticket_event_happening_id ON "Ticket"(event_happening_id); -- index for optimizing seat-based joins to retrieve venue layout details for each ticket CREATE INDEX idx_ticket_seat_id ON "Ticket"(seat_id);
6. Резултат по оптимизација:
Времето на извршување падна на 3.882 ms.
Подобрување: Забрзување од околу 245 пати.
Времето на INSERT и UPDATE операциите останува минимално, што потврдува дека делумните индекси се многу „лесни“ за одржување бидејќи не се ажурираат при секоја промена, туку само кога се менува статусот на достапност.
- SELECT
EXPLAIN ANALYZE
SELECT * FROM "Available_Tickets"
WHERE event_id = 1;
| QUERY PLAN |
|---|
| Merge Join (cost\=26670.90..26870.11 rows\=793 width\=85) (actual time\=2.496..3.734 rows\=347 loops\=1) |
| Merge Cond: (st.seat_id \= t.seat_id) |
| -> Nested Loop (cost\=0.86..2908890.51 rows\=20753360 width\=19) (actual time\=0.270..1.289 rows\=1163 loops\=1) |
| -> Index Scan using "Seat_pkey" on "Seat" st (cost\=0.56..2383051.22 rows\=20753360 width\=20) (actual time\=0.049..0.516 rows\=1163 loops\=1) |
| -> Memoize (cost\=0.30..0.32 rows\=1 width\=15) (actual time\=0.000..0.000 rows\=1 loops\=1163) |
| Cache Key: st.section_id |
| Cache Mode: logical |
| Hits: 1156 Misses: 7 Evictions: 0 Overflows: 0 Memory Usage: 1kB |
| -> Index Scan using "Section_pkey" on "Section" s (cost\=0.29..0.31 rows\=1 width\=15) (actual time\=0.009..0.009 rows\=1 loops\=7) |
| Index Cond: (section_id \= st.section_id) |
| -> Sort (cost\=26667.76..26669.74 rows\=793 width\=82) (actual time\=2.213..2.236 rows\=347 loops\=1) |
| Sort Key: t.seat_id |
| Sort Method: quicksort Memory: 62kB |
| -> Nested Loop (cost\=5.05..26629.57 rows\=793 width\=82) (actual time\=0.566..2.095 rows\=347 loops\=1) |
| -> Nested Loop (cost\=4.61..30.67 rows\=5 width\=55) (actual time\=0.153..0.260 rows\=7 loops\=1) |
| -> Index Scan using "Event_pkey" on "Event" e (cost\=0.29..8.30 rows\=1 width\=39) (actual time\=0.077..0.083 rows\=1 loops\=1) |
| Index Cond: (event_id \= 1) |
| -> Bitmap Heap Scan on "Event_Happening" eh (cost\=4.33..22.32 rows\=5 width\=24) (actual time\=0.072..0.168 rows\=7 loops\=1) |
| Recheck Cond: (event_id \= 1) |
| Heap Blocks: exact\=6 |
| -> Bitmap Index Scan on idx_event_happening_event_id (cost\=0.00..4.32 rows\=5 width\=0) (actual time\=0.047..0.047 rows\=11 loops\=1) |
| Index Cond: (event_id \= 1) |
| -> Index Scan using idx_ticket_event_happening_id on "Ticket" t (cost\=0.44..5316.69 rows\=309 width\=35) (actual time\=0.234..0.253 rows\=50 loops\=7) |
| Index Cond: (event_happening_id \= eh.event_happening_id) |
| Filter: is_available |
| Rows Removed by Filter: 568 |
| Planning Time: 4.840 ms |
| Execution Time: 3.882 ms |
- INSERT
EXPLAIN ANALYZE
INSERT INTO "Ticket" (ticket_id, ticket_type, base_price, is_available, event_happening_id, seat_id)
VALUES ((SELECT MAX(ticket_id) + 1 FROM "Ticket"), 'Standard', 500.00, TRUE, 1, 888888);
| QUERY PLAN |
|---|
| Insert on "Ticket" (cost\=0.61..0.62 rows\=0 width\=0) (actual time\=0.545..0.546 rows\=0 loops\=1) |
| InitPlan 2 |
| -> Result (cost\=0.59..0.61 rows\=1 width\=8) (actual time\=0.092..0.093 rows\=1 loops\=1) |
| InitPlan 1 |
| -> Limit (cost\=0.56..0.59 rows\=1 width\=8) (actual time\=0.089..0.090 rows\=1 loops\=1) |
| -> Index Only Scan Backward using "Ticket_pkey" on "Ticket" "Ticket_1" (cost\=0.56..882762.56 rows\=28108400 width\=8) (actual time\=0.088..0.089 rows\=1 loops\=1) |
| Heap Fetches: 1 |
| -> Result (cost\=0.00..0.01 rows\=1 width\=147) (actual time\=0.095..0.095 rows\=1 loops\=1) |
| Planning Time: 0.250 ms |
| Trigger for constraint fk_ticket_event_happening: time\=0.473 calls\=1 |
| Trigger for constraint fk_ticket_seat: time\=207.063 calls\=1 |
| Execution Time: 208.133 ms |
- UPDATE
EXPLAIN ANALYZE
UPDATE "Ticket"
SET base_price = 700.00
WHERE ticket_id = (SELECT MAX(ticket_id) FROM "Ticket");
| QUERY PLAN |
|---|
| Update on "Ticket" (cost\=1.17..9.18 rows\=0 width\=0) (actual time\=0.168..0.169 rows\=0 loops\=1) |
| InitPlan 2 |
| -> Result (cost\=0.59..0.60 rows\=1 width\=8) (actual time\=0.033..0.034 rows\=1 loops\=1) |
| InitPlan 1 |
| -> Limit (cost\=0.56..0.59 rows\=1 width\=8) (actual time\=0.031..0.031 rows\=1 loops\=1) |
| -> Index Only Scan Backward using "Ticket_pkey" on "Ticket" "Ticket_1" (cost\=0.56..882762.56 rows\=28108400 width\=8) (actual time\=0.030..0.030 rows\=1 loops\=1) |
| Heap Fetches: 1 |
| -> Index Scan using "Ticket_pkey" on "Ticket" (cost\=0.56..8.58 rows\=1 width\=10) (actual time\=0.045..0.046 rows\=1 loops\=1) |
| Index Cond: (ticket_id \= (InitPlan 2).col1) |
| Planning Time: 0.242 ms |
| Execution Time: 0.397 ms |
Last modified
8 days ago
Last modified on 05/17/26 20:22:19
Note:
See TracWiki
for help on using the wiki.
