| Version 7 (modified by , 7 hours ago) ( diff ) |
|---|
Други Развојни Активности
Анализа на перформанси
Овој дел ја анализира изведбата на четирите извештајни dashboard функции врз базата и испитува дали и колку индекси можат да ја подобрат.
Кратко за резултатот: индексите донесоа помеѓу −5% и +4%, што за ниту една од четирите функции не е значајно подобрување. Причината не е во индексите, туку во тоа што овие функции агрегираат врз целата база без ниту еден WHERE услов, па индексот нема редови што би можел да прескокне. Сите тврдења подолу се поткрепени со мерења.
Методологија
EXPLAIN ANALYZE SELECT * FROM dashboard_monthly_totals() враќа само еден ред, Function Scan on dashboard_monthly_totals, со вкупно време и ништо повеќе. Планот на телото на функцијата останува скриен, бидејќи PL/pgSQL го планира внатрешниот RETURN QUERY посебно и не го изложува нанадвор. Затоа во оваа анализа телото на секоја функција е извадено како самостоен SELECT и EXPLAIN ANALYZE е пуштен врз него. Така се добива целото стебло на планот, со join-овите, sort-овите, scan-овите и времето по јазол, што е единствениот начин да се види каде навистина се троши времето.
Секоја варијанта е мерена на истиот начин. Query-то прво се пушта еднаш без мерење, за да се загрее cache-от и да не се мери случајно диск I/O од првото читање. Потоа EXPLAIN ANALYZE се пушта три пати, се известува просекот од трите заедно со сите три поединечни времиња, а прикажан е планот од последното пуштање. Пред мерењата е пуштено ANALYZE врз целата база, за планерот да работи со свежа статистика.
Помеѓу секоја функција базата се враќа на почетна состојба, односно се бришат сите не-unique индекси. Така секоја секција почнува од истата основа и бројките се споредливи.
Големина на податоците:
| Табела | Редови | Големина (heap) |
| user_course_progress | 960.000 | 60 MB |
| user_tag | 900.000 | — |
| user_favorite_course | 900.000 | — |
| course_lecture_translate | 864.000 | — |
| payment | 800.000 | 52 MB |
| enrollment | 800.000 | 51 MB |
| account | 500.000 | 56 MB |
| user | 450.000 | — |
| review | 160.000 | 17 MB |
| course_version | 6.000 | — |
| course_translate | 6.000 | — |
| expert_course | 4.666 | — |
| course | 2.000 | — |
| language | 3 | — |
Релевантни поставки на серверот:
| Параметар | Вредност |
work_mem | 4 MB |
shared_buffers | 160 MB |
effective_cache_size | 5 GB |
random_page_cost | 4 |
max_parallel_workers_per_gather | 2 |
jit | on |
Постоечки индекси во базата
Базата веќе има 40 индекси, но сите до еден се создадени автоматски од PRIMARY KEY и UNIQUE ограничувањата. Нема ниту еден рачно додаден индекс наменет за изведба.
Ова е важно за читањето на резултатите подолу. Сите join-ови по primary key (enrollment_id, course_id, course_version_id, payment_id) веќе се покриени со индекс. Врските 1:1 како payment → enrollment преку uq_payment_enrollment и review → enrollment преку uq_review_enrollment исто така се покриени. Композитните UNIQUE индекси служат и како индекси за својот прв столб, па uq_course_translate (course_id, language_id) го покрива и барањето само по course_id. Реално единствениот непокриен foreign key е enrollment.course_version_id.
Со други зборови, најголемиот дел од очигледните индекси веќе ги има, што директно објаснува зошто додавањето нови носи мал ефект.
| account | account_pkey |
| account | uq_account_email |
| course | course_pkey |
| course_content | course_content_pkey |
| course_content | uq_course_content_position |
| course_content_translate | course_content_translate_pkey |
| course_content_translate | uq_course_content_translate |
| course_lecture | course_lecture_pkey |
| course_lecture | uq_course_lecture_position |
| course_lecture_translate | course_lecture_translate_pkey |
| course_lecture_translate | uq_course_lecture_translate |
| course_tag | pk_course_tag |
| course_translate | course_translate_pkey |
| course_translate | uq_course_translate |
| course_version | course_version_pkey |
| course_version | uq_course_version_number |
| course_version | uq_course_version_one_active |
| enrollment | enrollment_pkey |
| enrollment | uq_enrollment_user_version |
| expert | expert_pkey |
| expert | uq_expert_account_id |
| expert_course | pk_expert_course |
| language | language_pkey |
| language | uq_language_value |
| meeting_email_reminder | meeting_email_reminder_pkey |
| meeting_email_reminder | uq_mer_link |
| payment | payment_pkey |
| payment | uq_payment_enrollment |
| review | review_pkey |
| review | uq_review_enrollment |
| tag | tag_pkey |
| tag_translate | tag_translate_pkey |
| tag_translate | uq_tag_translate |
| user | uq_user_account_id |
| user | user_pkey |
| user_course_progress | uq_ucp |
| user_course_progress | user_course_progress_pkey |
| user_favorite_course | pk_user_favorite_course |
| user_tag | pk_user_tag |
| verification_token | verification_token_pkey |
dashboard_monthly_totals()
Вкупно запишувања и приход по месец. Резултатот има 30 реда, по еден за секој месец во податоците.
Дефиниција
-- Monthly total enrollments and revenue
CREATE OR REPLACE FUNCTION dashboard_monthly_totals()
RETURNS TABLE (
year INTEGER,
month INTEGER,
total_enrollments BIGINT,
total_revenue NUMERIC
) AS $$
#variable_conflict use_column
BEGIN
RETURN QUERY
SELECT
EXTRACT(YEAR FROM e.purchase_date)::INTEGER AS year,
EXTRACT(MONTH FROM e.purchase_date)::INTEGER AS month,
COUNT(e.enrollment_id)::BIGINT AS total_enrollments,
COALESCE(SUM(p.amount), 0)::NUMERIC AS total_revenue
FROM enrollment e
LEFT JOIN payment p
ON p.enrollment_id = e.enrollment_id
AND p.payment_status = 'completed' -- само наплатените пари се промет
GROUP BY
EXTRACT(YEAR FROM e.purchase_date),
EXTRACT(MONTH FROM e.purchase_date)
ORDER BY year DESC, month DESC;
END;
$$ LANGUAGE plpgsql;
Почетна состојба
Sort (cost=37127.27..37129.52 rows=900 width=112) (actual time=150.157..153.299 rows=30 loops=1)
Sort Key: (((EXTRACT(year FROM e.purchase_date)))::integer) DESC, (((EXTRACT(month FROM e.purchase_date)))::integer) DESC
Sort Method: quicksort Memory: 26kB
-> Finalize GroupAggregate (cost=36825.84..37083.11 rows=900 width=112) (actual time=150.115..153.287 rows=30 loops=1)
Group Key: (EXTRACT(year FROM e.purchase_date)), (EXTRACT(month FROM e.purchase_date))
-> Gather Merge (cost=36825.84..37035.86 rows=1800 width=108) (actual time=150.109..153.264 rows=90 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=35825.82..35828.07 rows=900 width=108) (actual time=148.317..148.320 rows=30 loops=3)
Sort Key: (EXTRACT(year FROM e.purchase_date)), (EXTRACT(month FROM e.purchase_date))
Sort Method: quicksort Memory: 28kB
Worker 0: Sort Method: quicksort Memory: 28kB
Worker 1: Sort Method: quicksort Memory: 28kB
-> Partial HashAggregate (cost=35765.91..35781.66 rows=900 width=108) (actual time=148.294..148.302 rows=30 loops=3)
Group Key: EXTRACT(year FROM e.purchase_date), EXTRACT(month FROM e.purchase_date)
Batches: 1 Memory Usage: 57kB
Worker 0: Batches: 1 Memory Usage: 57kB
Worker 1: Batches: 1 Memory Usage: 57kB
-> Parallel Hash Left Join (cost=15468.58..32432.58 rows=333333 width=81) (actual time=64.563..117.679 rows=266667 loops=3)
Hash Cond: (e.enrollment_id = p.enrollment_id)
-> Parallel Seq Scan on enrollment e (cost=0.00..9864.33 rows=333333 width=12) (actual time=0.004..14.058 rows=266667 loops=3)
-> Parallel Hash (cost=10833.67..10833.67 rows=266633 width=13) (actual time=30.498..30.498 rows=213333 loops=3)
Buckets: 262144 Batches: 8 Memory Usage: 5856kB
-> Parallel Seq Scan on payment p (cost=0.00..10833.67 rows=266633 width=13) (actual time=0.016..14.775 rows=213333 loops=3)
Filter: (payment_status = 'completed'::payment_status)
Rows Removed by Filter: 53333
Planning Time: 0.268 ms
Execution Time: 153.339 ms
Време: 153,8 ms (три пуштања: 157,7 / 150,3 / 153,3 ms).
Од планот се гледа дека Parallel Seq Scan on enrollment чита сите 800.000 редови, а Parallel Seq Scan on payment чита целата табела и потоа отфрла околу 160.000 редови преку Filter: payment_status = 'completed'. Двете страни одат во Parallel Hash Left Join. Оттука произлегуваат две идеи: парцијален covering индекс врз payment, за да не се читаат воопшто редовите што не се completed и amount да се земе директно од индексот, и covering индекс врз enrollment, за да се овозможи Index Only Scan без пристап до heap-от.
Чекор 1, парцијален covering индекс врз payment
CREATE INDEX idx_payment_completed_covering\n ON payment(enrollment_id) INCLUDE (amount)\n WHERE payment_status = 'completed';
Sort (cost=37116.74..37118.99 rows=900 width=112) (actual time=145.784..149.022 rows=30 loops=1)
Sort Key: (((EXTRACT(year FROM e.purchase_date)))::integer) DESC, (((EXTRACT(month FROM e.purchase_date)))::integer) DESC
Sort Method: quicksort Memory: 26kB
-> Finalize GroupAggregate (cost=36815.32..37072.58 rows=900 width=112) (actual time=145.743..149.011 rows=30 loops=1)
Group Key: (EXTRACT(year FROM e.purchase_date)), (EXTRACT(month FROM e.purchase_date))
-> Gather Merge (cost=36815.32..37025.33 rows=1800 width=108) (actual time=145.738..148.989 rows=90 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=35815.29..35817.54 rows=900 width=108) (actual time=144.522..144.524 rows=30 loops=3)
Sort Key: (EXTRACT(year FROM e.purchase_date)), (EXTRACT(month FROM e.purchase_date))
Sort Method: quicksort Memory: 28kB
Worker 0: Sort Method: quicksort Memory: 28kB
Worker 1: Sort Method: quicksort Memory: 28kB
-> Partial HashAggregate (cost=35755.38..35771.13 rows=900 width=108) (actual time=144.496..144.505 rows=30 loops=3)
Group Key: EXTRACT(year FROM e.purchase_date), EXTRACT(month FROM e.purchase_date)
Batches: 1 Memory Usage: 57kB
Worker 0: Batches: 1 Memory Usage: 57kB
Worker 1: Batches: 1 Memory Usage: 57kB
-> Parallel Hash Left Join (cost=15460.05..32422.05 rows=333333 width=81) (actual time=60.990..113.876 rows=266667 loops=3)
Hash Cond: (e.enrollment_id = p.enrollment_id)
-> Parallel Seq Scan on enrollment e (cost=0.00..9864.33 rows=333333 width=12) (actual time=0.018..13.806 rows=266667 loops=3)
-> Parallel Hash (cost=10833.67..10833.67 rows=266111 width=13) (actual time=28.989..28.989 rows=213333 loops=3)
Buckets: 262144 Batches: 8 Memory Usage: 5856kB
-> Parallel Seq Scan on payment p (cost=0.00..10833.67 rows=266111 width=13) (actual time=0.020..14.149 rows=213333 loops=3)
Filter: (payment_status = 'completed'::payment_status)
Rows Removed by Filter: 53333
Planning Time: 0.327 ms
Execution Time: 149.062 ms
Време: 153,0 ms (три пуштања: 156,5 / 153,5 / 149,1 ms), односно без ефект (+0,5%, во границите на мерната грешка).
Планот е непроменет. Планерот го игнорира индексот и останува на Parallel Seq Scan on payment.
Чекор 2, плус covering индекс врз enrollment
CREATE INDEX idx_enrollment_purchase_date_cov\n ON enrollment(purchase_date) INCLUDE (enrollment_id);
Sort (cost=37116.74..37118.99 rows=900 width=112) (actual time=162.435..165.607 rows=30 loops=1)
Sort Key: (((EXTRACT(year FROM e.purchase_date)))::integer) DESC, (((EXTRACT(month FROM e.purchase_date)))::integer) DESC
Sort Method: quicksort Memory: 26kB
-> Finalize GroupAggregate (cost=36815.32..37072.58 rows=900 width=112) (actual time=162.379..165.593 rows=30 loops=1)
Group Key: (EXTRACT(year FROM e.purchase_date)), (EXTRACT(month FROM e.purchase_date))
-> Gather Merge (cost=36815.32..37025.33 rows=1800 width=108) (actual time=162.369..165.558 rows=90 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=35815.29..35817.54 rows=900 width=108) (actual time=161.332..161.335 rows=30 loops=3)
Sort Key: (EXTRACT(year FROM e.purchase_date)), (EXTRACT(month FROM e.purchase_date))
Sort Method: quicksort Memory: 28kB
Worker 0: Sort Method: quicksort Memory: 28kB
Worker 1: Sort Method: quicksort Memory: 28kB
-> Partial HashAggregate (cost=35755.38..35771.13 rows=900 width=108) (actual time=161.296..161.307 rows=30 loops=3)
Group Key: EXTRACT(year FROM e.purchase_date), EXTRACT(month FROM e.purchase_date)
Batches: 1 Memory Usage: 57kB
Worker 0: Batches: 1 Memory Usage: 57kB
Worker 1: Batches: 1 Memory Usage: 57kB
-> Parallel Hash Left Join (cost=15460.05..32422.05 rows=333333 width=81) (actual time=69.057..128.267 rows=266667 loops=3)
Hash Cond: (e.enrollment_id = p.enrollment_id)
-> Parallel Seq Scan on enrollment e (cost=0.00..9864.33 rows=333333 width=12) (actual time=0.021..14.344 rows=266667 loops=3)
-> Parallel Hash (cost=10833.67..10833.67 rows=266111 width=13) (actual time=31.942..31.943 rows=213333 loops=3)
Buckets: 262144 Batches: 8 Memory Usage: 5856kB
-> Parallel Seq Scan on payment p (cost=0.00..10833.67 rows=266111 width=13) (actual time=0.023..15.409 rows=213333 loops=3)
Filter: (payment_status = 'completed'::payment_status)
Rows Removed by Filter: 53333
Planning Time: 0.315 ms
Execution Time: 165.649 ms
Време: 161,7 ms (три пуштања: 162,2 / 157,2 / 165,6 ms), односно забавување од 5,1%.
И вториот индекс е игнориран, и двата scan-а остануваат Seq Scan.
Проверка со присилно користење на индексите
За да се потврди дека планерот е во право, индексите се наметнати со SET enable_seqscan = off.
Sort (cost=51799.37..51801.62 rows=900 width=112) (actual time=274.477..277.882 rows=30 loops=1)
Sort Key: (((EXTRACT(year FROM e.purchase_date)))::integer) DESC, (((EXTRACT(month FROM e.purchase_date)))::integer) DESC
Sort Method: quicksort Memory: 26kB
-> Finalize GroupAggregate (cost=51497.95..51755.21 rows=900 width=112) (actual time=274.376..277.815 rows=30 loops=1)
Group Key: (EXTRACT(year FROM e.purchase_date)), (EXTRACT(month FROM e.purchase_date))
-> Gather Merge (cost=51497.95..51707.96 rows=1800 width=108) (actual time=274.363..277.783 rows=90 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=50497.92..50500.17 rows=900 width=108) (actual time=271.312..271.315 rows=30 loops=3)
Sort Key: (EXTRACT(year FROM e.purchase_date)), (EXTRACT(month FROM e.purchase_date))
Sort Method: quicksort Memory: 28kB
Worker 0: Sort Method: quicksort Memory: 28kB
Worker 1: Sort Method: quicksort Memory: 28kB
-> Partial HashAggregate (cost=50438.01..50453.76 rows=900 width=108) (actual time=271.276..271.285 rows=30 loops=3)
Group Key: EXTRACT(year FROM e.purchase_date), EXTRACT(month FROM e.purchase_date)
Batches: 1 Memory Usage: 57kB
Worker 0: Batches: 1 Memory Usage: 57kB
Worker 1: Batches: 1 Memory Usage: 57kB
-> Parallel Hash Left Join (cost=20337.69..47104.68 rows=333333 width=81) (actual time=181.814..237.345 rows=266667 loops=3)
Hash Cond: (e.enrollment_id = p.enrollment_id)
-> Parallel Index Only Scan using idx_enrollment_purchase_date_cov on enrollment e (cost=0.42..19669.76 rows=333333 width=12) (actual time=0.085..43.629 rows=266667 loops=3)
Heap Fetches: 0
-> Parallel Hash (cost=15710.87..15710.87 rows=266111 width=13) (actual time=101.237..101.239 rows=213333 loops=3)
Buckets: 262144 Batches: 8 Memory Usage: 5856kB
-> Parallel Index Only Scan using idx_payment_completed_covering on payment p (cost=0.42..15710.87 rows=266111 width=13) (actual time=0.101..74.900 rows=213333 loops=3)
Heap Fetches: 0
Planning Time: 1.117 ms
Execution Time: 278.000 ms
Време: 278,0 ms наспроти 153,8 ms, односно 1,8 пати побавно. Планерот донесе точната одлука.
Проверка со WHERE услов
Тврдењето дека индекс нема што да прескокне вреди да се провери директно. Истото query, ограничено на последните околу три месеци, со обичен индекс врз purchase_date:
CREATE INDEX idx_enrollment_purchase_date ON enrollment(purchase_date);
WHERE e.purchase_date >= DATE '2025-04-01'
Тоа зафаќа 70.152 од 800.000 реда, односно околу 8,8%.
Sort (cost=21376.64..21378.89 rows=900 width=112) (actual time=36.787..36.950 rows=3 loops=1)
Sort Key: (((EXTRACT(year FROM e.purchase_date)))::integer) DESC, (((EXTRACT(month FROM e.purchase_date)))::integer) DESC
Sort Method: quicksort Memory: 25kB
-> Finalize GroupAggregate (cost=21075.21..21332.48 rows=900 width=112) (actual time=36.779..36.943 rows=3 loops=1)
Group Key: (EXTRACT(year FROM e.purchase_date)), (EXTRACT(month FROM e.purchase_date))
-> Gather Merge (cost=21075.21..21285.23 rows=1800 width=108) (actual time=36.774..36.938 rows=9 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=20075.19..20077.44 rows=900 width=108) (actual time=33.689..33.691 rows=3 loops=3)
Sort Key: (EXTRACT(year FROM e.purchase_date)), (EXTRACT(month FROM e.purchase_date))
Sort Method: quicksort Memory: 25kB
Worker 0: Sort Method: quicksort Memory: 25kB
Worker 1: Sort Method: quicksort Memory: 25kB
-> Partial HashAggregate (cost=20015.28..20031.03 rows=900 width=108) (actual time=33.678..33.682 rows=3 loops=3)
Group Key: EXTRACT(year FROM e.purchase_date), EXTRACT(month FROM e.purchase_date)
Batches: 1 Memory Usage: 49kB
Worker 0: Batches: 1 Memory Usage: 49kB
Worker 1: Batches: 1 Memory Usage: 49kB
-> Parallel Hash Right Join (cost=8046.92..19724.57 rows=29071 width=81) (actual time=4.891..31.141 rows=23384 loops=3)
Hash Cond: (p.enrollment_id = e.enrollment_id)
-> Parallel Seq Scan on payment p (cost=0.00..10833.67 rows=266145 width=13) (actual time=0.023..12.330 rows=213333 loops=3)
Filter: (payment_status = 'completed'::payment_status)
Rows Removed by Filter: 53333
-> Parallel Hash (cost=7683.53..7683.53 rows=29071 width=12) (actual time=4.454..4.455 rows=23384 loops=3)
Buckets: 131072 Batches: 1 Memory Usage: 4384kB
-> Parallel Bitmap Heap Scan on enrollment e (cost=789.14..7683.53 rows=29071 width=12) (actual time=0.812..2.932 rows=23384 loops=3)
Recheck Cond: (purchase_date >= '2025-04-01'::date)
Heap Blocks: exact=548
-> Bitmap Index Scan on idx_enrollment_purchase_date (cost=0.00..771.70 rows=69770 width=0) (actual time=0.964..0.964 rows=70152 loops=1)
Index Cond: (purchase_date >= '2025-04-01'::date)
Planning Time: 0.318 ms
Execution Time: 37.000 ms
Време: 38,1 ms (три пуштања: 38,9 / 38,4 / 37,0 ms) наспроти 153,8 ms без филтер, односно 4,0 пати побрзо.
И овој пат планерот го искористи индексот:
-> Bitmap Index Scan on idx_enrollment_purchase_date\n (actual time=0.964..0.964 rows=70152 loops=1)
Ова е клучната разлика. Истиот индекс што беше игнориран во чекор 1 и 2 сега се користи, и тоа со голема добивка, затоа што сега постои услов што исфрлува редови. Индексот не стана подобар, се промени query-то.
| Варијанта | Редови | Време |
без WHERE (тековна функција) | 800.000 | 153,8 ms |
| со филтер по период и индекс | 70.152 | 38,1 ms |
Заклучок
Индексите тука не помагаат, и не постои индекс што би помогнал. Причината е во самата природа на query-то, бидејќи тоа е агрегација врз целата табела без ниту еден WHERE услов. За да се пресмета збир по месец, мора да се допре секој ред од enrollment и payment.
Индексот е корисен кога служи за да се прескокнат редови. Овде нема што да се прескокне, потребни се сите. Во таа ситуација секвенцијалното читање е оптимално, бидејќи чита цели блокови по редослед на дискот, додека index scan би додал индиректност, прво индекс па heap, без да заштеди ниту еден ред. Присилното мерење го докажува тоа емпириски, Index Only Scan е побавен од Seq Scan за оваа намена.
Вреди да се забележи и дека ова query и онака е најбрзото од четирите. Тоа е така затоа што агрегира во само 30 групи и никогаш не прелева на диск, па тука едноставно нема проблем за решавање.
Најважниот наод е мерењето со WHERE услов. Со филтер по период истиот индекс дава 4,0 пати забрзување, што значи дека проблемот не е во индексите, туку во тоа што функцијата секогаш пресметува сè. Ако извештајот во практика се гледа по период, најголемата добивка би дошла од додавање параметри:
dashboard_monthly_totals(p_from DATE, p_to DATE)
dashboard_monthly_courses()
Приход и запишувања по месец, разложено по курс и верзија. Резултатот има 86.400 реда. Ова е најбавната од четирите функции.
Дефиниција
-- Monthly course-specific
CREATE OR REPLACE FUNCTION dashboard_monthly_courses()
RETURNS TABLE (
year INTEGER,
month INTEGER,
course_id INTEGER,
course_name TEXT,
course_description TEXT,
course_difficulty TEXT,
course_price NUMERIC,
version_number INTEGER,
is_version_active BOOLEAN,
total_paid_enrollments BIGINT,
total_students BIGINT,
total_revenue NUMERIC,
total_reviews BIGINT,
average_rating NUMERIC
) AS $$
#variable_conflict use_column
BEGIN
RETURN QUERY
SELECT
EXTRACT(YEAR FROM p.payment_date)::INTEGER AS year,
EXTRACT(MONTH FROM p.payment_date)::INTEGER AS month,
c.course_id::INTEGER AS course_id,
ct.title_short::TEXT AS course_name,
ct.description_short::TEXT AS course_description,
c.difficulty::TEXT AS course_difficulty,
c.price::NUMERIC AS course_price,
cv.version_number::INTEGER AS version_number,
cv.is_active::BOOLEAN AS is_version_active,
COUNT(e.enrollment_id)::BIGINT AS total_paid_enrollments,
COUNT(DISTINCT e.user_id)::BIGINT AS total_students,
SUM(p.amount)::NUMERIC AS total_revenue,
COUNT(r.review_id)::BIGINT AS total_reviews,
COALESCE(AVG(r.rating), 0)::NUMERIC AS average_rating
FROM course c
JOIN course_translate ct ON c.course_id = ct.course_id
JOIN language l ON l.id = ct.language_id
JOIN course_version cv ON c.course_id = cv.course_id
JOIN enrollment e ON cv.course_version_id = e.course_version_id
JOIN payment p ON e.enrollment_id = p.enrollment_id AND p.payment_status = 'completed'
LEFT JOIN review r ON e.enrollment_id = r.enrollment_id
GROUP BY
EXTRACT(YEAR FROM p.payment_date),
EXTRACT(MONTH FROM p.payment_date),
c.course_id, ct.course_translate_id, cv.course_version_id
ORDER BY year DESC, month DESC, total_revenue DESC, total_students DESC;
END;
$$ LANGUAGE plpgsql;
Почетна состојба
Sort (cost=1216174.15..1220973.55 rows=1919760 width=331) (actual time=3403.219..3423.748 rows=86400 loops=1)
Sort Key: (((EXTRACT(year FROM p.payment_date)))::integer) DESC, (((EXTRACT(month FROM p.payment_date)))::integer) DESC, (sum(p.amount)) DESC, (count(DISTINCT e.user_id)) DESC
Sort Method: external merge Disk: 14728kB
-> GroupAggregate (cost=157773.34..425268.25 rows=1919760 width=331) (actual time=488.509..3344.444 rows=86400 loops=1)
Group Key: cv.course_version_id, (EXTRACT(year FROM p.payment_date)), (EXTRACT(month FROM p.payment_date)), c.course_id, ct.course_translate_id
-> Incremental Sort (cost=157773.34..305283.25 rows=1919760 width=192) (actual time=488.481..3119.040 rows=1920000 loops=1)
Sort Key: cv.course_version_id, (EXTRACT(year FROM p.payment_date)), (EXTRACT(month FROM p.payment_date)), c.course_id, ct.course_translate_id, e.user_id
Presorted Key: cv.course_version_id
Full-sort Groups: 5400 Sort Method: quicksort Average Memory: 35kB Peak Memory: 35kB
Pre-sorted Groups: 5400 Sort Method: quicksort Average Memory: 67kB Peak Memory: 68kB
-> Merge Join (cost=157752.78..201287.46 rows=1919760 width=192) (actual time=488.170..857.077 rows=1920000 loops=1)
Merge Cond: (cv.course_version_id = e.course_version_id)
-> Nested Loop (cost=1.00..3496.80 rows=18000 width=91) (actual time=173.855..190.556 rows=18000 loops=1)
-> Nested Loop (cost=0.86..3091.22 rows=18000 width=99) (actual time=173.848..187.208 rows=18000 loops=1)
Join Filter: (c.course_id = ct.course_id)
-> Nested Loop (cost=0.57..1142.36 rows=6000 width=38) (actual time=173.836..178.986 rows=6000 loops=1)
-> Index Scan using course_version_pkey on course_version cv (cost=0.28..355.58 rows=6000 width=21) (actual time=0.006..1.836 rows=6000 loops=1)
-> Memoize (cost=0.29..0.33 rows=1 width=17) (actual time=0.029..0.029 rows=1 loops=6000)
Cache Key: cv.course_id
Cache Mode: logical
Hits: 4000 Misses: 2000 Evictions: 0 Overflows: 0 Memory Usage: 237kB
-> Index Scan using course_pkey on course c (cost=0.28..0.32 rows=1 width=17) (actual time=0.001..0.001 rows=1 loops=2000)
Index Cond: (course_id = cv.course_id)
-> Memoize (cost=0.29..0.81 rows=3 width=77) (actual time=0.000..0.001 rows=3 loops=6000)
Cache Key: cv.course_id
Cache Mode: logical
Hits: 4000 Misses: 2000 Evictions: 0 Overflows: 0 Memory Usage: 797kB
-> Index Scan using uq_course_translate on course_translate ct (cost=0.28..0.80 rows=3 width=77) (actual time=0.001..0.002 rows=3 loops=2000)
Index Cond: (course_id = cv.course_id)
-> Memoize (cost=0.14..0.16 rows=1 width=8) (actual time=0.000..0.000 rows=1 loops=18000)
Cache Key: ct.language_id
Cache Mode: logical
Hits: 17997 Misses: 3 Evictions: 0 Overflows: 0 Memory Usage: 1kB
-> Index Only Scan using language_pkey on language l (cost=0.13..0.15 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=3)
Index Cond: (id = ct.language_id)
Heap Fetches: 0
-> Materialize (cost=157751.13..160950.73 rows=639920 width=45) (actual time=314.289..406.363 rows=1919998 loops=1)
-> Sort (cost=157751.13..159350.93 rows=639920 width=45) (actual time=314.287..347.268 rows=640000 loops=1)
Sort Key: e.course_version_id
Sort Method: external merge Disk: 29944kB
-> Merge Left Join (cost=1.80..76351.25 rows=639920 width=45) (actual time=0.043..237.686 rows=640000 loops=1)
Merge Cond: (e.enrollment_id = r.enrollment_id)
-> Merge Join (cost=1.38..66767.19 rows=639920 width=33) (actual time=0.031..183.912 rows=640000 loops=1)
Merge Cond: (e.enrollment_id = p.enrollment_id)
-> Index Scan using enrollment_pkey on enrollment e (cost=0.42..27318.42 rows=800000 width=24) (actual time=0.017..55.353 rows=800000 loops=1)
-> Index Scan using uq_payment_enrollment on payment p (cost=0.42..29454.42 rows=639920 width=17) (actual time=0.010..59.537 rows=640000 loops=1)
Filter: (payment_status = 'completed'::payment_status)
Rows Removed by Filter: 160000
-> Index Scan using uq_review_enrollment on review r (cost=0.42..6305.42 rows=160000 width=20) (actual time=0.009..12.521 rows=160000 loops=1)
Planning Time: 1.646 ms
JIT:
Functions: 53
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 0.945 ms (Deform 0.511 ms), Inlining 30.437 ms, Optimization 75.801 ms, Emission 67.586 ms, Total 174.768 ms
Execution Time: 3438.197 ms
Време: 3434,4 ms (три пуштања: 3446,3 / 3418,8 / 3438,2 ms).
Планот покажува неколку работи. Merge Join враќа 1.920.000 реда, тројно повеќе од очекуваното. Incremental Sort трае од 488 ms до 3119 ms, што значи дека околу 2,6 секунди, односно приблизно 76% од целото време, се троши на едно сортирање. Сортирањата се прелеваат на диск, external merge Disk: 29944kB и external merge Disk: 14728kB. Во клучот за сортирање влегува и e.user_id, поради COUNT(DISTINCT e.user_id). Конечно, enrollment.course_version_id нема индекс.
Оттука произлегуваат три идеи: индекс врз непокриениот foreign key enrollment.course_version_id, композитен индекс (course_version_id, user_id) што би можел да го понуди редоследот што Incremental Sort го бара, и парцијален covering индекс врз payment за наплатените плаќања.
Чекор 1, индекс врз непокриениот foreign key
CREATE INDEX idx_enrollment_course_version_id\n ON enrollment(course_version_id);
Sort (cost=1213871.37..1218661.37 rows=1916001 width=331) (actual time=3384.601..3405.332 rows=86400 loops=1)
Sort Key: (((EXTRACT(year FROM p.payment_date)))::integer) DESC, (((EXTRACT(month FROM p.payment_date)))::integer) DESC, (sum(p.amount)) DESC, (count(DISTINCT e.user_id)) DESC
Sort Method: external merge Disk: 14728kB
-> GroupAggregate (cost=157587.52..424539.86 rows=1916001 width=331) (actual time=493.498..3324.347 rows=86400 loops=1)
Group Key: cv.course_version_id, (EXTRACT(year FROM p.payment_date)), (EXTRACT(month FROM p.payment_date)), c.course_id, ct.course_translate_id
-> Incremental Sort (cost=157587.52..304789.79 rows=1916001 width=192) (actual time=493.453..3100.893 rows=1920000 loops=1)
Sort Key: cv.course_version_id, (EXTRACT(year FROM p.payment_date)), (EXTRACT(month FROM p.payment_date)), c.course_id, ct.course_translate_id, e.user_id
Presorted Key: cv.course_version_id
Full-sort Groups: 5400 Sort Method: quicksort Average Memory: 35kB Peak Memory: 35kB
Pre-sorted Groups: 5400 Sort Method: quicksort Average Memory: 67kB Peak Memory: 68kB
-> Merge Join (cost=157567.00..201024.48 rows=1916001 width=192) (actual time=493.176..843.628 rows=1920000 loops=1)
Merge Cond: (cv.course_version_id = e.course_version_id)
-> Nested Loop (cost=1.00..3496.80 rows=18000 width=91) (actual time=181.119..197.409 rows=18000 loops=1)
-> Nested Loop (cost=0.86..3091.22 rows=18000 width=99) (actual time=181.113..194.116 rows=18000 loops=1)
Join Filter: (c.course_id = ct.course_id)
-> Nested Loop (cost=0.57..1142.36 rows=6000 width=38) (actual time=181.097..186.273 rows=6000 loops=1)
-> Index Scan using course_version_pkey on course_version cv (cost=0.28..355.58 rows=6000 width=21) (actual time=0.006..1.839 rows=6000 loops=1)
-> Memoize (cost=0.29..0.33 rows=1 width=17) (actual time=0.031..0.031 rows=1 loops=6000)
Cache Key: cv.course_id
Cache Mode: logical
Hits: 4000 Misses: 2000 Evictions: 0 Overflows: 0 Memory Usage: 237kB
-> Index Scan using course_pkey on course c (cost=0.28..0.32 rows=1 width=17) (actual time=0.001..0.001 rows=1 loops=2000)
Index Cond: (course_id = cv.course_id)
-> Memoize (cost=0.29..0.81 rows=3 width=77) (actual time=0.000..0.001 rows=3 loops=6000)
Cache Key: cv.course_id
Cache Mode: logical
Hits: 4000 Misses: 2000 Evictions: 0 Overflows: 0 Memory Usage: 797kB
-> Index Scan using uq_course_translate on course_translate ct (cost=0.28..0.80 rows=3 width=77) (actual time=0.001..0.002 rows=3 loops=2000)
Index Cond: (course_id = cv.course_id)
-> Memoize (cost=0.14..0.16 rows=1 width=8) (actual time=0.000..0.000 rows=1 loops=18000)
Cache Key: ct.language_id
Cache Mode: logical
Hits: 17997 Misses: 3 Evictions: 0 Overflows: 0 Memory Usage: 1kB
-> Index Only Scan using language_pkey on language l (cost=0.13..0.15 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=3)
Index Cond: (id = ct.language_id)
Heap Fetches: 0
-> Materialize (cost=157565.99..160759.33 rows=638667 width=45) (actual time=312.034..404.507 rows=1919998 loops=1)
-> Sort (cost=157565.99..159162.66 rows=638667 width=45) (actual time=312.032..345.703 rows=640000 loops=1)
Sort Key: e.course_version_id
Sort Method: external merge Disk: 29944kB
-> Merge Left Join (cost=4.28..76334.47 rows=638667 width=45) (actual time=0.046..236.700 rows=640000 loops=1)
Merge Cond: (e.enrollment_id = r.enrollment_id)
-> Merge Join (cost=3.86..66755.26 rows=638667 width=33) (actual time=0.033..183.695 rows=640000 loops=1)
Merge Cond: (e.enrollment_id = p.enrollment_id)
-> Index Scan using enrollment_pkey on enrollment e (cost=0.42..27318.42 rows=800000 width=24) (actual time=0.018..55.760 rows=800000 loops=1)
-> Index Scan using uq_payment_enrollment on payment p (cost=0.42..29454.42 rows=638667 width=17) (actual time=0.011..59.935 rows=640000 loops=1)
Filter: (payment_status = 'completed'::payment_status)
Rows Removed by Filter: 160000
-> Index Scan using uq_review_enrollment on review r (cost=0.42..6305.42 rows=160000 width=20) (actual time=0.010..12.617 rows=160000 loops=1)
Planning Time: 1.632 ms
JIT:
Functions: 53
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 0.834 ms (Deform 0.414 ms), Inlining 34.467 ms, Optimization 77.756 ms, Emission 68.858 ms, Total 181.915 ms
Execution Time: 3419.300 ms
Време: 3425,5 ms (три пуштања: 3435,3 / 3422,0 / 3419,3 ms), односно без ефект (+0,3%, во границите на мерната грешка).
external merge Disk: 29944kB е сè уште тука, индексот не го отстрани сортирањето.
Чекор 2, композитен индекс за редослед и парцијален индекс врз payment
CREATE INDEX idx_enrollment_cv_user\n ON enrollment(course_version_id, user_id) INCLUDE (enrollment_id);\n\nCREATE INDEX idx_payment_completed_cov2\n ON payment(enrollment_id) INCLUDE (amount, payment_date)\n WHERE payment_status = 'completed';
Sort (cost=1210572.31..1215378.11 rows=1922319 width=331) (actual time=3346.915..3366.914 rows=86400 loops=1)
Sort Key: (((EXTRACT(year FROM p.payment_date)))::integer) DESC, (((EXTRACT(month FROM p.payment_date)))::integer) DESC, (sum(p.amount)) DESC, (count(DISTINCT e.user_id)) DESC
Sort Method: external merge Disk: 14728kB
-> GroupAggregate (cost=150730.70..418596.88 rows=1922319 width=331) (actual time=422.918..3285.159 rows=86400 loops=1)
Group Key: cv.course_version_id, (EXTRACT(year FROM p.payment_date)), (EXTRACT(month FROM p.payment_date)), c.course_id, ct.course_translate_id
-> Incremental Sort (cost=150730.70..298451.94 rows=1922319 width=192) (actual time=422.888..3057.450 rows=1920000 loops=1)
Sort Key: cv.course_version_id, (EXTRACT(year FROM p.payment_date)), (EXTRACT(month FROM p.payment_date)), c.course_id, ct.course_translate_id, e.user_id
Presorted Key: cv.course_version_id
Full-sort Groups: 5400 Sort Method: quicksort Average Memory: 35kB Peak Memory: 35kB
Pre-sorted Groups: 5400 Sort Method: quicksort Average Memory: 67kB Peak Memory: 68kB
-> Merge Join (cost=150710.10..194299.21 rows=1922319 width=192) (actual time=422.594..786.566 rows=1920000 loops=1)
Merge Cond: (cv.course_version_id = e.course_version_id)
-> Nested Loop (cost=1.00..3496.80 rows=18000 width=91) (actual time=161.040..177.890 rows=18000 loops=1)
-> Nested Loop (cost=0.86..3091.22 rows=18000 width=99) (actual time=161.033..174.454 rows=18000 loops=1)
Join Filter: (c.course_id = ct.course_id)
-> Nested Loop (cost=0.57..1142.36 rows=6000 width=38) (actual time=161.022..166.381 rows=6000 loops=1)
-> Index Scan using course_version_pkey on course_version cv (cost=0.28..355.58 rows=6000 width=21) (actual time=0.006..1.895 rows=6000 loops=1)
-> Memoize (cost=0.29..0.33 rows=1 width=17) (actual time=0.027..0.027 rows=1 loops=6000)
Cache Key: cv.course_id
Cache Mode: logical
Hits: 4000 Misses: 2000 Evictions: 0 Overflows: 0 Memory Usage: 237kB
-> Index Scan using course_pkey on course c (cost=0.28..0.32 rows=1 width=17) (actual time=0.001..0.001 rows=1 loops=2000)
Index Cond: (course_id = cv.course_id)
-> Memoize (cost=0.29..0.81 rows=3 width=77) (actual time=0.000..0.001 rows=3 loops=6000)
Cache Key: cv.course_id
Cache Mode: logical
Hits: 4000 Misses: 2000 Evictions: 0 Overflows: 0 Memory Usage: 797kB
-> Index Scan using uq_course_translate on course_translate ct (cost=0.28..0.80 rows=3 width=77) (actual time=0.001..0.002 rows=3 loops=2000)
Index Cond: (course_id = cv.course_id)
-> Memoize (cost=0.14..0.16 rows=1 width=8) (actual time=0.000..0.000 rows=1 loops=18000)
Cache Key: ct.language_id
Cache Mode: logical
Hits: 17997 Misses: 3 Evictions: 0 Overflows: 0 Memory Usage: 1kB
-> Index Only Scan using language_pkey on language l (cost=0.13..0.15 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=3)
Index Cond: (id = ct.language_id)
Heap Fetches: 0
-> Materialize (cost=150709.09..153912.96 rows=640773 width=45) (actual time=261.530..354.982 rows=1919998 loops=1)
-> Sort (cost=150709.09..152311.03 rows=640773 width=45) (actual time=261.528..293.805 rows=640000 loops=1)
Sort Key: e.course_version_id
Sort Method: external merge Disk: 29944kB
-> Merge Left Join (cost=2.33..69196.29 rows=640773 width=45) (actual time=0.026..190.549 rows=640000 loops=1)
Merge Cond: (e.enrollment_id = r.enrollment_id)
-> Merge Join (cost=1.91..59607.51 rows=640773 width=33) (actual time=0.017..141.685 rows=640000 loops=1)
Merge Cond: (e.enrollment_id = p.enrollment_id)
-> Index Scan using enrollment_pkey on enrollment e (cost=0.42..27318.42 rows=800000 width=24) (actual time=0.008..44.448 rows=800000 loops=1)
-> Index Only Scan using idx_payment_completed_cov2 on payment p (cost=0.42..22280.02 rows=640773 width=17) (actual time=0.006..27.571 rows=640000 loops=1)
Heap Fetches: 0
-> Index Scan using uq_review_enrollment on review r (cost=0.42..6305.42 rows=160000 width=20) (actual time=0.006..9.342 rows=160000 loops=1)
Planning Time: 1.767 ms
JIT:
Functions: 49
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 0.946 ms (Deform 0.461 ms), Inlining 30.944 ms, Optimization 68.381 ms, Emission 61.678 ms, Total 161.949 ms
Execution Time: 3381.984 ms
Време: 3360,9 ms (три пуштања: 3354,5 / 3346,1 / 3382,0 ms), односно маргинално забрзување од 2,1%.
Овде има мал напредок и тој е видлив во планот, бидејќи парцијалниот индекс навистина се користи:
-> Index Only Scan using idx_payment_completed_cov2 on payment p\n (actual time=0.006..27.571 rows=640000 loops=1)
Наспроти оригиналниот Index Scan ... (actual time=0.010..59.537), тој јазол стана 2,2 пати побрз. Но бидејќи носи само околу 30 ms од вкупно околу 3400 ms, крајниот ефект е околу 2%. Incremental Sort и понатаму троши 3057 ms и останува недопрен.
Проверка со work_mem
Бидејќи сортирањата се прелеваат на диск, тестирано е и подигање на work_mem од 4 MB на 256 MB.
Sort Method: quicksort Memory: 18260kB (наместо external merge Disk: 14728kB)\nSort Method: quicksort Memory: 62077kB (наместо external merge Disk: 29944kB)\nExecution Time: 3388.270 ms
Прелевањето на диск целосно исчезна, но времето остана речиси исто, 3388 ms наспроти 3434,4 ms. Заклучокот е дека тесното грло не е диск I/O, бидејќи оперативниот систем и онака ги држеше тие привремени датотеки во page cache. Проблемот е чисто процесорски, сортирање и агрегирање на 1,92 милиони редови.
Од каде доаѓаат 1.920.000 редови
Query-то содржи join кон language без филтер по јазик:
JOIN language l ON l.id = ct.language_id
Во базата има 3 јазици, mk, en и sq, и точно 3 преводи по курс, па секое запишување се множи со 3. Од 640.000 платени запишувања се добиваат 1.920.000 реда во join-от, а излезот е 86.400 реда наместо 28.800. Со додаден филтер за јазик:
JOIN language l ON l.id = ct.language_id AND l.value = 'en'
времето паѓа на 1055,3 ms, односно 3,3 пати побрзо, и излезот станува 28.800 реда.
Истото важи и за dashboard_course_performance(). И таа функција намерно враќа резултат за сите јазици, и таму трите јазика чинат приблизно двојно повеќе време.
Ова не е предлог да се смени функцијата. Враќањето на сите јазици е намерна одлука, а филтрирањето би го променило резултатот, од 86.400 на 28.800 реда. Мерењето е наведено само за да се види каде оди времето. Ако некогаш затреба извештај само за еден јазик, најефтино е јазикот да биде параметар на функцијата, наместо филтрирање врз готовиот резултат, бидејќи тогаш трите пати повеќе редови никогаш не влегуваат во агрегацијата.
Заклучок
Индексите донесоа 2,1%, а вистинскиот проблем е на друго место. Тесното грло е Incremental Sort врз 1,92 милиони редови, кој сам по себе троши околу три четвртини од времето, и тоа сортирање не може да се замени со индекс поради две причини.
Првата е што клучот за сортирање се состои од столбови од повеќе различни табели, cv.course_version_id, c.course_id, ct.course_translate_id и e.user_id, плус пресметани изрази со EXTRACT(...). Индексот дава подреденост само во рамките на една табела, па не постои индекс што би го дал тој редослед врз резултатот од join-от. Втората е што COUNT(DISTINCT e.user_id) го присилува user_id да влезе во клучот за сортирање, бидејќи PostgreSQL мора да ги групира вредностите за да ги изброи уникатните.
Затоа поредокот на ефикасност овде е следниот:
| Интервенција | Време | Добивка |
| почетна состојба | 3434,4 ms | — |
| индекси, чекор 1 и 2 | 3360,9 ms | 2,1% |
work_mem 4 MB на 256 MB | 3388,3 ms | околу 1% |
| филтер по јазик, менува резултат | 1055,3 ms | 69% |
Со други зборови, сите индекси заедно донесоа околу 2%, додека бројот на редови што влегуваат во агрегацијата чини три пати побавно query. Кога планот покажува дека проблемот е бројот на редови, решението е да се намали тој број, а не да се додаваат индекси.
dashboard_course_performance()
Изведба на секој курс и секоја негова верзија, за сите времиња и за сите јазици. Функцијата намерно не филтрира по јазик. course_translate содржи по 3 преводи за секој курс, mk, en и sq, па резултатот содржи по еден ред за секој превод на секоја верзија на курс, вкупно 18.000 реда од 6.000 верзии.
Дефиниција
-- All time course specific, for each course version
CREATE OR REPLACE FUNCTION dashboard_course_performance()
RETURNS TABLE (
course_id INTEGER,
course_name TEXT,
course_description TEXT,
course_difficulty TEXT,
course_price NUMERIC,
version_number INTEGER,
is_version_active BOOLEAN,
total_enrollments BIGINT,
paid_enrollments BIGINT,
trial_enrollments BIGINT,
total_completions BIGINT,
total_students BIGINT,
total_revenue NUMERIC,
average_rating NUMERIC,
total_reviews BIGINT,
avg_days_to_complete INTEGER,
completion_rate_percentage NUMERIC,
avg_lecture_completion_percentage NUMERIC
) AS $$
#variable_conflict use_column
BEGIN
RETURN QUERY
WITH lecture_progress AS (
SELECT ucp.enrollment_id,
COUNT(*) FILTER (WHERE ucp.is_completed) * 100.0 / COUNT(*) AS completed_percentage
FROM user_course_progress ucp
GROUP BY ucp.enrollment_id
)
SELECT
c.course_id::INTEGER AS course_id,
ct.title_short::TEXT AS course_name,
ct.description_short::TEXT AS course_description,
c.difficulty::TEXT AS course_difficulty,
c.price::NUMERIC AS course_price,
cv.version_number::INTEGER AS version_number,
cv.is_active::BOOLEAN AS is_version_active,
COUNT(e.enrollment_id)::BIGINT AS total_enrollments,
COUNT(CASE WHEN p.payment_status = 'completed' THEN e.enrollment_id END)::BIGINT AS paid_enrollments,
COUNT(CASE WHEN p.payment_id IS NULL THEN e.enrollment_id END)::BIGINT AS trial_enrollments,
COUNT(CASE WHEN e.completion_date IS NOT NULL THEN e.enrollment_id END)::BIGINT AS total_completions,
COUNT(DISTINCT e.user_id)::BIGINT AS total_students,
COALESCE(SUM(CASE WHEN p.payment_status = 'completed' THEN p.amount ELSE 0 END), 0)::NUMERIC AS total_revenue,
COALESCE(AVG(r.rating), 0)::NUMERIC AS average_rating,
COUNT(r.review_id)::BIGINT AS total_reviews,
ROUND(AVG(CASE WHEN e.completion_date IS NOT NULL
THEN (e.completion_date - e.activation_date)
END), 0)::INTEGER AS avg_days_to_complete,
COALESCE(
ROUND(100.0 * COUNT(CASE WHEN e.completion_date IS NOT NULL THEN e.enrollment_id END)::NUMERIC
/ NULLIF(COUNT(e.enrollment_id), 0), 2),
0)::NUMERIC AS completion_rate_percentage,
COALESCE(ROUND(AVG(lp.completed_percentage), 2), 0)::NUMERIC AS avg_lecture_completion_percentage
FROM course c
JOIN course_translate ct ON c.course_id = ct.course_id
JOIN language l ON l.id = ct.language_id
JOIN course_version cv ON c.course_id = cv.course_id
LEFT JOIN enrollment e ON cv.course_version_id = e.course_version_id -- include versions with zero enrollments
LEFT JOIN payment p ON e.enrollment_id = p.enrollment_id -- include enrollments without payments
LEFT JOIN review r ON e.enrollment_id = r.enrollment_id -- include enrollments without reviews
LEFT JOIN lecture_progress lp ON lp.enrollment_id = e.enrollment_id -- include enrollments with no progress rows
GROUP BY c.course_id, ct.course_translate_id, cv.course_version_id
ORDER BY total_revenue DESC, completion_rate_percentage DESC;
END;
$$ LANGUAGE plpgsql;
Почетна состојба
Sort (cost=1738812.79..1744812.79 rows=2400000 width=351) (actual time=2946.780..2947.192 rows=18000 loops=1)
Sort Key: (COALESCE(sum(CASE WHEN (p.payment_status = 'completed'::payment_status) THEN p.amount ELSE '0'::numeric END), '0'::numeric)) DESC, (COALESCE(round(((100.0 * (count(CASE WHEN (e.completion_date IS NOT NULL) THEN e.enrollment_id ELSE NULL::bigint END))::numeric) / (NULLIF(count(e.enrollment_id), 0))::numeric), 2), '0'::numeric)) DESC
Sort Method: quicksort Memory: 4026kB
-> GroupAggregate (cost=325503.28..713378.56 rows=2400000 width=351) (actual time=936.469..2937.854 rows=18000 loops=1)
Group Key: cv.course_version_id, c.course_id, ct.course_translate_id
-> Incremental Sort (cost=325503.28..497378.56 rows=2400000 width=176) (actual time=936.414..2723.983 rows=2400000 loops=1)
Sort Key: cv.course_version_id, c.course_id, ct.course_translate_id, e.user_id
Presorted Key: cv.course_version_id
Full-sort Groups: 6000 Sort Method: quicksort Average Memory: 35kB Peak Memory: 35kB
Pre-sorted Groups: 6000 Sort Method: quicksort Average Memory: 89kB Peak Memory: 89kB
-> Merge Left Join (cost=325479.65..363532.28 rows=2400000 width=176) (actual time=936.128..1192.913 rows=2400000 loops=1)
Merge Cond: (cv.course_version_id = e.course_version_id)
-> Sort (cost=2523.56..2568.56 rows=18000 width=91) (actual time=236.097..236.814 rows=18000 loops=1)
Sort Key: cv.course_version_id
Sort Method: quicksort Memory: 2692kB
-> Hash Join (cost=253.07..1251.35 rows=18000 width=91) (actual time=230.258..233.758 rows=18000 loops=1)
Hash Cond: (c.course_id = cv.course_id)
-> Hash Join (cost=73.07..853.85 rows=6000 width=86) (actual time=229.654..232.239 rows=6000 loops=1)
Hash Cond: (ct.language_id = l.id)
-> Hash Join (cost=72.00..814.78 rows=6000 width=94) (actual time=0.265..2.501 rows=6000 loops=1)
Hash Cond: (ct.course_id = c.course_id)
-> Seq Scan on course_translate ct (cost=0.00..727.00 rows=6000 width=77) (actual time=0.023..1.510 rows=6000 loops=1)
-> Hash (cost=47.00..47.00 rows=2000 width=17) (actual time=0.235..0.235 rows=2000 loops=1)
Buckets: 2048 Batches: 1 Memory Usage: 112kB
-> Seq Scan on course c (cost=0.00..47.00 rows=2000 width=17) (actual time=0.009..0.133 rows=2000 loops=1)
-> Hash (cost=1.03..1.03 rows=3 width=8) (actual time=229.383..229.383 rows=3 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on language l (cost=0.00..1.03 rows=3 width=8) (actual time=229.370..229.373 rows=3 loops=1)
-> Hash (cost=105.00..105.00 rows=6000 width=21) (actual time=0.599..0.599 rows=6000 loops=1)
Buckets: 8192 Batches: 1 Memory Usage: 393kB
-> Seq Scan on course_version cv (cost=0.00..105.00 rows=6000 width=21) (actual time=0.007..0.300 rows=6000 loops=1)
-> Materialize (cost=322955.29..326955.29 rows=800000 width=93) (actual time=700.004..814.939 rows=2399998 loops=1)
-> Sort (cost=322955.29..324955.29 rows=800000 width=93) (actual time=700.002..742.481 rows=800000 loops=1)
Sort Key: e.course_version_id
Sort Method: external merge Disk: 63912kB
-> Merge Left Join (cost=1.70..162483.72 rows=800000 width=93) (actual time=0.069..533.088 rows=800000 loops=1)
Merge Cond: (e.enrollment_id = ucp.enrollment_id)
-> Merge Left Join (cost=1.27..77078.27 rows=800000 width=61) (actual time=0.040..257.927 rows=800000 loops=1)
Merge Cond: (e.enrollment_id = r.enrollment_id)
-> Merge Left Join (cost=0.85..66772.85 rows=800000 width=49) (actual time=0.027..195.293 rows=800000 loops=1)
Merge Cond: (e.enrollment_id = p.enrollment_id)
-> Index Scan using enrollment_pkey on enrollment e (cost=0.42..27318.42 rows=800000 width=32) (actual time=0.014..54.881 rows=800000 loops=1)
-> Index Scan using uq_payment_enrollment on payment p (cost=0.42..27454.42 rows=800000 width=25) (actual time=0.009..54.968 rows=800000 loops=1)
-> Index Scan using uq_review_enrollment on review r (cost=0.42..6305.42 rows=160000 width=20) (actual time=0.009..12.316 rows=160000 loops=1)
-> GroupAggregate (cost=0.42..76660.31 rows=299784 width=40) (actual time=0.026..223.124 rows=320000 loops=1)
Group Key: ucp.enrollment_id
-> Index Scan using uq_ucp on user_course_progress ucp (cost=0.42..63464.63 rows=960000 width=9) (actual time=0.014..120.943 rows=960000 loops=1)
Planning Time: 1.689 ms
JIT:
Functions: 58
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 1.016 ms (Deform 0.503 ms), Inlining 31.592 ms, Optimization 104.539 ms, Emission 93.272 ms, Total 230.418 ms
Execution Time: 2958.273 ms
Време: 2974,0 ms (три пуштања: 2960,8 / 3003,1 / 2958,3 ms).
Од планот, Incremental Sort трае од 936 ms до 2724 ms, значи околу 1530 ms или приблизно 52% од целото време се троши на едно сортирање. Merge Left Join враќа 2.400.000 реда, а функцијата на крај враќа само 18.000. Materialize враќа 2.399.998 реда од 800.000 сортирани, што значи дека истите редови се препрочитуваат по еднаш за секој јазик. Сортирањето по e.course_version_id се прелева на диск со external merge Disk: 63912kB. Најскапиот поединечен scan е Index Scan using uq_ucp on user_course_progress, кој чита 960.000 редови за 120,9 ms. И тука enrollment.course_version_id нема индекс.
Двете идеи се индекс врз enrollment.course_version_id, за да се избегне големото сортирање, и covering индекс врз user_course_progress, за CTE-то lecture_progress да може да работи преку Index Only Scan без пристап до heap-от.
Чекор 1, индекс врз enrollment.course_version_id
CREATE INDEX idx_enrollment_course_version_id\n ON enrollment(course_version_id);
Sort (cost=1738846.95..1744846.95 rows=2400000 width=351) (actual time=2943.828..2944.239 rows=18000 loops=1)
Sort Key: (COALESCE(sum(CASE WHEN (p.payment_status = 'completed'::payment_status) THEN p.amount ELSE '0'::numeric END), '0'::numeric)) DESC, (COALESCE(round(((100.0 * (count(CASE WHEN (e.completion_date IS NOT NULL) THEN e.enrollment_id ELSE NULL::bigint END))::numeric) / (NULLIF(count(e.enrollment_id), 0))::numeric), 2), '0'::numeric)) DESC
Sort Method: quicksort Memory: 4026kB
-> GroupAggregate (cost=325500.08..713412.72 rows=2400000 width=351) (actual time=931.922..2934.137 rows=18000 loops=1)
Group Key: cv.course_version_id, c.course_id, ct.course_translate_id
-> Incremental Sort (cost=325500.08..497412.72 rows=2400000 width=176) (actual time=931.861..2720.117 rows=2400000 loops=1)
Sort Key: cv.course_version_id, c.course_id, ct.course_translate_id, e.user_id
Presorted Key: cv.course_version_id
Full-sort Groups: 6000 Sort Method: quicksort Average Memory: 35kB Peak Memory: 35kB
Pre-sorted Groups: 6000 Sort Method: quicksort Average Memory: 89kB Peak Memory: 89kB
-> Merge Left Join (cost=325476.44..363566.44 rows=2400000 width=176) (actual time=931.564..1184.598 rows=2400000 loops=1)
Merge Cond: (cv.course_version_id = e.course_version_id)
-> Sort (cost=2523.56..2568.56 rows=18000 width=91) (actual time=234.588..235.294 rows=18000 loops=1)
Sort Key: cv.course_version_id
Sort Method: quicksort Memory: 2692kB
-> Hash Join (cost=253.07..1251.35 rows=18000 width=91) (actual time=228.752..232.321 rows=18000 loops=1)
Hash Cond: (c.course_id = cv.course_id)
-> Hash Join (cost=73.07..853.85 rows=6000 width=86) (actual time=228.156..230.803 rows=6000 loops=1)
Hash Cond: (ct.language_id = l.id)
-> Hash Join (cost=72.00..814.78 rows=6000 width=94) (actual time=0.261..2.581 rows=6000 loops=1)
Hash Cond: (ct.course_id = c.course_id)
-> Seq Scan on course_translate ct (cost=0.00..727.00 rows=6000 width=77) (actual time=0.023..1.592 rows=6000 loops=1)
-> Hash (cost=47.00..47.00 rows=2000 width=17) (actual time=0.230..0.231 rows=2000 loops=1)
Buckets: 2048 Batches: 1 Memory Usage: 112kB
-> Seq Scan on course c (cost=0.00..47.00 rows=2000 width=17) (actual time=0.008..0.136 rows=2000 loops=1)
-> Hash (cost=1.03..1.03 rows=3 width=8) (actual time=227.888..227.888 rows=3 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on language l (cost=0.00..1.03 rows=3 width=8) (actual time=227.875..227.878 rows=3 loops=1)
-> Hash (cost=105.00..105.00 rows=6000 width=21) (actual time=0.591..0.592 rows=6000 loops=1)
Buckets: 8192 Batches: 1 Memory Usage: 393kB
-> Seq Scan on course_version cv (cost=0.00..105.00 rows=6000 width=21) (actual time=0.006..0.295 rows=6000 loops=1)
-> Materialize (cost=322952.88..326952.88 rows=800000 width=93) (actual time=696.947..810.881 rows=2399998 loops=1)
-> Sort (cost=322952.88..324952.88 rows=800000 width=93) (actual time=696.944..738.560 rows=800000 loops=1)
Sort Key: e.course_version_id
Sort Method: external merge Disk: 63912kB
-> Merge Left Join (cost=1.70..162481.32 rows=800000 width=93) (actual time=0.071..528.730 rows=800000 loops=1)
Merge Cond: (e.enrollment_id = ucp.enrollment_id)
-> Merge Left Join (cost=1.27..77075.86 rows=800000 width=61) (actual time=0.040..258.573 rows=800000 loops=1)
Merge Cond: (e.enrollment_id = r.enrollment_id)
-> Merge Left Join (cost=0.85..66770.86 rows=800000 width=49) (actual time=0.028..195.985 rows=800000 loops=1)
Merge Cond: (e.enrollment_id = p.enrollment_id)
-> Index Scan using enrollment_pkey on enrollment e (cost=0.42..27318.42 rows=800000 width=32) (actual time=0.015..55.784 rows=800000 loops=1)
-> Index Scan using uq_payment_enrollment on payment p (cost=0.42..27454.42 rows=800000 width=25) (actual time=0.008..55.376 rows=800000 loops=1)
-> Index Scan using uq_review_enrollment on review r (cost=0.42..6305.42 rows=160000 width=20) (actual time=0.008..12.441 rows=160000 loops=1)
-> GroupAggregate (cost=0.42..76660.31 rows=299784 width=40) (actual time=0.028..219.215 rows=320000 loops=1)
Group Key: ucp.enrollment_id
-> Index Scan using uq_ucp on user_course_progress ucp (cost=0.42..63464.63 rows=960000 width=9) (actual time=0.013..117.947 rows=960000 loops=1)
Planning Time: 1.627 ms
JIT:
Functions: 58
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 1.100 ms (Deform 0.571 ms), Inlining 31.834 ms, Optimization 103.894 ms, Emission 92.180 ms, Total 229.007 ms
Execution Time: 2955.216 ms
Време: 2949,2 ms (три пуштања: 2941,1 / 2951,3 / 2955,2 ms), односно без ефект (+0,8%, во границите на мерната грешка).
Индексот не е искористен. Планерот останува на веригата merge join-ови по enrollment_id, бидејќи payment, review и user_course_progress сите се спојуваат по enrollment_id и тие индекси веќе постојат, па дури потоа сортира по course_version_id. external merge Disk: 63912kB останува.
Чекор 2, covering индекс врз user_course_progress
CREATE INDEX idx_ucp_enrollment_completed\n ON user_course_progress(enrollment_id) INCLUDE (is_completed);
Sort (cost=1705431.82..1711431.82 rows=2400000 width=351) (actual time=2850.269..2850.660 rows=18000 loops=1)
Sort Key: (COALESCE(sum(CASE WHEN (p.payment_status = 'completed'::payment_status) THEN p.amount ELSE '0'::numeric END), '0'::numeric)) DESC, (COALESCE(round(((100.0 * (count(CASE WHEN (e.completion_date IS NOT NULL) THEN e.enrollment_id ELSE NULL::bigint END))::numeric) / (NULLIF(count(e.enrollment_id), 0))::numeric), 2), '0'::numeric)) DESC
Sort Method: quicksort Memory: 4026kB
-> GroupAggregate (cost=292084.95..679997.59 rows=2400000 width=351) (actual time=821.100..2841.442 rows=18000 loops=1)
Group Key: cv.course_version_id, c.course_id, ct.course_translate_id
-> Incremental Sort (cost=292084.95..463997.59 rows=2400000 width=176) (actual time=821.042..2626.356 rows=2400000 loops=1)
Sort Key: cv.course_version_id, c.course_id, ct.course_translate_id, e.user_id
Presorted Key: cv.course_version_id
Full-sort Groups: 6000 Sort Method: quicksort Average Memory: 35kB Peak Memory: 35kB
Pre-sorted Groups: 6000 Sort Method: quicksort Average Memory: 89kB Peak Memory: 89kB
-> Merge Left Join (cost=292061.31..330151.31 rows=2400000 width=176) (actual time=820.735..1077.264 rows=2400000 loops=1)
Merge Cond: (cv.course_version_id = e.course_version_id)
-> Sort (cost=2523.56..2568.56 rows=18000 width=91) (actual time=230.300..231.048 rows=18000 loops=1)
Sort Key: cv.course_version_id
Sort Method: quicksort Memory: 2692kB
-> Hash Join (cost=253.07..1251.35 rows=18000 width=91) (actual time=224.575..228.017 rows=18000 loops=1)
Hash Cond: (c.course_id = cv.course_id)
-> Hash Join (cost=73.07..853.85 rows=6000 width=86) (actual time=223.945..226.495 rows=6000 loops=1)
Hash Cond: (ct.language_id = l.id)
-> Hash Join (cost=72.00..814.78 rows=6000 width=94) (actual time=0.288..2.497 rows=6000 loops=1)
Hash Cond: (ct.course_id = c.course_id)
-> Seq Scan on course_translate ct (cost=0.00..727.00 rows=6000 width=77) (actual time=0.027..1.526 rows=6000 loops=1)
-> Hash (cost=47.00..47.00 rows=2000 width=17) (actual time=0.251..0.251 rows=2000 loops=1)
Buckets: 2048 Batches: 1 Memory Usage: 112kB
-> Seq Scan on course c (cost=0.00..47.00 rows=2000 width=17) (actual time=0.011..0.154 rows=2000 loops=1)
-> Hash (cost=1.03..1.03 rows=3 width=8) (actual time=223.648..223.649 rows=3 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on language l (cost=0.00..1.03 rows=3 width=8) (actual time=223.634..223.638 rows=3 loops=1)
-> Hash (cost=105.00..105.00 rows=6000 width=21) (actual time=0.625..0.625 rows=6000 loops=1)
Buckets: 8192 Batches: 1 Memory Usage: 393kB
-> Seq Scan on course_version cv (cost=0.00..105.00 rows=6000 width=21) (actual time=0.008..0.305 rows=6000 loops=1)
-> Materialize (cost=289537.75..293537.75 rows=800000 width=93) (actual time=590.408..704.748 rows=2399998 loops=1)
-> Sort (cost=289537.75..291537.75 rows=800000 width=93) (actual time=590.404..631.143 rows=800000 loops=1)
Sort Key: e.course_version_id
Sort Method: external merge Disk: 63912kB
-> Merge Left Join (cost=1.70..129066.19 rows=800000 width=93) (actual time=0.074..427.971 rows=800000 loops=1)
Merge Cond: (e.enrollment_id = ucp.enrollment_id)
-> Merge Left Join (cost=1.27..77075.86 rows=800000 width=61) (actual time=0.037..255.216 rows=800000 loops=1)
Merge Cond: (e.enrollment_id = r.enrollment_id)
-> Merge Left Join (cost=0.85..66770.86 rows=800000 width=49) (actual time=0.025..192.972 rows=800000 loops=1)
Merge Cond: (e.enrollment_id = p.enrollment_id)
-> Index Scan using enrollment_pkey on enrollment e (cost=0.42..27318.42 rows=800000 width=32) (actual time=0.013..55.297 rows=800000 loops=1)
-> Index Scan using uq_payment_enrollment on payment p (cost=0.42..27454.42 rows=800000 width=25) (actual time=0.008..55.497 rows=800000 loops=1)
-> Index Scan using uq_review_enrollment on review r (cost=0.42..6305.42 rows=160000 width=20) (actual time=0.008..12.381 rows=160000 loops=1)
-> GroupAggregate (cost=0.42..42782.96 rows=320327 width=40) (actual time=0.034..122.050 rows=320000 loops=1)
Group Key: ucp.enrollment_id
-> Index Only Scan using idx_ucp_enrollment_completed on user_course_progress ucp (cost=0.42..29176.42 rows=960000 width=9) (actual time=0.028..46.170 rows=960000 loops=1)
Heap Fetches: 0
Planning Time: 1.702 ms
JIT:
Functions: 56
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 1.158 ms (Deform 0.626 ms), Inlining 32.279 ms, Optimization 102.678 ms, Emission 88.707 ms, Total 224.822 ms
Execution Time: 2862.106 ms
Време: 2846,2 ms (три пуштања: 2832,2 / 2844,2 / 2862,1 ms), односно маргинално забрзување од 4,3%.
Ова е најдобриот резултат од сите индекси во оваа анализа. Причината е видлива во планот, скапиот scan врз 960.000 редови се промени од:
Index Scan using uq_ucp on user_course_progress ucp\n (actual time=0.014..120.943 rows=960000 loops=1)
во:
Index Only Scan using idx_ucp_enrollment_completed on user_course_progress ucp\n (actual time=0.028..46.170 rows=960000 loops=1)
Тој јазол стана 2,6 пати побрз, од 120,9 ms на 46,2 ms, бидејќи is_completed сега се чита директно од индексот и воопшто не се пристапува до heap-от.
Проверка со work_mem
Со work_mem = 256 MB сортирањето од 63 MB престанува да се прелева на диск.
Sort Method: quicksort Memory: 95827kB\nExecution Time: 3117.918 ms
Резултатот е побавен, 3117,9 ms наспроти 2974,0 ms, односно околу 5% позагубено. Причината е што quicksort врз околу 96 MB во меморија е поскап од external merge sort, кој е оптимизиран за големи количини и чии привремени датотеки и онака се во page cache. Ова е добар потсетник дека поголем work_mem не значи автоматски побрзо.
Колку чини враќањето на сите јазици
Функцијата намерно враќа резултат за сите три јазика. Вреди сепак да се измери колку чини таа одлука. Со додаден филтер за еден јазик:
JOIN language l ON l.id = ct.language_id AND l.value = 'en'
и со истите два индекси од чекор 1 и 2, времето паѓа на 1666,2 ms (три пуштања: 1663,9 / 1669,7 / 1665,1 ms), а резултатот има 6.000 наместо 18.000 реда.
| Варијанта | Редови | Време |
| сите јазици, тековна | 18.000 | 2846,2 ms |
само en | 6.000 | 1666,2 ms |
Тоа е 1,71 пати побрзо, односно 41% помалку време, што е многукратно повеќе од сето што донесоа индексите заедно.
Ова не е предлог да се смени функцијата, бидејќи враќањето на сите јазици е намерна одлука и филтрирањето би го променило резултатот. Наведено е само за да се види каде реално оди времето, бидејќи три пати повеќе редови значат приблизно двојно повеќе време. Ако некогаш се појави потреба од извештај само за еден јазик, најефтино е јазикот да биде параметар на функцијата, наместо да се филтрира дополнително врз готовиот резултат.
Заклучок
Индексот помогна, но само 4,3%, и тоа е поучно само по себе. Covering индексот врз user_course_progress направи тој дел од планот да биде 2,6 пати побрз, но вкупното query забрза само за 4,3%. Ова е директна илустрација на Амдаловиот закон, бидејќи ако забрзаш дел што зафаќа само неколку проценти од времето, крајната добивка не може да надмине тие неколку проценти.
Заштедените околу 75 ms се реални, но останатите околу 2850 ms се трошат на Incremental Sort врз 2,4 милиони редови, кој е најскапиот дел со околу 1530 ms, на веригата merge join-ови врз 800.000 редови, на сортирањето по course_version_id што се прелева на диск, и на GroupAggregate со COUNT(DISTINCT e.user_id).
Индексот врз course_version_id од чекор 1 не помогна воопшто, затоа што планерот има подобра алтернатива. Сите четири табели во веригата, enrollment, payment, review и user_course_progress, се спојуваат по enrollment_id, а по тој столб веќе постојат unique индекси. Планерот затоа чита сè подредено по enrollment_id со merge join-ови, што е поевтино од тоа да влезе преку course_version_id и потоа да прави случајни пристапи. Поуката е дека индекс се користи само ако планерот процени дека е поевтин од алтернативата, па создавањето индекс не значи дека тој ќе биде употребен.
Вториот заклучок, од мерењето со филтер по јазик, е дека бројот на редови што влегуваат во агрегацијата е далеку поважен од индексите. Трите јазика ја прават функцијата 1,71 пати побавна, додека индексите донесоа 4,3%.
dashboard_expert_performance()
Збирна изведба по експерт. Резултатот има 50.000 реда, по еден за секој експерт.
Дефиниција
-- Expert performance summary
CREATE OR REPLACE FUNCTION dashboard_expert_performance()
RETURNS TABLE (
expert_id INTEGER,
expert_name TEXT,
courses_created BIGINT,
total_enrollments BIGINT,
paid_enrollments BIGINT,
trial_enrollments BIGINT,
total_revenue NUMERIC,
avg_rating NUMERIC,
total_reviews BIGINT
) AS $$
#variable_conflict use_column
BEGIN
RETURN QUERY
SELECT
ex.expert_id::INTEGER AS expert_id,
a.name::TEXT AS expert_name,
COUNT(DISTINCT c.course_id)::BIGINT AS courses_created,
COUNT(DISTINCT e.enrollment_id)::BIGINT AS total_enrollments,
COUNT(DISTINCT CASE WHEN p.payment_status = 'completed' THEN e.enrollment_id END)::BIGINT AS paid_enrollments,
COUNT(DISTINCT CASE WHEN p.payment_id IS NULL THEN e.enrollment_id END)::BIGINT AS trial_enrollments,
COALESCE(SUM(CASE WHEN p.payment_status = 'completed' THEN p.amount ELSE 0 END), 0)::NUMERIC AS total_revenue,
COALESCE(AVG(r.rating), 0)::NUMERIC AS avg_rating,
COUNT(r.review_id)::BIGINT AS total_reviews
FROM expert ex
JOIN account a ON a.id = ex.account_id -- expert name lives on account
LEFT JOIN expert_course ec ON ex.expert_id = ec.expert_id -- experts with no courses should be included
LEFT JOIN course c ON ec.course_id = c.course_id -- experts with no courses should be included
LEFT JOIN course_version cv ON c.course_id = cv.course_id -- left join so experts without courses are not dropped
LEFT JOIN enrollment e ON cv.course_version_id = e.course_version_id -- left join to include courses with zero enrollments
LEFT JOIN payment p ON e.enrollment_id = p.enrollment_id -- left join to include enrollments without payments
LEFT JOIN review r ON e.enrollment_id = r.enrollment_id -- left join to include enrollments without reviews
GROUP BY ex.expert_id, a.id
ORDER BY total_revenue DESC NULLS LAST;
END;
$$ LANGUAGE plpgsql;
Почетна состојба
Sort (cost=9416816.80..9466816.80 rows=20000000 width=156) (actual time=1620.638..1623.502 rows=50000 loops=1)
Sort Key: (COALESCE(sum(CASE WHEN (p.payment_status = 'completed'::payment_status) THEN p.amount ELSE '0'::numeric END), '0'::numeric)) DESC NULLS LAST
Sort Method: external merge Disk: 5216kB
-> GroupAggregate (cost=436066.06..2274667.63 rows=20000000 width=156) (actual time=841.856..1610.629 rows=50000 loops=1)
Group Key: ex.expert_id, a.id
-> Incremental Sort (cost=436066.06..1374667.63 rows=20000000 width=82) (actual time=841.225..1328.382 rows=1913735 loops=1)
Sort Key: ex.expert_id, a.id, c.course_id
Presorted Key: ex.expert_id, a.id
Full-sort Groups: 4073 Sort Method: quicksort Average Memory: 31kB Peak Memory: 31kB
Pre-sorted Groups: 4986 Sort Method: quicksort Average Memory: 217kB Peak Memory: 218kB
-> Merge Left Join (cost=436066.05..474667.63 rows=20000000 width=82) (actual time=840.824..1123.142 rows=1913735 loops=1)
Merge Cond: (ex.expert_id = ec.expert_id)
-> Gather Merge (cost=13663.64..19486.97 rows=50000 width=37) (actual time=179.596..182.605 rows=50000 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=12663.62..12715.70 rows=20833 width=37) (actual time=118.464..118.848 rows=16667 loops=3)
Sort Key: ex.expert_id, a.id
Sort Method: quicksort Memory: 25kB
Worker 0: Sort Method: quicksort Memory: 2117kB
Worker 1: Sort Method: quicksort Memory: 2154kB
-> Hash Join (cost=1396.00..11169.21 rows=20833 width=37) (actual time=114.640..117.284 rows=16667 loops=3)
Hash Cond: (a.id = ex.account_id)
-> Parallel Seq Scan on account a (cost=0.00..9226.33 rows=208333 width=29) (actual time=0.015..8.180 rows=166667 loops=3)
-> Hash (cost=771.00..771.00 rows=50000 width=16) (actual time=98.396..98.396 rows=50000 loops=3)
Buckets: 65536 Batches: 1 Memory Usage: 2856kB
-> Seq Scan on expert ex (cost=0.00..771.00 rows=50000 width=16) (actual time=0.006..1.642 rows=50000 loops=3)
-> Materialize (cost=422393.66..431725.66 rows=1866400 width=53) (actual time=661.199..836.447 rows=1866401 loops=1)
-> Sort (cost=422393.66..427059.66 rows=1866400 width=53) (actual time=661.196..742.643 rows=1866401 loops=1)
Sort Key: ec.expert_id
Sort Method: external merge Disk: 107416kB
-> Hash Right Join (cost=666.96..100402.06 rows=1866400 width=53) (actual time=3.098..377.129 rows=1866401 loops=1)
Hash Cond: (e.course_version_id = cv.course_version_id)
-> Merge Left Join (cost=1.75..77072.85 rows=800000 width=45) (actual time=0.040..266.632 rows=800000 loops=1)
Merge Cond: (e.enrollment_id = r.enrollment_id)
-> Merge Left Join (cost=1.33..66768.43 rows=800000 width=33) (actual time=0.028..203.477 rows=800000 loops=1)
Merge Cond: (e.enrollment_id = p.enrollment_id)
-> Index Scan using enrollment_pkey on enrollment e (cost=0.42..27318.42 rows=800000 width=16) (actual time=0.014..59.040 rows=800000 loops=1)
-> Index Scan using uq_payment_enrollment on payment p (cost=0.42..27454.42 rows=800000 width=25) (actual time=0.009..58.879 rows=800000 loops=1)
-> Index Scan using uq_review_enrollment on review r (cost=0.42..6305.42 rows=160000 width=20) (actual time=0.008..13.251 rows=160000 loops=1)
-> Hash (cost=490.24..490.24 rows=13998 width=24) (actual time=3.017..3.019 rows=13998 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 894kB
-> Hash Right Join (cost=215.26..490.24 rows=13998 width=24) (actual time=1.085..2.207 rows=13998 loops=1)
Hash Cond: (cv.course_id = c.course_id)
-> Seq Scan on course_version cv (cost=0.00..105.00 rows=6000 width=16) (actual time=0.008..0.297 rows=6000 loops=1)
-> Hash (cost=156.93..156.93 rows=4666 width=16) (actual time=1.057..1.058 rows=4666 loops=1)
Buckets: 8192 Batches: 1 Memory Usage: 283kB
-> Hash Left Join (cost=72.00..156.93 rows=4666 width=16) (actual time=0.259..0.779 rows=4666 loops=1)
Hash Cond: (ec.course_id = c.course_id)
-> Seq Scan on expert_course ec (cost=0.00..72.66 rows=4666 width=16) (actual time=0.010..0.199 rows=4666 loops=1)
-> Hash (cost=47.00..47.00 rows=2000 width=8) (actual time=0.244..0.244 rows=2000 loops=1)
Buckets: 2048 Batches: 1 Memory Usage: 95kB
-> Seq Scan on course c (cost=0.00..47.00 rows=2000 width=8) (actual time=0.013..0.139 rows=2000 loops=1)
Planning Time: 1.235 ms
JIT:
Functions: 78
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 1.617 ms (Deform 0.862 ms), Inlining 102.392 ms, Optimization 98.491 ms, Emission 82.286 ms, Total 284.785 ms
Execution Time: 1637.668 ms
Време: 1677,6 ms (три пуштања: 1722,1 / 1673,0 / 1637,7 ms).
Од планот, сортирањето по ec.expert_id се прелева на диск со external merge Disk: 107416kB, што е 107 MB и најголемото прелевање во целата анализа, и трае околу 661 ms. Merge Left Join враќа 1.913.735 реда, иако на крај се враќаат само 50.000. Parallel Seq Scan on account чита сите 500.000 сметки за да ги најде 50.000-те што припаѓаат на експерти. Освен тоа, expert_course.course_id нема индекс, бидејќи pk_expert_course е (expert_id, course_id) и покрива само expert_id.
Идеите се индекси врз непокриените foreign key-ови, enrollment.course_version_id и expert_course.course_id, како и covering индекси врз payment и review за Index Only Scan во веригата join-ови.
Чекор 1, индекси врз непокриените foreign key-ови
CREATE INDEX idx_enrollment_course_version_id ON enrollment(course_version_id);\nCREATE INDEX idx_expert_course_course_id ON expert_course(course_id);
Sort (cost=9416821.36..9466821.36 rows=20000000 width=156) (actual time=1605.269..1608.025 rows=50000 loops=1)
Sort Key: (COALESCE(sum(CASE WHEN (p.payment_status = 'completed'::payment_status) THEN p.amount ELSE '0'::numeric END), '0'::numeric)) DESC NULLS LAST
Sort Method: external merge Disk: 5216kB
-> GroupAggregate (cost=436070.63..2274672.19 rows=20000000 width=156) (actual time=830.945..1594.989 rows=50000 loops=1)
Group Key: ex.expert_id, a.id
-> Incremental Sort (cost=436070.63..1374672.19 rows=20000000 width=82) (actual time=830.323..1315.718 rows=1913735 loops=1)
Sort Key: ex.expert_id, a.id, c.course_id
Presorted Key: ex.expert_id, a.id
Full-sort Groups: 4073 Sort Method: quicksort Average Memory: 31kB Peak Memory: 31kB
Pre-sorted Groups: 4986 Sort Method: quicksort Average Memory: 217kB Peak Memory: 218kB
-> Merge Left Join (cost=436070.61..474672.19 rows=20000000 width=82) (actual time=829.891..1113.685 rows=1913735 loops=1)
Merge Cond: (ex.expert_id = ec.expert_id)
-> Gather Merge (cost=13663.64..19486.97 rows=50000 width=37) (actual time=174.600..177.658 rows=50000 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=12663.62..12715.70 rows=20833 width=37) (actual time=114.172..114.547 rows=16667 loops=3)
Sort Key: ex.expert_id, a.id
Sort Method: quicksort Memory: 25kB
Worker 0: Sort Method: quicksort Memory: 2150kB
Worker 1: Sort Method: quicksort Memory: 2121kB
-> Hash Join (cost=1396.00..11169.21 rows=20833 width=37) (actual time=111.012..113.206 rows=16667 loops=3)
Hash Cond: (a.id = ex.account_id)
-> Parallel Seq Scan on account a (cost=0.00..9226.33 rows=208333 width=29) (actual time=0.017..8.058 rows=166667 loops=3)
-> Hash (cost=771.00..771.00 rows=50000 width=16) (actual time=94.795..94.795 rows=50000 loops=3)
Buckets: 65536 Batches: 1 Memory Usage: 2856kB
-> Seq Scan on expert ex (cost=0.00..771.00 rows=50000 width=16) (actual time=0.006..1.703 rows=50000 loops=3)
-> Materialize (cost=422398.23..431730.23 rows=1866400 width=53) (actual time=655.263..831.308 rows=1866401 loops=1)
-> Sort (cost=422398.23..427064.23 rows=1866400 width=53) (actual time=655.260..738.876 rows=1866401 loops=1)
Sort Key: ec.expert_id
Sort Method: external merge Disk: 107416kB
-> Hash Right Join (cost=667.77..100406.62 rows=1866400 width=53) (actual time=2.857..370.398 rows=1866401 loops=1)
Hash Cond: (e.course_version_id = cv.course_version_id)
-> Merge Left Join (cost=2.56..77077.41 rows=800000 width=45) (actual time=0.039..261.857 rows=800000 loops=1)
Merge Cond: (e.enrollment_id = r.enrollment_id)
-> Merge Left Join (cost=2.14..66772.11 rows=800000 width=33) (actual time=0.028..199.757 rows=800000 loops=1)
Merge Cond: (e.enrollment_id = p.enrollment_id)
-> Index Scan using enrollment_pkey on enrollment e (cost=0.42..27318.42 rows=800000 width=16) (actual time=0.015..57.657 rows=800000 loops=1)
-> Index Scan using uq_payment_enrollment on payment p (cost=0.42..27454.42 rows=800000 width=25) (actual time=0.009..57.687 rows=800000 loops=1)
-> Index Scan using uq_review_enrollment on review r (cost=0.42..6305.42 rows=160000 width=20) (actual time=0.008..12.992 rows=160000 loops=1)
-> Hash (cost=490.24..490.24 rows=13998 width=24) (actual time=2.793..2.795 rows=13998 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 894kB
-> Hash Right Join (cost=215.26..490.24 rows=13998 width=24) (actual time=0.996..2.052 rows=13998 loops=1)
Hash Cond: (cv.course_id = c.course_id)
-> Seq Scan on course_version cv (cost=0.00..105.00 rows=6000 width=16) (actual time=0.007..0.296 rows=6000 loops=1)
-> Hash (cost=156.93..156.93 rows=4666 width=16) (actual time=0.976..0.977 rows=4666 loops=1)
Buckets: 8192 Batches: 1 Memory Usage: 283kB
-> Hash Left Join (cost=72.00..156.93 rows=4666 width=16) (actual time=0.232..0.744 rows=4666 loops=1)
Hash Cond: (ec.course_id = c.course_id)
-> Seq Scan on expert_course ec (cost=0.00..72.66 rows=4666 width=16) (actual time=0.009..0.193 rows=4666 loops=1)
-> Hash (cost=47.00..47.00 rows=2000 width=8) (actual time=0.219..0.220 rows=2000 loops=1)
Buckets: 2048 Batches: 1 Memory Usage: 95kB
-> Seq Scan on course c (cost=0.00..47.00 rows=2000 width=8) (actual time=0.009..0.132 rows=2000 loops=1)
Planning Time: 1.373 ms
JIT:
Functions: 78
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 1.284 ms (Deform 0.701 ms), Inlining 94.140 ms, Optimization 97.362 ms, Emission 81.477 ms, Total 274.263 ms
Execution Time: 1621.652 ms
Време: 1669,3 ms (три пуштања: 1666,7 / 1719,6 / 1621,7 ms), односно без ефект (+0,5%, во границите на мерната грешка).
external merge Disk: 107416kB останува непроменето.
Чекор 2, covering индекси врз payment и review
CREATE INDEX idx_payment_cov_all\n ON payment(enrollment_id) INCLUDE (amount, payment_status, payment_id);\n\nCREATE INDEX idx_review_cov\n ON review(enrollment_id) INCLUDE (rating, review_id);
Sort (cost=9416093.11..9466093.11 rows=20000000 width=156) (actual time=1597.646..1600.382 rows=50000 loops=1)
Sort Key: (COALESCE(sum(CASE WHEN (p.payment_status = 'completed'::payment_status) THEN p.amount ELSE '0'::numeric END), '0'::numeric)) DESC NULLS LAST
Sort Method: external merge Disk: 5216kB
-> GroupAggregate (cost=435342.38..2273943.94 rows=20000000 width=156) (actual time=822.516..1588.476 rows=50000 loops=1)
Group Key: ex.expert_id, a.id
-> Incremental Sort (cost=435342.38..1373943.94 rows=20000000 width=82) (actual time=821.886..1309.364 rows=1913735 loops=1)
Sort Key: ex.expert_id, a.id, c.course_id
Presorted Key: ex.expert_id, a.id
Full-sort Groups: 4073 Sort Method: quicksort Average Memory: 31kB Peak Memory: 31kB
Pre-sorted Groups: 4986 Sort Method: quicksort Average Memory: 217kB Peak Memory: 218kB
-> Merge Left Join (cost=435342.36..473943.94 rows=20000000 width=82) (actual time=821.466..1102.978 rows=1913735 loops=1)
Merge Cond: (ex.expert_id = ec.expert_id)
-> Gather Merge (cost=13663.64..19486.97 rows=50000 width=37) (actual time=166.177..169.143 rows=50000 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=12663.62..12715.70 rows=20833 width=37) (actual time=112.352..112.718 rows=16667 loops=3)
Sort Key: ex.expert_id, a.id
Sort Method: quicksort Memory: 25kB
Worker 0: Sort Method: quicksort Memory: 2133kB
Worker 1: Sort Method: quicksort Memory: 2138kB
-> Hash Join (cost=1396.00..11169.21 rows=20833 width=37) (actual time=109.228..111.420 rows=16667 loops=3)
Hash Cond: (a.id = ex.account_id)
-> Parallel Seq Scan on account a (cost=0.00..9226.33 rows=208333 width=29) (actual time=0.017..8.065 rows=166667 loops=3)
-> Hash (cost=771.00..771.00 rows=50000 width=16) (actual time=93.237..93.237 rows=50000 loops=3)
Buckets: 65536 Batches: 1 Memory Usage: 2856kB
-> Seq Scan on expert ex (cost=0.00..771.00 rows=50000 width=16) (actual time=0.006..1.664 rows=50000 loops=3)
-> Materialize (cost=421669.98..431001.98 rows=1866400 width=53) (actual time=655.259..830.777 rows=1866401 loops=1)
-> Sort (cost=421669.98..426335.98 rows=1866400 width=53) (actual time=655.256..738.073 rows=1866401 loops=1)
Sort Key: ec.expert_id
Sort Method: external merge Disk: 107416kB
-> Hash Right Join (cost=667.96..99678.37 rows=1866400 width=53) (actual time=3.019..372.200 rows=1866401 loops=1)
Hash Cond: (e.course_version_id = cv.course_version_id)
-> Merge Left Join (cost=2.75..76349.16 rows=800000 width=45) (actual time=0.117..259.226 rows=800000 loops=1)
Merge Cond: (e.enrollment_id = r.enrollment_id)
-> Merge Left Join (cost=2.10..66772.85 rows=800000 width=33) (actual time=0.103..204.048 rows=800000 loops=1)
Merge Cond: (e.enrollment_id = p.enrollment_id)
-> Index Scan using enrollment_pkey on enrollment e (cost=0.42..27318.42 rows=800000 width=16) (actual time=0.017..60.059 rows=800000 loops=1)
-> Index Scan using uq_payment_enrollment on payment p (cost=0.42..27454.42 rows=800000 width=25) (actual time=0.073..60.156 rows=800000 loops=1)
-> Index Only Scan using idx_review_cov on review r (cost=0.42..5576.42 rows=160000 width=20) (actual time=0.009..8.833 rows=160000 loops=1)
Heap Fetches: 0
-> Hash (cost=490.24..490.24 rows=13998 width=24) (actual time=2.872..2.874 rows=13998 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 894kB
-> Hash Right Join (cost=215.26..490.24 rows=13998 width=24) (actual time=0.992..2.084 rows=13998 loops=1)
Hash Cond: (cv.course_id = c.course_id)
-> Seq Scan on course_version cv (cost=0.00..105.00 rows=6000 width=16) (actual time=0.009..0.314 rows=6000 loops=1)
-> Hash (cost=156.93..156.93 rows=4666 width=16) (actual time=0.969..0.970 rows=4666 loops=1)
Buckets: 8192 Batches: 1 Memory Usage: 283kB
-> Hash Left Join (cost=72.00..156.93 rows=4666 width=16) (actual time=0.240..0.742 rows=4666 loops=1)
Hash Cond: (ec.course_id = c.course_id)
-> Seq Scan on expert_course ec (cost=0.00..72.66 rows=4666 width=16) (actual time=0.015..0.202 rows=4666 loops=1)
-> Hash (cost=47.00..47.00 rows=2000 width=8) (actual time=0.220..0.220 rows=2000 loops=1)
Buckets: 2048 Batches: 1 Memory Usage: 95kB
-> Seq Scan on course c (cost=0.00..47.00 rows=2000 width=8) (actual time=0.012..0.133 rows=2000 loops=1)
Planning Time: 1.353 ms
JIT:
Functions: 76
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 1.455 ms (Deform 0.796 ms), Inlining 95.801 ms, Optimization 92.443 ms, Emission 80.145 ms, Total 269.843 ms
Execution Time: 1613.605 ms
Време: 1605,8 ms (три пуштања: 1606,0 / 1597,8 / 1613,6 ms), односно маргинално забрзување од 4,3%.
review премина на Index Only Scan, од 13,3 ms на 8,8 ms на тој јазол, но payment остана на обичен Index Scan, бидејќи планерот процени дека covering индексот од 38 MB не се исплати наспроти веќе постоечкиот uq_payment_enrollment.
Заклучок
Индексите донесоа 4,3%, бидејќи проблемот не е во пристапот до податоците. Најскапиот дел од планот е сортирањето на 1,86 милиони редови по ec.expert_id, со 107 MB прелевање на диск. Тоа сортирање се прави врз резултат од join, не врз базна табела, а индекс може да даде подреденост само на базна табела. Затоа не постои индекс што би го отстранил.
Вистинскиот проблем е структурен. Query-то прави join од expert сè до enrollment, payment и review, при што бројот на редови расте на речиси 2 милиони, а потоа со COUNT(DISTINCT ...) се собира назад на 50.000. Со други зборови, се обработуваат околу 38 пати повеќе редови отколку што има во резултатот.
Индексите не можат да го поправат тоа. Она што би можело е пред-агрегирање на enrollment и payment по course_version_id во подquery пред join-от со expert, така што множењето на редови никогаш не се случува, но тоа е надвор од опсегот на оваа анализа.
Општ заклучок
Збирни резултати:
| Функција | Почетна | Со индекси | Добивка |
dashboard_monthly_totals() | 153,8 ms | 161,7 ms | -5,1% |
dashboard_monthly_courses() | 3434,4 ms | 3360,9 ms | +2,1% |
dashboard_course_performance() | 2974,0 ms | 2846,2 ms | +4,3% |
dashboard_expert_performance() | 1677,6 ms | 1605,8 ms | +4,3% |
Добивката се движи од −5% до +4%, што за ниту една од четирите функции не претставува значајно подобрување. Ова не е неуспех на експериментот, туку очекуван резултат, и причините се четири.
Првата е што индексот служи за да се прескокнат редови, а овие query-а не прескокнуваат ништо. Сите четири функции се извештаи што агрегираат врз целата база и немаат WHERE услов што ограничува на период, курс или корисник. Кога на query-то му требаат сите 800.000 редови, Seq Scan е оптималниот начин да се прочитаат, бидејќи чита цели блокови по редослед без индиректност. Кај dashboard_monthly_totals() присилното користење на индекси е дури 1,8 пати побавно од секвенцијалното читање. Индексите помагаат драматично доколку функциите примаа параметри, што не е претпоставка туку измерено: со филтер по период, 70.152 од 800.000 реда, и индекс врз enrollment(purchase_date), функција 1 станува 4,0 пати побрза, од 153,8 ms на 38,1 ms, и планерот го користи индексот што претходно го игнорираше.
Втората е што најскапите операции се сортирања врз резултат од join, а не пристап до податоци. Кај функциите 2, 3 и 4 доминантниот трошок е Sort или Incremental Sort, до 76% од времето кај dashboard_monthly_courses(). Тие сортирања се прават врз меѓурезултат од join, чиј клуч содржи столбови од повеќе табели и пресметани изрази. Индексот дава подреденост само во рамките на една базна табела, па таквото сортирање принципиелно не може да се елиминира со индекс.
Третата е што најкорисните индекси веќе постојат. Сите join-ови во овие функции одат по enrollment_id, course_id и course_version_id, а тие се primary key-ови или unique constraint-и, што значи дека индексите веќе се создадени автоматски. Токму затоа новите индекси немаа што да придонесат, бидејќи работата што тие би ја вршеле веќе се вршеше.
Четвртата е Амдаловиот закон. Единствениот индекс со мерлив ефект е idx_ucp_enrollment_completed кај dashboard_course_performance(). Тој го направи scan-от врз user_course_progress 2,6 пати побрз, од 120,9 на 46,2 ms, но бидејќи тој scan зафаќаше само околу 4% од вкупното време, крајната добивка е 4,3%. Забрзување на мал дел од работата дава мала вкупна добивка, колку и да е импресивен факторот на тој дел.
Индексите не се бесплатни, бидејќи заземаат простор и го забавуваат секое INSERT, UPDATE и DELETE:
| Индекс | Големина | Табела | Однос |
idx_payment_cov_all | 38 MB | 52 MB | 73% |
idx_ucp_enrollment_completed | 29 MB | 60 MB | 48% |
idx_review_cov | 6,4 MB | 17 MB | 37% |
idx_enrollment_course_version_id | 5,4 MB | 51 MB | 11% |
idx_expert_course_course_id | 104 kB | — | — |
idx_payment_cov_all зафаќа 73% од големината на самата табела, а донесе околу 2%. Тоа е лоша размена, бидејќи секое ново плаќање ќе мора да го одржува и тој индекс.
Од сите тестирани индекси, вредни за задржување се само два:
-- Мерлива добивка кај dashboard_course_performance()\nCREATE INDEX idx_ucp_enrollment_completed\n ON user_course_progress(enrollment_id) INCLUDE (is_completed);\n\n-- Не помогна кај извештаите, но е единствениот непокриен foreign key.\n-- Вреди заради DELETE/UPDATE врз course_version и обични OLTP барања.\nCREATE INDEX idx_enrollment_course_version_id\n ON enrollment(course_version_id);
Останатите, idx_payment_cov_all, idx_review_cov, idx_payment_completed_covering, idx_enrollment_purchase_date_cov и idx_enrollment_cv_user, не се препорачуваат, бидејќи цената во простор и во забавени записи е поголема од добивката од 1 до 2%.
Мерењата покажаа дека надвор од индексите постојат значително поголеми добивки:
| Интервенција | Ефект | Забелешка |
| Јазик како параметар на функција 2 | 3,3 пати побрзо | менува резултат, сите јазици се намерна одлука |
| Јазик како параметар на функција 3 | 1,7 пати побрзо | исто така менува резултат |
| Параметар за период кај функција 1 | 4,0 пати побрзо | измерено, ги прави индексите корисни |
| Пред-агрегирање пред join кај функција 4 | потенцијално голем | го спречува множењето на редови |
work_mem 4 MB на 256 MB | 0% до −5% | не помогна, кај функција 3 дури штети |
jit = off | околу ±3% | без доследен ефект |
Најголемиот поединечен фактор во целата анализа не е индекс, туку бројот на редови што влегуваат во агрегацијата. Кај функциите 2 и 3 тој број е тројно поголем затоа што се враќаат сите три јазика, што е намерна одлука но со мерлива цена, бидејќи функција 2 е 3,3 пати побавна а функција 3 е 1,7 пати побавна од варијантите со еден јазик. Ако некогаш затреба извештај за еден јазик, најдобро е тоа да се направи преку параметар во функцијата, за филтерот да делува пред агрегацијата, бидејќи дополнително филтрирање врз готовиот резултат не носи никаква добивка кога целата работа веќе е завршена.
Индексите се вредна алатка кога query-то бара мал дел од голема табела. Овие четири функции бараат сè, па индексот нема што да прескокне. Пред да се додаваат индекси, EXPLAIN ANALYZE треба да покаже дека времето навистина се троши на пристап до податоци, а овде тоа се троши на сортирање и агрегирање на редови што query-то само ги умножило.
Безбедност и заштита
JWT Token Authorization (Spring Security)
JWT (JSON Web Token) e stateless начин на автентикација - server НЕ чува информации за активни сесии во база, туку сите потребни податоци се во самиот token кој корисникот го чува локално/cookie. JWT содржи енкодирана json структура на информации (user_id, email, role, expiry...).
Java код во Spring Boot:
@Bean
public SecurityFilterChain filterChain(HttpSecurity http) throws Exception {
http
.csrf(AbstractHttpConfigurer::disable)
.cors(Customizer.withDefaults())
.sessionManagement(session -> session.sessionCreationPolicy(SessionCreationPolicy.STATELESS))
.authorizeHttpRequests(request -> request
.requestMatchers("/api/verification-tokens/**").permitAll()
.requestMatchers("/api/auth/**").permitAll()
.requestMatchers("/api/courses/**").permitAll()
.requestMatchers("/api/test/**").permitAll()
.requestMatchers("/api/auth/oauth2/**").permitAll()
.requestMatchers("/oauth2/**", "/login/oauth2/**").permitAll()
.anyRequest().authenticated()
)
.exceptionHandling(exception -> exception
.authenticationEntryPoint(authenticationEntryPoint)
)
.oauth2Login(oauth2 -> oauth2
// Use the custom handler instead of defaultSuccessUrl()
.successHandler(oauth2SuccessHandler)
.failureUrl("http://localhost:5173/login?error")
)
.authenticationProvider(authenticationProvider)
.addFilterBefore(jwtAuthFilter, UsernamePasswordAuthenticationFilter.class);
return http.build();
}
Хеширање на пасворди (BCrypt)
Пасвордите на корисниците и експертите се чуваат во база во хеширана форма преку BCrypt, а не како plain text. Ова овозможува сигурно чување на пасвордите.
SQL Injection Prevention (Spring JPA/JPQL)
Преку JPA/JPQL се спречува SQL Injection напад каде корисникот внесува злонамерен код за да манипулира со базата на податоци. Нападите се избегнуваат преку третирање на параметарот како plain data, а не команда.
Безбедно:
@Query("select u.email from User u where u.id = :userId")
String getUserEmailById(@Param("userId") Long userId);
Небезбедно:
String query = "SELECT * FROM users WHERE email = '" + email + "'";
CORS Configuration
CORS е безбеден механизам за заштита од requests од различни домени. Со тоа се заштитуваме од можни злонамерни requests.
Java код во Spring Boot:
@Bean
public CorsConfigurationSource corsConfigurationSource() {
CorsConfiguration config = new CorsConfiguration();
config.addAllowedOriginPattern("http://localhost:*");
config.addAllowedHeader("*");
config.addAllowedMethod("*");
config.setAllowCredentials(true);
config.addExposedHeader(HttpHeaders.CONTENT_DISPOSITION);
UrlBasedCorsConfigurationSource source = new UrlBasedCorsConfigurationSource();
source.registerCorsConfiguration("/**", config);
return source;
}
