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

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

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

CREATE VIEW "Performer_Events" AS
SELECT p.performer_id,
       p.name AS performer_name,
       e.event_id,
       e.name AS event_name,
       eh.event_time
FROM "Performer" p
JOIN "Event_Happening_Performer" ehp ON p.performer_id = ehp.performer_id
JOIN "Event_Happening" eh ON ehp.event_happening_id = eh.event_happening_id
JOIN "Event" e ON eh.event_id = e.event_id;

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

Примарен филтер за овој поглед ќе биде според performer_id (ID на изведувач), бидејќи најчестото пребарување е за да се видат настапите на конкретен изведувач. Дополнително, ќе може да се пребарува и по event_id.

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

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

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

Иницијалното време за извршување на погледот изнесува 3.249 s (3249 ms). Ова е исклучително бавно и неприфатливо за една веб-апликација, каде што секој одзив над 500ms се смета за критичен.

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

Иако базата се обидува да користи постоечки Primary Key индекси, главниот проблем се јавува во спојувањето на четирите табели Performer, Event_Happening_Performer, Event_Happening, Event.

Најбавниот дел е кај Nested Loop операциите и проверката на Foreign Keys при INSERT (1.424 s).

Времето на планирање е исто така високо (1.8 s), што укажува на комплексност во резолвирањето на релациите без соодветни патеки.

• SELECT

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

QUERY PLAN
Nested Loop (cost\=1.14..73.38 rows\=7 width\=68) (actual time\=1505.862..3249.242 rows\=4 loops\=1)
-> Nested Loop (cost\=0.86..70.92 rows\=7 width\=37) (actual time\=1079.648..2259.465 rows\=4 loops\=1)
-> Index Scan using "Performer_pkey" on "Performer" p (cost\=0.28..8.30 rows\=1 width\=21) (actual time\=701.003..701.005 rows\=1 loops\=1)
Index Cond: (performer_id \= 10)
-> Nested Loop (cost\=0.57..62.55 rows\=7 width\=24) (actual time\=378.637..1558.442 rows\=4 loops\=1)
-> Index Only Scan using uq_performer_at_time on "Event_Happening_Performer" ehp (cost\=0.29..4.41 rows\=7 width\=16) (actual time\=90.817..90.838 rows\=4 loops\=1)
Index Cond: (performer_id \= 10)
Heap Fetches: 0
-> Index Scan using "Event_Happening_pkey" on "Event_Happening" eh (cost\=0.29..8.30 rows\=1 width\=24) (actual time\=366.888..366.889 rows\=1 loops\=4)
Index Cond: (event_happening_id \= ehp.event_happening_id)
-> Index Scan using "Event_pkey" on "Event" e (cost\=0.29..0.35 rows\=1 width\=39) (actual time\=247.432..247.432 rows\=1 loops\=4)
Index Cond: (event_id \= eh.event_id)
Planning Time: 1816.633 ms
Execution Time: 3249.314 ms

• INSERT

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

QUERY PLAN
Insert on "Event_Happening_Performer" (cost\=0.00..0.01 rows\=0 width\=0) (actual time\=558.243..558.244 rows\=0 loops\=1)
-> Result (cost\=0.00..0.01 rows\=1 width\=16) (actual time\=0.001..0.002 rows\=1 loops\=1)
Planning Time: 0.042 ms
Trigger for constraint fk_ehp_event_happening: time\=826.105 calls\=1
Trigger for constraint fk_ehp_performer: time\=40.075 calls\=1
Execution Time: 1424.451 ms

• UPDATE

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

QUERY PLAN
Update on "Event_Happening_Performer" (cost\=0.29..8.31 rows\=0 width\=0) (actual time\=0.219..0.219 rows\=0 loops\=1)
-> Index Scan using uq_performer_at_time on "Event_Happening_Performer" (cost\=0.29..8.31 rows\=1 width\=14) (actual time\=0.120..0.121 rows\=1 loops\=1)
Index Cond: ((performer_id \= 10) AND (event_happening_id \= 1))
Planning Time: 0.139 ms
Trigger for constraint fk_ehp_performer: time\=0.279 calls\=1
Execution Time: 23.621 ms

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

За да го решиме проблемот, воведуваме индекси на колоните кои служат за поврзување во JOIN условите и во WHERE филтрите:

• idx_ehp_performer_id и idx_ehp_happening_id на табелата што ги поврзува изведувачите со термините.

• idx_event_happening_event_id за побрзо поврзување на конкретниот термин со името на настанот.

-- indexes for linking performers with specific event occurrences (M:N relationship)
CREATE INDEX idx_ehp_performer_id ON "Event_Happening_Performer"(performer_id);
CREATE INDEX idx_ehp_happening_id ON "Event_Happening_Performer"(event_happening_id);

-- index for optimizing event lookups within scheduled event happenings
CREATE INDEX idx_event_happening_event_id ON "Event_Happening"(event_id);

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

По додавањето на индексите, времето на извршување на SELECT се намали на 0.533 ms.

Подобрување: Ова е забрзување од над 6000 пати.

Операциите за INSERT и UPDATE сега се извршуваат за помалку од 1ms, што значи дека индексите не го забавуваат системот, туку помагаат дури и кај тригерите за Foreign Keys.

• SELECT

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

QUERY PLAN
Nested Loop (cost\=1.14..77.38 rows\=7 width\=68) (actual time\=0.251..0.479 rows\=4 loops\=1)
-> Nested Loop (cost\=0.86..74.92 rows\=7 width\=37) (actual time\=0.201..0.306 rows\=4 loops\=1)
-> Index Scan using "Performer_pkey" on "Performer" p (cost\=0.28..8.30 rows\=1 width\=21) (actual time\=0.093..0.094 rows\=1 loops\=1)
Index Cond: (performer_id \= 10)
-> Nested Loop (cost\=0.57..66.55 rows\=7 width\=24) (actual time\=0.105..0.207 rows\=4 loops\=1)
-> Index Only Scan using uq_performer_at_time on "Event_Happening_Performer" ehp (cost\=0.29..8.41 rows\=7 width\=16) (actual time\=0.062..0.065 rows\=4 loops\=1)
Index Cond: (performer_id \= 10)
Heap Fetches: 1
-> Index Scan using "Event_Happening_pkey" on "Event_Happening" eh (cost\=0.29..8.30 rows\=1 width\=24) (actual time\=0.032..0.032 rows\=1 loops\=4)
Index Cond: (event_happening_id \= ehp.event_happening_id)
-> Index Scan using "Event_pkey" on "Event" e (cost\=0.29..0.35 rows\=1 width\=39) (actual time\=0.041..0.041 rows\=1 loops\=4)
Index Cond: (event_id \= eh.event_id)
Planning Time: 1.600 ms
Execution Time: 0.533 ms

• INSERT

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

QUERY PLAN
Insert on "Event_Happening_Performer" (cost\=0.00..0.01 rows\=0 width\=0) (actual time\=0.307..0.308 rows\=0 loops\=1)
-> Result (cost\=0.00..0.01 rows\=1 width\=16) (actual time\=0.001..0.001 rows\=1 loops\=1)
Planning Time: 0.046 ms
Trigger for constraint fk_ehp_event_happening: time\=0.332 calls\=1
Trigger for constraint fk_ehp_performer: time\=0.169 calls\=1
Execution Time: 0.833 ms

• UPDATE

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

QUERY PLAN
Update on "Event_Happening_Performer" (cost\=0.29..8.31 rows\=0 width\=0) (actual time\=0.437..0.438 rows\=0 loops\=1)
-> Index Scan using idx_ehp_happening_id on "Event_Happening_Performer" (cost\=0.29..8.31 rows\=1 width\=14) (actual time\=0.174..0.175 rows\=1 loops\=1)
Index Cond: (event_happening_id \= 2)
Filter: (performer_id \= 10)
Rows Removed by Filter: 1
Planning Time: 0.161 ms
Trigger for constraint fk_ehp_performer: time\=0.271 calls\=1
Execution Time: 0.763 ms