Профилирање и оптимизација на извршувањето на прашалниците

Профилирањето на прашалници претставува анализа на начинот на кој 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 може да ја обработи само релевантната партиција.