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

Анализа и оптимизација на 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

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

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

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

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
WHERE u.is_active = TRUE;

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

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

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

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

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

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

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

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

Овој аналитички поглед го мери „ефектот на продажба“ преку пресметка на процентот на пополнетост (occupancy_percentage) на секој термин. Тој е примарна алатка за менаџерите кои треба да го следат успехот на настанот во реално време. Имплементиран е како материјализиран поглед за да поддржи брзо аналитичко известување без дополнително оптоварување на базата со тешки JOIN операции врз табелите за нарачки.

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
WHERE e.is_active = TRUE
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). Материјализацијата тука е клучна бидејќи пресметката не се извршува при секое барање, туку само кога се освежува погледот.