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

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

Без индекс:

• SELECT

EXPLAIN ANALYZE
    SELECT * FROM "Performer_Events" WHERE performer_id = 10;

• INSERT

EXPLAIN ANALYZE
    INSERT INTO "Event_Happening_Performer" (event_happening_id, performer_id)
    VALUES (1, 10);

• UPDATE

EXPLAIN ANALYZE
    UPDATE "Event_Happening_Performer"
    SET performer_id = 11
    WHERE event_happening_id = 1 AND performer_id = 10;

Погледот е бавен поради комплексни врски помеѓи четири табели што резултира со време од 3.2s. Индексите на performer_id и 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);

Со индекс:

• SELECT

EXPLAIN ANALYZE
    SELECT * FROM "Performer_Events" WHERE performer_id = 10;

• INSERT

EXPLAIN ANALYZE
    INSERT INTO "Event_Happening_Performer" (event_happening_id, performer_id)
    VALUES (2, 10);

• UPDATE

EXPLAIN ANALYZE
    UPDATE "Event_Happening_Performer"
    SET performer_id = 12
    WHERE event_happening_id = 2 AND performer_id = 10;

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

Без индекс:

• SELECT

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

• INSERT

EXPLAIN ANALYZE
    INSERT INTO "Seat" (seat_id, section_id, seat_number)
    SELECT COALESCE(MAX(seat_id), 0) + 1, 1, 999999 FROM "Seat";

• UPDATE

EXPLAIN ANALYZE
    UPDATE "Seat"
    SET seat_number = 888888
    WHERE seat_number = 999999;

Времето за ажурирање од 312s е неприфатливо за интеракција со мапа на седишта во реално време. Со поставување индекси на seat_number и venue_id, пребарувањето и промената на статусот на седиштата се извршуваат за милисекунди наместо за неколку минути.

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

-- 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);

Со индекс:

• SELECT

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

• INSERT

EXPLAIN ANALYZE
    INSERT INTO "Seat" (seat_id, section_id, seat_number)
    SELECT COALESCE(MAX(seat_id), 0) + 1, 1, 111222 FROM "Seat";

• UPDATE

EXPLAIN ANALYZE
    UPDATE "Seat"
    SET seat_number = 333444
    WHERE seat_number = 111222;

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

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

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;

Без индекс:

• SELECT

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

• 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";

• UPDATE

EXPLAIN ANALYZE
    UPDATE "Ticket_Purchase"
    SET purchase_amount = 1500.00
    WHERE user_id = 1 AND qr_code = 'QR-TEST-CODE-001';

Приказот на историјата на билети трае предолги 251.9s, што го блокира корисничкиот профил. Индексот на user_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);

Со индекс:

• SELECT

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

• 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";

• UPDATE

EXPLAIN ANALYZE
    UPDATE "Ticket_Purchase"
    SET purchase_amount = 1350.00
    WHERE user_id = 1 AND qr_code = 'QR-TEST-CODE-002';

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

Без индекс:

• SELECT

EXPLAIN ANALYZE
    SELECT * FROM "Event_User_Ratings"
    WHERE user_id = 1;

• 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";

• UPDATE

EXPLAIN ANALYZE
    UPDATE "Event_Happening_Rating"
    SET rating = 4, comment = 'New test rating'
    WHERE event_happening_id = 1 AND user_id = 1;

Без индекси, секое пребарување на оценките по корисник предизвикува непотребно оптоварување на меморијата преку Seq Scan. Индексирањето на user_id и event_happening_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);

Со индекс:

• SELECT

EXPLAIN ANALYZE
    SELECT * FROM "Event_User_Ratings" WHERE user_id = 1;

• 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";

• UPDATE

EXPLAIN ANALYZE
    UPDATE "Event_Happening_Rating"
    SET rating = 6, comment = 'New test rating'
    WHERE event_happening_id = 1 AND user_id = 2;

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

Без индекс:

• SELECT

EXPLAIN ANALYZE
    SELECT * FROM "Event_Overall_Ratings" WHERE event_id = 1;

• 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";

• UPDATE

EXPLAIN ANALYZE
    UPDATE "Event_Happening_Rating"
    SET rating = 8, comment = 'New test rating'
    WHERE event_happening_id = 1 AND user_id = 15;

Пресметката на просечни оценки бара постојано агрегирање на податоци, што е бавно при секој нов приказ. Композитен индекс на (event_happening_id, rating) овозможува математичките операции да се вршат директно врз индексот, забрзувајќи го приказот на почетната страна.

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

-- 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);

Со индекс:

• SELECT

EXPLAIN ANALYZE
    SELECT * FROM "Event_Overall_Ratings" WHERE event_id = 1;

• 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";

• UPDATE

EXPLAIN ANALYZE
    UPDATE "Event_Happening_Rating"
    SET rating = 8, comment = 'New test rating'
    WHERE event_happening_id = 1 AND user_id = 15;

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

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

-- 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);

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

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

-- 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);

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

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

-- 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);