Changes between Initial Version and Version 1 of Phase4_Profiling_Optimization

Timestamp:

05/08/26 03:09:07 (3 weeks ago)

Author:

226052

Comment:

--

Phase4_Profiling_Optimization

@@ +1 @@
+= Профилирање и оптимизација на извршувањето на прашалниците
+
+Профилирањето на прашалници претставува анализа на начинот на кој PostgreSQL го извршува одреден SQL прашалник. Целта е да се открие дали прашалникот користи неефикасни операции, како секвенцијално читање на големи табели, непотребно голем број join операции или сортирање на голем број редови. За оваа анализа во PostgreSQL се користи EXPLAIN ANALYZE, кој го прикажува планот на извршување и реалното време потребно за извршување на прашалникот.
+
+{{{
+EXPLAIN ANALYZE
+WITH customer_history AS (
+    SELECT DISTINCT
+        f.film_id,
+        f.rating,
+        fc.category_id
+    FROM rental r
+    JOIN inventory i ON r.inventory_id = i.inventory_id
+    JOIN film f ON i.film_id = f.film_id
+    JOIN film_category fc ON f.film_id = fc.film_id
+    WHERE r.customer_id = 101
+),
+preferred_categories AS (
+    SELECT
+        category_id,
+        COUNT(*) AS category_score
+    FROM customer_history
+    GROUP BY category_id
+),
+candidate_films AS (
+    SELECT
+        f.film_id,
+        f.title,
+        f.rating,
+        fc.category_id,
+        pc.category_score
+    FROM film f
+    JOIN film_category fc ON f.film_id = fc.film_id
+    JOIN preferred_categories pc ON fc.category_id = pc.category_id
+    WHERE NOT EXISTS (
+        SELECT 1
+        FROM customer_history ch
+        WHERE ch.film_id = f.film_id
+    )
+),
+film_popularity AS (
+    SELECT
+        i.film_id,
+        COUNT(r.rental_id) AS rental_count
+    FROM rental r
+    JOIN inventory i ON r.inventory_id = i.inventory_id
+    WHERE r.rental_date >= DATE '2024-01-01'
+      AND r.rental_date < DATE '2025-01-01'
+    GROUP BY i.film_id
+)
+SELECT
+    cf.film_id,
+    cf.title,
+    cf.rating,
+    c.name AS category,
+    cf.category_score,
+    COALESCE(fp.rental_count, 0) AS yearly_rentals,
+    (cf.category_score * 10 + COALESCE(fp.rental_count, 0)) AS recommendation_score
+FROM candidate_films cf
+JOIN category c ON cf.category_id = c.category_id
+LEFT JOIN film_popularity fp ON cf.film_id = fp.film_id
+WHERE cf.rating IN (
+    SELECT rating
+    FROM customer_history
+)
+ORDER BY recommendation_score DESC, cf.title
+LIMIT 10;
+}}}
+
+Овој прашалник претставува пример за препорака на филмови за клиент. Прво се анализира историјата на изнајмувања на клиентот, односно кои филмови, категории и ratings ги има гледано. Потоа се избираат кандидати од истите категории, се исклучуваат филмовите што клиентот веќе ги изнајмил, и се додава популарноста на филмовите според бројот на изнајмувања во 2024 година. На крај се пресметува recommendation_score и се враќаат најдобрите 10 препораки.
+
+{{{
+CTE customer_history
+-> Parallel Seq Scan on rental r_1
+   Filter: (customer_id = 101)
+   Rows Removed by Filter: 3333329
+film_popularity
+-> Parallel Seq Scan on rental r
+   Filter: ((rental_date >= '2024-01-01') AND (rental_date < '2025-01-01'))
+Rows Removed by Filter: 2666129
+Buffers: shared hit=207891
+Execution Time: 865.763 ms
+}}}
+
+При првото извршување, прашалникот се изврши без дополнителни индекси. Времето на извршување беше околу 866 ms. Во планот се забележува Parallel Seq Scan врз табелата rental, и тоа на две места: при пронаоѓање на историјата на клиентот и при пресметување на популарноста на филмовите во 2024 година. Ова значи дека PostgreSQL мора да прочита голем дел од табелата rental, која има над 10 милиони редови.
+За оптимизација се додаваат индекси врз колоните што најмногу се користат во WHERE условите и JOIN операциите. Индексот idx_rental_customer_inventory директно помага при пронаоѓање на изнајмувањата за конкретен клиент. Индексот idx_inventory_inventory_film помага при поврзување на rental со inventory и film. Индексите врз rental_date, film_category и rating се додадени како можни оптимизации, но PostgreSQL не мора секогаш да ги користи ако процени дека друг план е поефикасен.
+
+За оптимизација се додаваат индекси врз колоните што најмногу се користат во WHERE условите и JOIN операциите. Индексот idx_rental_customer_inventory директно помага при пронаоѓање на изнајмувањата за конкретен клиент. Индексот idx_inventory_inventory_film помага при поврзување на rental со inventory и film. Индексите врз rental_date, film_category и rating се додадени како можни оптимизации, но PostgreSQL не мора секогаш да ги користи ако процени дека друг план е поефикасен.
+
+{{{
+CREATE INDEX idx_rental_customer_inventory ON rental(customer_id, inventory_id);
+CREATE INDEX idx_rental_date_inventory ON rental(rental_date, inventory_id);
+CREATE INDEX idx_inventory_inventory_film ON inventory(inventory_id, film_id);
+CREATE INDEX idx_film_category_category_film ON film_category(category_id, film_id);
+CREATE INDEX idx_film_rating ON film(rating);
+}}}
+
+По додавање на индексите, истиот прашалник повторно се извршува со EXPLAIN ANALYZE. 
+{{{
+CTE customer_history
+-> Index Only Scan using idx_rental_customer_inventory on rental r_1
+   Index Cond: (customer_id = 101)
+   Heap Fetches: 1
+-> Index Only Scan using idx_inventory_inventory_film on inventory i_1
+   Index Cond: (inventory_id = r_1.inventory_id)
+film_popularity
+-> Parallel Seq Scan on rental r
+   Filter: ((rental_date >= '2024-01-01') AND (rental_date < '2025-01-01'))
+   Rows Removed by Filter: 2666129
+Buffers: shared hit=104500 read=15
+Execution Time: 566.908 ms
+}}}
+
+Времето на извршување се намали од околу 866 ms на околу 567 ms.
+
+Најголемата промена се забележува во делот customer_history. Пред оптимизацијата PostgreSQL користеше Parallel Seq Scan врз табелата rental и мораше да провери голем број редови за да ги најде изнајмувањата на клиентот 101. По додавање на индексот idx_rental_customer_inventory, планот користи Index Only Scan, со што овој дел од прашалникот се намали од околу 256 ms на околу 4.4 ms.
+Сепак, во делот film_popularity PostgreSQL сè уште користи Parallel Seq Scan врз rental за филтрирање на изнајмувањата во 2024 година. Причината е што условот за 2024 година враќа околу 2 милиони редови, па PostgreSQL проценува дека секвенцијално скенирање е поефикасно од користење индекс. Ова покажува дека додавање индекс не значи дека индексот секогаш ќе биде искористен.
+Иако додавањето индекси го подобри делот од прашалникот каде што се бара историјата на конкретен клиент, во планот на извршување сè уште се забележува Parallel Seq Scan врз табелата rental при пресметување на популарноста на филмовите во 2024 година. Ова се случува затоа што условот:
+{{{
+WHERE r.rental_date >= DATE '2024-01-01'
+  AND r.rental_date < DATE '2025-01-01'
+}}}
+
+работи врз табела со над 10 милиони редови.
+
+Бидејќи прашалникот филтрира според датум, табелата rental може да се оптимизира со партиционирање според колоната rental_date. Со range partitioning, податоците се делат во посебни партиции по години. На тој начин, кога прашалникот бара податоци само за 2024 година, PostgreSQL може да ја чита само партицијата за 2024 година, наместо целата табела.
+
+{{{
+ALTER TABLE rental RENAME TO rental_old;
+CREATE TABLE rental (
+    rental_id BIGINT GENERATED ALWAYS AS IDENTITY,
+    rental_date TIMESTAMP NOT NULL,
+    inventory_id BIGINT NOT NULL,
+    customer_id BIGINT NOT NULL,
+    return_date TIMESTAMP,
+    staff_id BIGINT NOT NULL,
+    last_update TIMESTAMP DEFAULT now() NOT NULL,
+    PRIMARY KEY (rental_id, rental_date)
+)
+PARTITION BY RANGE (rental_date);
+
+CREATE TABLE rental_2020 PARTITION OF rental
+FOR VALUES FROM ('2020-01-01') TO ('2021-01-01');
+
+CREATE TABLE rental_2021 PARTITION OF rental
+FOR VALUES FROM ('2021-01-01') TO ('2022-01-01');
+
+CREATE TABLE rental_2022 PARTITION OF rental
+FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');
+
+CREATE TABLE rental_2023 PARTITION OF rental
+FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
+
+CREATE TABLE rental_2024 PARTITION OF rental
+FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
+
+CREATE TABLE rental_2025 PARTITION OF rental
+FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
+
+CREATE TABLE rental_2026 PARTITION OF rental
+FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');
+Потоа податоците се префрлаат од старата табела rental_old во новата партиционирана табела rental.
+INSERT INTO rental (rental_id, rental_date, inventory_id, customer_id, return_date, staff_id, last_update)
+OVERRIDING SYSTEM VALUE
+SELECT rental_id, rental_date, inventory_id, customer_id, return_date, staff_id, last_update
+FROM rental_old;
+}}}
+
+По партиционирањето, истиот прашалник повторно се извршува.
+{{{
+CTE customer_history
+-> Append
+   -> Index Only Scan using rental_2020_customer_id_inventory_id_idx on rental_2020
+      Index Cond: (customer_id = 101)
+   -> Index Only Scan using rental_2021_customer_id_inventory_id_idx on rental_2021
+      Index Cond: (customer_id = 101)
+   -> Index Only Scan using rental_2022_customer_id_inventory_id_idx on rental_2022
+      Index Cond: (customer_id = 101)
+   -> Index Only Scan using rental_2023_customer_id_inventory_id_idx on rental_2023
+      Index Cond: (customer_id = 101)
+   -> Index Only Scan using rental_2024_customer_id_inventory_id_idx on rental_2024
+      Index Cond: (customer_id = 101)
+
+film_popularity
+-> Parallel Seq Scan on rental_2024 r
+   Filter: ((rental_date >= '2024-01-01') AND (rental_date < '2025-01-01'))
+
+Buffers: shared hit=22020 read=15
+Execution Time: 347.399 ms
+}}}
+Времето на извршување се намали на околу 347 ms. Во планот се забележува дека при пресметување на популарноста за 2024 година PostgreSQL чита само од партицијата rental_2024 (Parallel Seq Scan on rental_2024).
+Ова значи дека PostgreSQL применил partition pruning во делот од прашалникот каде што има услов за 2024 година и не ги чита партициите за другите години за тој дел од планот. Дополнително, делот customer_history користи Index Only Scan врз партициите, што овозможува брзо пронаоѓање на изнајмувањата за конкретниот клиент.
+Во табелата е прикажана споредба на времето на извршување на истиот прашалник во три различни состојби: без дополнителна оптимизација, по додавање индекси и по партиционирање на табелата rental според rental_date.
+
+|| Фаза || Време на извршување ||
+|| Без дополнителни индекси || 865.763 ms ||
+|| Со индекси || 566.908 ms ||
+|| Со партиционирање и индекси || 347.399 ms ||
+
+Од резултатите се гледа дека секоја оптимизација го намалува времето на извршување. Индексите најмногу помогнаа во делот каде што се бара историјата на конкретен клиент, додека партиционирањето помогна во делот каде што се анализираат изнајмувањата за 2024 година.
+Партиционирањето е најкорисно кога прашалниците често филтрираат според колоната што е partition key. Во овој пример, rental_date е погодна колона за partition key бидејќи прашалникот ја анализира популарноста на филмовите за конкретна година. Наместо да се обработува целата табела rental, PostgreSQL може да ја обработи само релевантната партиција.
+
+
+