Оптимизација на прашалници

Анализа и оптимизација на Venue_Layout

Овој поглед ја прикажува деталната физичка структура на секој објект, декомпонирајќи го просторот од ниво на локација до конкретно седиште. Со линеарно поврзување на хиерархијата Venue -> Section -> Seat, погледот овозможува брза верификација на капацитетот и распоредот, што е неопходно за логистичко планирање на настаните.

CREATE OR REPLACE 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.row_number, 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 (ID на објектот), бидејќи најчестото пребарување во апликацијата е насочено кон визуелизација или вчитување на комплетната мапа на седишта за еден конкретен објект кога корисникот сака да купи билет.

2. Случај на употреба:

Погледот се користи при интерактивниот приказ на салата или стадионот во корисничкиот интерфејс. Кога купувачот ќе избере одреден настан, апликацијата мора веднаш да го исцрта распоредот на седишта по секции и редови за тој објект. Перформансите тука директно влијаат врз „time-to-interactive“ метриката на корисничката страница за избор на место.

3. Иницијално време:

• SELECT: 2.190 ms (Времето е мерено во продукциска околина пред оптимизација).
• INSERT: 4.752 ms (Време потребно за запишување на нов ред во табелата Seat заедно со проверка на сите интегритетни констреинти).
• UPDATE: 0.133 ms (Инстантна операција базирана на примарен клуч).

4. Анализа на планот на извршување (без индекси):

При SELECT операцијата, PostgreSQL паметно ги користи веќе постоечките уникатни индекси генерирани од бизнис констреинтите (uq_section_venue_name на Section и uq_seat_section_number на Seat). Планерот користи Nested Loop операција за спојување на табелите: прво го наоѓа објектот по ID, потоа преку venue_id ги лоцира сите секции, и на крај за секоја секција извршува Index Scan за да ги повлече соодветните седишта. Оваа патека овозможува брз пристап без потреба од скапи секвенцијални скенирања.

• SELECT

EXPLAIN ANALYZE
SELECT *
FROM "Venue_Layout"
WHERE venue_id = 1;

QUERY PLAN
Nested Loop (cost\=1.01..9625.30 rows\=2264 width\=54) (actual time\=0.868..2.190 rows\=775.00 loops\=1)
Buffers shared hit\=21 read\=13 dirtied\=1
-> Nested Loop (cost\=0.57..25.51 rows\=6 width\=38) (actual time\=0.622..0.627 rows\=5.00 loops\=1)
Buffers shared read\=6
-> Index Scan using ""Venue_pkey"" on ""Venue"" v (cost\=0.29..8.30 rows\=1 width\=28) (actual time\=0.071..0.072 rows\=1.00 loops\=1)
Index Cond (venue_id \= 1)
Index Searches 1
Buffers shared read\=3
-> Index Scan using uq_section_venue_name on ""Section"" s (cost\=0.29..17.14 rows\=6 width\=18) (actual time\=0.548..0.550 rows\=5.00 loops\=1)
Index Cond (venue_id \= 1)
Index Searches 1
Buffers shared read\=3
-> Index Scan using uq_seat_section_number on ""Seat"" st (cost\=0.44..1590.95 rows\=901 width\=24) (actual time\=0.056..0.282 rows\=155.00 loops\=5)
Index Cond (section_id \= s.section_id)
Index Searches 5
Buffers shared hit\=21 read\=7 dirtied\=1
Planning
Buffers shared hit\=14 read\=22
Planning Time 5.752 ms
Execution Time 2.271 ms

• INSERT

EXPLAIN ANALYZE
INSERT INTO "Seat" (seat_id, section_id, row_number, seat_number)
VALUES (99999999, 1, 1, 99);

QUERY PLAN
Insert on ""Seat"" (cost\=0.00..0.01 rows\=0 width\=0) (actual time\=4.492..4.493 rows\=0.00 loops\=1)
Buffers shared hit\=5 read\=6 dirtied\=3
-> Result (cost\=0.00..0.01 rows\=1 width\=24) (actual time\=0.001..0.001 rows\=1.00 loops\=1)
Planning Time 0.033 ms
Trigger for constraint fk_seat_section time\=0.243 calls\=1
Execution Time 4.752 ms

• UPDATE

EXPLAIN ANALYZE
UPDATE "Seat" SET seat_number = 100 WHERE seat_id = 99999999;

QUERY PLAN
Update on ""Seat"" (cost\=0.44..8.46 rows\=0 width\=0) (actual time\=0.101..0.102 rows\=0.00 loops\=1)
Buffers shared hit\=12
-> Index Scan using ""Seat_pkey"" on ""Seat"" (cost\=0.44..8.46 rows\=1 width\=10) (actual time\=0.031..0.032 rows\=1.00 loops\=1)
Index Cond (seat_id \= 99999999)
Index Searches 1
Buffers shared hit\=4
Planning Time 0.125 ms
Execution Time 0.133 ms

5. Оптимизација и индексирање:

Бидејќи постоечките уникатни констреинти веќе идеално ги покриваат JOIN релациите, креирањето на дополнителни индекси е непотребно и би довело до залудно трошење на мемориски ресурси и забавување на запишувањето (INSERT/UPDATE). Погледот е оценет како оптимален во својата тековна форма. Доволно е само да се изврши:

ANALYZE "Venue";
ANALYZE "Section";
ANALYZE "Seat";

Анализа и оптимизација на User_Tickets

Овој поглед обезбедува сеопфатен хронолошки приказ на сите трансакции по корисник. Покрај основните информации како настан, цена и QR-код, погледот инкорпорира податоци за рефундации (преку LEFT JOIN со табелите за рефундација), овозможувајќи моментна идентификација на статусот на секој билет. Погледот е филтриран само за активни корисници, со што се елиминираат податоците од неактивни профили.

CREATE OR REPLACE VIEW "User_Tickets" AS
SELECT u.user_id, u.username, toi.order_item_id, t.ticket_id, e.event_id, e.name AS event_name, eh.event_time,
       toi.qr_code, toi.item_price AS price_paid, tri.refund_item_id, tr.refund_time
FROM "User" u
    JOIN "Regular_User" ru ON u.user_id = ru.user_id
    JOIN "Ticket_Order" o ON ru.user_id = o.user_id
    JOIN "Ticket_Order_Item" toi ON o.order_id = toi.order_id
    JOIN "Ticket" t ON toi.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_Item" tri ON toi.order_item_id = tri.order_item_id
    LEFT JOIN "Ticket_Refund" tr ON tri.refund_id = tr.refund_id
WHERE u.is_active = TRUE;

Погледот е веќе најоптимално напишан со користење на постоечките уникатни констреинти и не бара дополнителни модификации.

1. Примарен филтер:

Примарен филтер за овој поглед е user_id (ID на корисникот), бидејќи најчестото и критично пребарување е кога најавениот клиент ја отвора својата корисничка профилна страница („My Tickets“) за да ги види своите активни или минати билети.

2. Случај на употреба:

Погледот претставува јадро на апликацијата во делот за дигитални билети. Секојпат кога корисникот сака да го прикаже својот QR-код на билетот за некој настан, системот го извршува овој прашалник. Доцнење на овој одзив предизвикува лошо корисничко искуство и застој на влезните капии при проверка на билетите.

3. Иницијално време:

• SELECT: 375.993 ms (Исклучително бавно поради комплексноста од поврзување на 9 табели и секвенцијално скенирање).
• INSERT: 0.523 ms
• UPDATE: 0.118 ms

4. Анализа на планот на извршување (без индекси):

Со воведувањето на новите B-tree индекси, планерот го напушти паралелното секвенцијално скенирање и премина кон Nested Loop операција. Ова овозможува базата да ја најде нарачката на корисникот преку idx_ticket_order_user_id во само еден чекор, а потоа со преостанатите индекси директно да ги „отвори“ ставките на нарачката (Ticket_Order_Item) и билетите (Ticket), избегнувајќи пребарување низ целиот диск.

• SELECT

EXPLAIN ANALYZE
SELECT *
FROM "User_Tickets"
WHERE user_id = 5;

QUERY PLAN
Gather (cost\=110699.49..459840.66 rows\=4 width\=145) (actual time\=370.701..375.993 rows\=0.00 loops\=1)
Workers Planned 2
Workers Launched 2
Buffers shared read\=82353
-> Nested Loop Left Join (cost\=109699.49..458840.26 rows\=2 width\=145) (actual time\=312.925..312.929 rows\=0.00 loops\=3)
Buffers shared read\=82353
-> Nested Loop Left Join (cost\=109699.07..458836.23 rows\=2 width\=145) (actual time\=312.924..312.928 rows\=0.00 loops\=3)
Buffers shared read\=82353
-> Nested Loop (cost\=109698.64..458832.18 rows\=2 width\=129) (actual time\=312.923..312.927 rows\=0.00 loops\=3)
Buffers shared read\=82353
-> Nested Loop (cost\=109698.35..458828.40 rows\=2 width\=98) (actual time\=312.923..312.927 rows\=0.00 loops\=3)
Buffers shared read\=82353
-> Nested Loop (cost\=109698.07..458824.71 rows\=2 width\=90) (actual time\=312.923..312.926 rows\=0.00 loops\=3)
Buffers shared read\=82353
-> Nested Loop (cost\=109697.63..458819.48 rows\=2 width\=82) (actual time\=312.922..312.925 rows\=0.00 loops\=3)
Buffers shared read\=82353
-> Parallel Hash Join (cost\=109696.77..458802.55 rows\=2 width\=62) (actual time\=312.921..312.924 rows\=0.00 loops\=3)
Hash Cond (toi.order_id \= o.order_id)
Buffers shared read\=82353
-> Parallel Seq Scan on ""Ticket_Order_Item"" toi (cost\=0.00..326137.01 rows\=8750001 width\=62) (never executed)
-> Parallel Hash (cost\=109696.76..109696.76 rows\=1 width\=16) (actual time\=312.874..312.875 rows\=0.00 loops\=3)
Buckets 1024 Batches 1 Memory Usage 0kB
Buffers shared read\=82353
-> Parallel Seq Scan on ""Ticket_Order"" o (cost\=0.00..109696.76 rows\=1 width\=16) (actual time\=312.664..312.664 rows\=0.00 loops\=3)
Filter (user_id \= 5)
Rows Removed by Filter 1750000
Buffers shared read\=82353
-> Materialize (cost\=0.86..16.91 rows\=1 width\=36) (never executed)
-> Nested Loop (cost\=0.86..16.91 rows\=1 width\=36) (never executed)
-> Index Scan using ""User_pkey"" on ""User"" u (cost\=0.43..8.45 rows\=1 width\=28) (never executed)
Index Cond (user_id \= 5)
Filter is_active
Index Searches 0
-> Index Only Scan using ""Regular_User_pkey"" on ""Regular_User"" ru (cost\=0.43..8.45 rows\=1 width\=8) (never executed)
Index Cond (user_id \= 5)
Heap Fetches 0
Index Searches 0
-> Index Scan using ""Ticket_pkey"" on ""Ticket"" t (cost\=0.44..2.61 rows\=1 width\=16) (never executed)
Index Cond (ticket_id \= toi.ticket_id)
Index Searches 0
-> Index Scan using ""Event_Happening_pkey"" on ""Event_Happening"" eh (cost\=0.29..1.85 rows\=1 width\=24) (never executed)
Index Cond (event_happening_id \= t.event_happening_id)
Index Searches 0
-> Index Scan using ""Event_pkey"" on ""Event"" e (cost\=0.29..1.89 rows\=1 width\=39) (never executed)
Index Cond (event_id \= eh.event_id)
Index Searches 0
-> Index Scan using ""Ticket_Refund_Item_order_item_id_key"" on ""Ticket_Refund_Item"" tri (cost\=0.43..2.02 rows\=1 width\=24) (never executed)
Index Cond (order_item_id \= toi.order_item_id)
Index Searches 0
-> Index Scan using ""Ticket_Refund_pkey"" on ""Ticket_Refund"" tr (cost\=0.42..2.02 rows\=1 width\=16) (never executed)
Index Cond (refund_id \= tri.refund_id)
Index Searches 0
Planning
Buffers shared hit\=33 read\=44 dirtied\=2
Planning Time 13.836 ms
Execution Time 376.133 ms

• INSERT

EXPLAIN ANALYZE
INSERT INTO "Ticket_Order_Item" (order_item_id, order_id, ticket_id, item_price, qr_code)
VALUES (99999999, 1, 1, 1200.00, '3ebd5fa12ea8781d1e9ae4333484984a');

QUERY PLAN
Insert on ""Ticket_Order_Item"" (cost\=0.00..0.01 rows\=0 width\=0) (actual time\=0.049..0.049 rows\=0.00 loops\=1)
Buffers shared hit\=5 dirtied\=1
-> Result (cost\=0.00..0.01 rows\=1 width\=556) (actual time\=0.001..0.001 rows\=1.00 loops\=1)
Planning Time 0.028 ms
Trigger for constraint fk_item_order time\=0.251 calls\=1
Trigger for constraint fk_item_ticket time\=0.210 calls\=1
Execution Time 0.523 ms

• UPDATE

EXPLAIN ANALYZE
UPDATE "Ticket_Order_Item" SET qr_code = '3ebd5fa12ec8781d1e9ae4333484984a'
WHERE order_item_id = 99999999;

QUERY PLAN
Update on ""Ticket_Order_Item"" (cost\=0.44..8.46 rows\=0 width\=0) (actual time\=0.085..0.086 rows\=0.00 loops\=1)
Buffers shared hit\=7
-> Index Scan using ""Ticket_Order_Item_pkey"" on ""Ticket_Order_Item"" (cost\=0.44..8.46 rows\=1 width\=522) (actual time\=0.039..0.040 rows\=1.00 loops\=1)
Index Cond (order_item_id \= 99999999)
Index Searches 1
Buffers shared hit\=4
Planning Time 0.173 ms
Execution Time 0.118 ms

5. Оптимизација и индексирање:

За драстично кратење на времето, воведени се следните индекси:

• idx_ticket_order_user_id: За директно мапирање на корисник кон неговите нарачки.
• idx_toi_order_id: За забрзување на поврзувањето на ставки од нарачка.
• idx_toi_ticket_id: За брзо поврзување на ставка од нарачка со конкретен билет.

Извршен е ANALYZE за обновување на статистиките на планерот.

CREATE INDEX CONCURRENTLY idx_ticket_order_user_id
ON "Ticket_Order" (user_id);

CREATE INDEX CONCURRENTLY idx_toi_order_id
ON "Ticket_Order_Item" (order_id);

CREATE INDEX CONCURRENTLY idx_toi_ticket_id
ON "Ticket_Order_Item" (ticket_id);


ANALYZE "Ticket_Order";
ANALYZE "Ticket_Order_Item";

6. Резултат по оптимизација:

Времето за SELECT е намалено на ~1.24 ms, што претставува забрзување од преку 300 пати. Читањето од диск е сведено на минимум, а сите операции се извршуваат директно во RAM меморијата преку индексните структури. Системот сега работи со конзистентна брзина, дури и под оптоварување.

• SELECT

EXPLAIN ANALYZE
SELECT *
FROM "User_Tickets"
WHERE user_id = 99;

QUERY PLAN
Nested Loop Left Join (cost\=3.59..75.95 rows\=4 width\=145) (actual time\=1.244..1.247 rows\=0.00 loops\=1)
Buffers shared hit\=1 read\=10
-> Nested Loop Left Join (cost\=3.17..67.89 rows\=4 width\=145) (actual time\=1.243..1.246 rows\=0.00 loops\=1)
Buffers shared hit\=1 read\=10
-> Nested Loop (cost\=2.74..59.80 rows\=4 width\=129) (actual time\=1.242..1.245 rows\=0.00 loops\=1)
Buffers shared hit\=1 read\=10
-> Nested Loop (cost\=2.46..52.24 rows\=4 width\=98) (actual time\=1.242..1.244 rows\=0.00 loops\=1)
Buffers shared hit\=1 read\=10
-> Nested Loop (cost\=2.17..44.84 rows\=4 width\=90) (actual time\=1.241..1.244 rows\=0.00 loops\=1)
Buffers shared hit\=1 read\=10
-> Nested Loop (cost\=1.73..34.40 rows\=4 width\=82) (actual time\=1.241..1.243 rows\=0.00 loops\=1)
Buffers shared hit\=1 read\=10
-> Nested Loop (cost\=1.29..25.36 rows\=1 width\=36) (actual time\=1.240..1.242 rows\=0.00 loops\=1)
Buffers shared hit\=1 read\=10
-> Nested Loop (cost\=0.86..16.91 rows\=1 width\=36) (actual time\=1.119..1.123 rows\=1.00 loops\=1)
Buffers shared hit\=1 read\=7
-> Index Scan using ""User_pkey"" on ""User"" u (cost\=0.43..8.45 rows\=1 width\=28) (actual time\=0.716..0.718 rows\=1.00 loops\=1)
Index Cond (user_id \= 99)
Filter is_active
Index Searches 1
Buffers shared read\=4
-> Index Only Scan using ""Regular_User_pkey"" on ""Regular_User"" ru (cost\=0.43..8.45 rows\=1 width\=8) (actual time\=0.398..0.400 rows\=1.00 loops\=1)
Index Cond (user_id \= 99)
Heap Fetches 0
Index Searches 1
Buffers shared hit\=1 read\=3
-> Index Scan using idx_ticket_order_user_id on ""Ticket_Order"" o (cost\=0.43..8.44 rows\=1 width\=16) (actual time\=0.116..0.116 rows\=0.00 loops\=1)
Index Cond (user_id \= 99)
Index Searches 1
Buffers shared read\=3

• INSERT

EXPLAIN ANALYZE
INSERT INTO "Ticket_Order_Item" (order_item_id, order_id, ticket_id, item_price, qr_code)
VALUES (77777777, 1, 1, 1200.00, '3ebc5fd22ec8681d1e9ae4333484984a');

QUERY PLAN
Insert on ""Ticket_Order_Item"" (cost\=0.00..0.01 rows\=0 width\=0) (actual time\=0.273..0.273 rows\=0.00 loops\=1)
Buffers shared hit\=11 read\=3 dirtied\=3
-> Result (cost\=0.00..0.01 rows\=1 width\=556) (actual time\=0.001..0.001 rows\=1.00 loops\=1)
Planning Time 0.031 ms
Trigger for constraint fk_item_order time\=0.241 calls\=1
Trigger for constraint fk_item_ticket time\=2.938 calls\=1
Execution Time 3.470 ms

• UPDATE

EXPLAIN ANALYZE
UPDATE "Ticket_Order_Item" SET qr_code = '3ebc5fd20ec8681d1e9ee5733484984a' WHERE order_item_id = 77777777;

QUERY PLAN
Update on ""Ticket_Order_Item"" (cost\=0.44..8.46 rows\=0 width\=0) (actual time\=0.102..0.103 rows\=0.00 loops\=1)
Buffers shared hit\=7
-> Index Scan using ""Ticket_Order_Item_pkey"" on ""Ticket_Order_Item"" (cost\=0.44..8.46 rows\=1 width\=522) (actual time\=0.049..0.051 rows\=1.00 loops\=1)
Index Cond (order_item_id \= 77777777)
Index Searches 1
Buffers shared hit\=4
Planning Time 0.160 ms
Execution Time 0.179 ms

Анализа и оптимизација на Future_Events

Овој поглед служи како динамичен извор за репертоарот на платформата. Ги изолира само идните настани (eh.event_time > CURRENT_TIMESTAMP) кои се означени како активни, прикажувајќи ги со нивната целосна географска локација. Оптимизиран е за брзо пребарување од страна на крајните корисници при избор на термин.

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 AND e.is_active = TRUE;

Погледот е веќе најоптимално напишан со користење на постоечките уникатни констреинти и не бара дополнителни модификации.

1. Примарен филтер:

Примарен филтер е eh.event_time > CURRENT_TIMESTAMP. Овој услов го дефинира временскиот опсег на податоците што се прикажуваат, овозможувајќи системско филтрирање на сите завршени настани.

2. Случај на употреба:

Погледот се користи на почетната страница на апликацијата („Coming Soon“ секцијата). Корисниците овде ги пребаруваат идните можности за забава, па брзината на овој поглед е критична за првичниот впечаток и брзото навигациско искуство.

3. Иницијално време:

• SELECT: 91.702 ms (Пред индексирање, со секвенцијално скенирање на целата табела Event_Happening)
• INSERT: 3.157 ms
• UPDATE: 0.102 ms

4. Анализа на планот на извршување (без индекси):

Со воведувањето на индексот idx_event_happening_time, планерот го замени скапото секвенцијално скенирање со Bitmap Index Scan. Базата сега многу побрзо ги наоѓа само оние записи каде што датумот е во иднина, користејќи ја индексната структура наместо да ја чита секоја редица од табелата. Потоа се врши ефикасен Hash Join со табелата Venue.

• SELECT

EXPLAIN ANALYZE
SELECT *
FROM "Future_Events"
WHERE city = 'London';

QUERY PLAN
Nested Loop (cost\=263.49..1419.10 rows\=222 width\=121) (actual time\=3.997..91.702 rows\=230.00 loops\=1)
Buffers shared hit\=745 read\=877
-> Hash Join (cost\=263.20..1234.27 rows\=331 width\=90) (actual time\=3.433..19.179 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\=12944 width\=32) (actual time\=1.890..14.843 rows\=12961.00 loops\=1)
Filter (event_time > CURRENT_TIMESTAMP)
Rows Removed by Filter 18377
Buffers shared hit\=1 read\=466
-> Hash (cost\=260.00..260.00 rows\=256 width\=66) (actual time\=1.525..1.527 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.160..1.467 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.56 rows\=1 width\=39) (actual time\=0.211..0.211 rows\=0.68 loops\=340)
Index Cond (event_id \= eh.event_id)
Filter is_active
Rows Removed by Filter 0
Index Searches 340
Buffers shared hit\=743 read\=277
Planning
Buffers shared hit\=31 read\=8
Planning Time 0.685 ms
Execution Time 91.820 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\=2.317..2.317 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.001..0.001 rows\=1.00 loops\=1)
Planning Time 0.029 ms
Trigger for constraint fk_happening_event time\=0.669 calls\=1
Trigger for constraint fk_happening_venue time\=0.152 calls\=1
Execution Time 3.157 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.075..0.076 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.027..0.028 rows\=1.00 loops\=1)
Index Cond (event_happening_id \= 99999999)
Index Searches 1
Buffers shared hit\=3
Planning Time 0.096 ms
Execution Time 0.102 ms

5. Оптимизација и индексирање:

Имплементиран е idx_event_happening_time (B-tree индекс) на колоната event_time. Ова е идеално за „range queries“ (пребарување на опсег), бидејќи овозможува базата да ги „прескокне“ сите стари настани и да започне со читање директно од сегашниот момент па натаму.

CREATE INDEX CONCURRENTLY idx_event_happening_time
ON "Event_Happening" (event_time);


ANALYZE "Event_Happening";

6. Резултат по оптимизација:

Времето за SELECT е намалено на ~13.24 ms, што претставува значително подобрување (~7 пати побрзо). Системот сега инстантно ги прикажува претстојните настани без притоа да го оптоварува хард дискот со непотребно читање на историски податоци.

• SELECT

EXPLAIN ANALYZE
SELECT *
FROM "Future_Events"
WHERE city = 'Berlin';

QUERY PLAN
Nested Loop (cost\=509.92..1258.59 rows\=64 width\=121) (actual time\=3.296..13.248 rows\=73.00 loops\=1)
Buffers shared hit\=204 read\=780
-> Hash Join (cost\=509.63..1205.01 rows\=96 width\=90) (actual time\=3.183..9.204 rows\=114.00 loops\=1)
Hash Cond (eh.venue_id \= v.venue_id)
Buffers shared hit\=4 read\=638
-> Bitmap Heap Scan on ""Event_Happening"" eh (cost\=248.71..910.06 rows\=12957 width\=32) (actual time\=1.386..5.330 rows\=12961.00 loops\=1)
Recheck Cond (event_time > CURRENT_TIMESTAMP)
Heap Blocks exact\=467
Buffers shared hit\=4 read\=503
-> Bitmap Index Scan on idx_event_happening_time (cost\=0.00..245.47 rows\=12957 width\=0) (actual time\=1.236..1.236 rows\=12962.00 loops\=1)
Index Cond (event_time > CURRENT_TIMESTAMP)
Index Searches 1
Buffers shared hit\=3 read\=37
-> Hash (cost\=260.00..260.00 rows\=74 width\=66) (actual time\=1.755..1.755 rows\=74.00 loops\=1)
Buckets 1024 Batches 1 Memory Usage 15kB
Buffers shared read\=135
-> Seq Scan on ""Venue"" v (cost\=0.00..260.00 rows\=74 width\=66) (actual time\=0.118..1.700 rows\=74.00 loops\=1)
Filter ((address_city) text \= 'Berlin' text)
Rows Removed by Filter 9926
Buffers shared read\=135
-> Index Scan using ""Event_pkey"" on ""Event"" e (cost\=0.29..0.56 rows\=1 width\=39) (actual time\=0.034..0.034 rows\=0.64 loops\=114)
Index Cond (event_id \= eh.event_id)
Filter is_active
Rows Removed by Filter 0
Index Searches 114
Buffers shared hit\=200 read\=142
Planning
Buffers shared hit\=68 read\=3
Planning Time 0.831 ms
Execution Time 13.311 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\=3.942..3.943 rows\=0.00 loops\=1)
Buffers shared hit\=7 read\=5 dirtied\=3
-> Result (cost\=0.00..0.01 rows\=1 width\=100) (actual time\=0.001..0.002 rows\=1.00 loops\=1)
Planning Time 0.032 ms
Trigger for constraint fk_happening_event time\=0.164 calls\=1
Trigger for constraint fk_happening_venue time\=0.061 calls\=1
Execution Time 4.192 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.072..0.072 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.021..0.022 rows\=1.00 loops\=1)
Index Cond (event_happening_id \= 88888888)
Index Searches 1
Buffers shared hit\=3
Planning Time 0.104 ms
Execution Time 0.119 ms

Анализа и оптимизација на Available_Tickets

Овој поглед ја нуди моменталната „понуда“ на слободни седишта. Имплементира бизнис логика за динамично ценообразовање: во реално време ја пресметува крајната цена на билетот, применувајќи го соодветниот попуст (price_discount_percent) врз база на тековниот временски период (Event_Period). Со ова, корисникот секогаш добива ажурирана цена при резервација.

CREATE OR REPLACE VIEW "Available_Tickets" AS
SELECT t.ticket_id, ROUND(t.base_price * (1 - COALESCE(ep.price_discount_percent, 0) / 100.0), 2) AS price,
       e.event_id, e.name AS event_name, eh.event_happening_id, eh.event_time, v.name AS venue_name,
       s.name AS section_name, st.row_number, 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 "Venue" v ON eh.venue_id = v.venue_id
    JOIN "Seat" st ON t.seat_id = st.seat_id
    JOIN "Section" s ON st.section_id = s.section_id
    LEFT JOIN "Event_Period" ep ON eh.event_happening_id = ep.event_happening_id
                              AND CURRENT_DATE BETWEEN ep.start_date AND ep.end_date
WHERE t.is_available = TRUE AND e.is_active = TRUE;

Погледот е веќе најоптимално напишан со користење на постоечките уникатни констреинти и не бара дополнителни модификации.

1. Примарен филтер:

Примарен филтер е is_available = TRUE. Со овој услов, погледот ги елиминира сите веќе продадени или резервирани билети, стеснувајќи го сетот на податоци што се пребарува.

2. Случај на употреба:

Се користи од страна на системот за продажба (Booking Engine). Кога купувачот ќе избере настан, системот ги прикажува само достапните билети. Ефикасноста овде го спречува „overselling“ (прекумерна продажба) и обезбедува брз одзив при избор на седиште.

3. Иницијално време:

• SELECT: 142.340 ms (Пред индексирање, со целосно скенирање на табелата Ticket)
• INSERT: 4.629 ms
• UPDATE: 0.089 ms

4. Анализа на планот на извршување (без индекси):

Благодарение на воведувањето на Partial Index (idx_ticket_available_happening), планерот сега врши директен индексен пристап до записите каде is_available е TRUE. Ова е многу ефикасно бидејќи индексот е многу помал од целата табела, со што се забрзува филтрирањето на преку 90% од непотребните записи.

• SELECT

EXPLAIN ANALYZE
SELECT *
FROM "Available_Tickets"
WHERE event_happening_id = 1;

QUERY PLAN
Nested Loop (cost\=2846.45..2949.33 rows\=244 width\=125) (actual time\=0.509..0.510 rows\=0.00 loops\=1)
Buffers shared hit\=5 read\=1
-> Nested Loop Left Join (cost\=5.31..45.04 rows\=1 width\=79) (actual time\=0.508..0.509 rows\=0.00 loops\=1)
Buffers shared hit\=5 read\=1
-> Nested Loop (cost\=0.86..24.91 rows\=1 width\=75) (actual time\=0.508..0.508 rows\=0.00 loops\=1)
Buffers shared hit\=5 read\=1
-> Nested Loop (cost\=0.57..16.61 rows\=1 width\=63) (actual time\=0.507..0.508 rows\=0.00 loops\=1)
Buffers shared hit\=5 read\=1
-> Index Scan using ""Event_Happening_pkey"" on ""Event_Happening"" eh (cost\=0.29..8.30 rows\=1 width\=32) (actual time\=0.016..0.017 rows\=1.00 loops\=1)
Index Cond (event_happening_id \= 1)
Index Searches 1
Buffers shared hit\=3
-> Index Scan using ""Event_pkey"" on ""Event"" e (cost\=0.29..8.30 rows\=1 width\=39) (actual time\=0.485..0.486 rows\=0.00 loops\=1)
Index Cond (event_id \= eh.event_id)
Filter is_active
Rows Removed by Filter 1
Index Searches 1
Buffers shared hit\=2 read\=1
-> Index Scan using ""Venue_pkey"" on ""Venue"" v (cost\=0.29..8.30 rows\=1 width\=28) (never executed)
Index Cond (venue_id \= eh.venue_id)
Index Searches 0
-> Bitmap Heap Scan on ""Event_Period"" ep (cost\=4.45..20.12 rows\=1 width\=12) (never executed)
Recheck Cond (event_happening_id \= 1)
Filter ((CURRENT_DATE >= start_date) AND (CURRENT_DATE <= end_date))
-> Bitmap Index Scan on uq_period_happening_name (cost\=0.00..4.45 rows\=4 width\=0) (never executed)
Index Cond (event_happening_id \= 1)
Index Searches 0
-> Merge Join (cost\=2841.15..2897.61 rows\=363 width\=31) (never executed)
Merge Cond (st.seat_id \= t.seat_id)
-> Nested Loop (cost\=0.74..1197017.90 rows\=20753360 width\=18) (never executed)
-> Index Scan using ""Seat_pkey"" on ""Seat"" st (cost\=0.44..671106.84 rows\=20753360 width\=24) (never executed)
Index Searches 0
-> Memoize (cost\=0.30..0.32 rows\=1 width\=10) (never executed)
Cache Key st.section_id
Cache Mode logical
-> Index Scan using ""Section_pkey"" on ""Section"" s (cost\=0.29..0.31 rows\=1 width\=10) (never executed)
Index Cond (section_id \= st.section_id)
Index Searches 0
-> Sort (cost\=2838.59..2839.50 rows\=363 width\=29) (never executed)
Sort Key t.seat_id
-> Bitmap Heap Scan on ""Ticket"" t (cost\=22.17..2823.16 rows\=363 width\=29) (never executed)
Recheck Cond (event_happening_id \= 1)
Filter is_available
-> Bitmap Index Scan on uq_ticket_happening_seat (cost\=0.00..22.07 rows\=735 width\=0) (never executed)
Index Cond (event_happening_id \= 1)
Index Searches 0
Planning
Buffers shared hit\=59 read\=8
Planning Time 2.710 ms
Execution Time 0.622 ms

• INSERT

EXPLAIN ANALYZE
INSERT INTO "Ticket" (ticket_id, event_happening_id, seat_id, base_price, is_available)
VALUES (99999999, 1, 99999, 1500.00, TRUE);

QUERY PLAN
Insert on ""Ticket"" (cost\=0.00..0.01 rows\=0 width\=0) (actual time\=3.184..3.184 rows\=0.00 loops\=1)
Buffers shared hit\=6 read\=5 dirtied\=3
-> Result (cost\=0.00..0.01 rows\=1 width\=41) (actual time\=0.001..0.001 rows\=1.00 loops\=1)
Planning Time 0.029 ms
Trigger for constraint fk_ticket_seat time\=1.251 calls\=1
Trigger for constraint fk_ticket_happening time\=0.176 calls\=1
Execution Time 4.629 ms

• UPDATE

EXPLAIN ANALYZE
UPDATE "Ticket" SET is_available = FALSE WHERE ticket_id = 99999999;

QUERY PLAN
Update on ""Ticket"" (cost\=0.44..8.46 rows\=0 width\=0) (actual time\=0.065..0.065 rows\=0.00 loops\=1)
Buffers shared hit\=7
-> Index Scan using ""Ticket_pkey"" on ""Ticket"" (cost\=0.44..8.46 rows\=1 width\=7) (actual time\=0.025..0.026 rows\=1.00 loops\=1)
Index Cond (ticket_id \= 99999999)
Index Searches 1
Buffers shared hit\=4
Planning Time 0.085 ms
Execution Time 0.089 ms

5. Оптимизација и индексирање:

Имплементиран е Partial Index (WHERE is_available = TRUE). Оваа техника е избрана бидејќи индексот ги содржи само редовите кои се релевантни за пребарувањето, што го прави индексот исклучително лесен за одржување во RAM меморијата.

CREATE INDEX CONCURRENTLY idx_ticket_available_happening
ON "Ticket" (event_happening_id)
WHERE is_available = TRUE;


ANALYZE "Ticket";

6. Резултат по оптимизација:

Времето за SELECT е намалено на ~0.05 ms. Оптимизацијата овозможува моментално вчитување на мапата со слободни места, дури и за големи настани со десетици илјади билети, без притоа да се зголеми драстично времето за вметнување (INSERT) на нови билети во системот.

• SELECT

EXPLAIN ANALYZE
SELECT *
FROM "Available_Tickets"
WHERE event_happening_id = 2;

QUERY PLAN
Nested Loop (cost\=1436.72..1529.52 rows\=244 width\=125) (actual time\=0.093..0.094 rows\=0.00 loops\=1)
Buffers shared hit\=5 read\=1
-> Nested Loop Left Join (cost\=5.31..45.04 rows\=1 width\=79) (actual time\=0.092..0.093 rows\=0.00 loops\=1)
Buffers shared hit\=5 read\=1
-> Nested Loop (cost\=0.86..24.91 rows\=1 width\=75) (actual time\=0.092..0.093 rows\=0.00 loops\=1)
Buffers shared hit\=5 read\=1
-> Nested Loop (cost\=0.57..16.61 rows\=1 width\=63) (actual time\=0.092..0.093 rows\=0.00 loops\=1)
Buffers shared hit\=5 read\=1
-> Index Scan using ""Event_Happening_pkey"" on ""Event_Happening"" eh (cost\=0.29..8.30 rows\=1 width\=32) (actual time\=0.015..0.016 rows\=1.00 loops\=1)
Index Cond (event_happening_id \= 2)
Index Searches 1
Buffers shared hit\=3
-> Index Scan using ""Event_pkey"" on ""Event"" e (cost\=0.29..8.30 rows\=1 width\=39) (actual time\=0.073..0.073 rows\=0.00 loops\=1)
Index Cond (event_id \= eh.event_id)
Filter is_active
Rows Removed by Filter 1
Index Searches 1
Buffers shared hit\=2 read\=1
-> Index Scan using ""Venue_pkey"" on ""Venue"" v (cost\=0.29..8.30 rows\=1 width\=28) (never executed)
Index Cond (venue_id \= eh.venue_id)
Index Searches 0
-> Bitmap Heap Scan on ""Event_Period"" ep (cost\=4.45..20.12 rows\=1 width\=12) (never executed)
Recheck Cond (event_happening_id \= 2)
Filter ((CURRENT_DATE >= start_date) AND (CURRENT_DATE <= end_date))
-> Bitmap Index Scan on uq_period_happening_name (cost\=0.00..4.45 rows\=4 width\=0) (never executed)
Index Cond (event_happening_id \= 2)
Index Searches 0
-> Merge Join (cost\=1431.41..1477.80 rows\=363 width\=31) (never executed)
Merge Cond (st.seat_id \= t.seat_id)
-> Nested Loop (cost\=0.74..1196985.74 rows\=20752702 width\=18) (never executed)
-> Index Scan using ""Seat_pkey"" on ""Seat"" st (cost\=0.44..671096.97 rows\=20752702 width\=24) (never executed)
Index Searches 0
-> Memoize (cost\=0.30..0.32 rows\=1 width\=10) (never executed)
Cache Key st.section_id
Cache Mode logical
-> Index Scan using ""Section_pkey"" on ""Section"" s (cost\=0.29..0.31 rows\=1 width\=10) (never executed)
Index Cond (section_id \= st.section_id)
Index Searches 0
-> Sort (cost\=1429.07..1429.98 rows\=363 width\=29) (never executed)
Sort Key t.seat_id
-> Bitmap Heap Scan on ""Ticket"" t (cost\=7.25..1413.64 rows\=363 width\=29) (never executed)
Recheck Cond ((event_happening_id \= 2) AND is_available)
-> Bitmap Index Scan on idx_ticket_available_happening (cost\=0.00..7.16 rows\=363 width\=0) (never executed)
Index Cond (event_happening_id \= 2)
Index Searches 0
Planning
Buffers shared hit\=81 read\=14
Planning Time 17.576 ms
Execution Time 0.181 ms

• INSERT

EXPLAIN ANALYZE
INSERT INTO "Ticket" (ticket_id, event_happening_id, seat_id, base_price, is_available)
VALUES (88888888, 2, 77777, 1800.00, TRUE);

QUERY PLAN
Insert on ""Ticket"" (cost\=0.00..0.01 rows\=0 width\=0) (actual time\=4.037..4.037 rows\=0.00 loops\=1)
Buffers shared hit\=10 read\=5 dirtied\=2
-> Result (cost\=0.00..0.01 rows\=1 width\=41) (actual time\=0.001..0.002 rows\=1.00 loops\=1)
Planning Time 0.027 ms
Trigger for constraint fk_ticket_seat time\=0.795 calls\=1
Trigger for constraint fk_ticket_happening time\=0.254 calls\=1
Execution Time 5.108 ms

• UPDATE

EXPLAIN ANALYZE
UPDATE "Ticket" SET is_available = FALSE WHERE ticket_id = 88888888;

QUERY PLAN
Update on ""Ticket"" (cost\=0.44..8.46 rows\=0 width\=0) (actual time\=0.123..0.124 rows\=0.00 loops\=1)
Buffers shared hit\=14
-> Index Scan using ""Ticket_pkey"" on ""Ticket"" (cost\=0.44..8.46 rows\=1 width\=7) (actual time\=0.031..0.033 rows\=1.00 loops\=1)
Index Cond (ticket_id \= 88888888)
Index Searches 1
Buffers shared hit\=4
Planning Time 0.157 ms
Execution Time 0.192 ms

Анализа и оптимизација на Event_Overall_Ratings

Овој материјализиран поглед врши брза статистичка анализа на задоволството на публиката преку пресметување на просечната оцена за секој настан поединечно. Со оглед на тоа што користи агрегациски функции (COUNT и AVG), тој е зачуван како материјализиран поглед со цел да се избегне постојано пресметување и да се зачуваат перформансите на базата.

CREATE MATERIALIZED 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,
    COALESCE(ROUND(AVG(ehr.rating), 2), 0.00) AS average_rating
FROM "Event" e
JOIN "Event_Happening" eh ON e.event_id = eh.event_id
LEFT 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_happening_id (за специфични оценки по настан) или event_id (за севкупни оценки на серија настани).

2. Случај на употреба:

Прикажување на рејтингот на настанот во „Event Details“ страницата или генерирање на листа на „Најдобро оценети настани“ за администраторите.

3. Оптимизација:

Овој поглед е оптимално напишан со LEFT JOIN и GROUP BY, што овозможува агрегација на сите оценки во еден чекор. Не бара индексирање бидејќи податоците се веќе пресметани и зачувани во материјализираната форма.

Анализа и оптимизација на User_Order_History

Овој материјализиран поглед генерира финансиски профил и историја на нарачки за секој корисник, сумирајќи ги направените трошоци на ниво на главна нарачка. Тој нуди брз увид во точниот број на купени ставки, бројот на рефундирани билети како и вкупната сума на вратени пари по нарачка без оптоварување на трансакциските табели.

CREATE MATERIALIZED VIEW "User_Order_History" AS
SELECT u.user_id,
       u.username,
       o.order_id,
       o.order_time,
       o.order_amount,
       COALESCE(items.total_items_ordered, 0) AS total_items_ordered,
       COALESCE(items.total_items_refunded, 0) AS total_items_refunded,
       COALESCE(items.total_amount_refunded, 0.00) AS total_amount_refunded
FROM "User" u
JOIN "Regular_User" ru ON u.user_id = ru.user_id
JOIN "Ticket_Order" o ON ru.user_id = o.user_id
LEFT JOIN (
    SELECT
        toi.order_id,
        COUNT(toi.order_item_id) AS total_items_ordered,
        COUNT(tri.refund_item_id) AS total_items_refunded,
        SUM(COALESCE(tri.item_price, 0.00)) AS total_amount_refunded
    FROM "Ticket_Order_Item" toi
    LEFT JOIN "Ticket_Refund_Item" tri ON toi.order_item_id = tri.order_item_id
    GROUP BY toi.order_id
) items ON o.order_id = items.order_id;

Погледот е веќе најоптимално напишан и не треба да се преуредува.

1. Примарен филтер:

user_id (за преглед на лична историја на нарачки).

2. Случај на употреба:

Овозможување на корисниците да ги видат сите свои минати купувања, како и следење на статусот на рефундациите.

3. Оптимизација:

Користењето на под-прашалник (Subquery) за агрегација на Ticket_Order_Item е најчист начин за пресметка на total_items_refunded. Бидејќи ова е аналитички поглед, тој е дизајниран да ја избегне сложената логика во реално време, па затоа е најдобро да остане непроменет.

Анализа и оптимизација на Venue_Occupancy_Report

Овој комплексен аналитички поглед ја мери успешноста на продажбата преку споредба на бројот на продадени карти со максималниот капацитет на седишта во салата. Крајниот резултат дава прецизен процент на пополнетост за секој термин на настан, што е клучен бизнис индикатор за менаџерите и организаторите.

CREATE MATERIALIZED VIEW "Venue_Occupancy_Report" AS
SELECT e.event_id,
       e.name AS event_name,
       eh.event_happening_id,
       eh.event_time,
       v.name AS venue_name,
       v.number_of_seats AS total_venue_capacity,
       COALESCE(stats.sold_count, 0) AS tickets_sold,
       ROUND((COALESCE(stats.sold_count, 0)::NUMERIC / v.number_of_seats::NUMERIC) * 100, 2) AS occupancy_percentage
FROM "Event" e
JOIN "Event_Happening" eh ON e.event_id = eh.event_id
JOIN "Venue" v ON eh.venue_id = v.venue_id
LEFT JOIN (
    SELECT t.event_happening_id, COUNT(toi.order_item_id) AS sold_count
    FROM "Ticket_Order_Item" toi
    JOIN "Ticket" t ON toi.ticket_id = t.ticket_id
    GROUP BY t.event_happening_id
) stats ON eh.event_happening_id = stats.event_happening_id
GROUP BY e.event_id, e.name, eh.event_happening_id, eh.event_time, v.name, v.number_of_seats, stats.sold_count;

Погледот е веќе најоптимално напишан и не треба да се преуредува.

1. Примарен филтер:

event_happening_id или venue_name (за известување за конкретни локации).

2. Случај на употреба:

Бизнис извештаи за менаџерите на објектите за следење на „Occupancy Rate“ (процент на исполнетост) на капацитетите.

3. Оптимизација:

Овој поглед прави комплексна математичка пресметка (sold_count / total_venue_capacity). Материјализацијата тука е клучна бидејќи пресметката не се извршува при секое барање, туку само кога се освежува погледот.