| Version 29 (modified by , 13 days ago) ( diff ) |
|---|
Индекси и оптимизација на прашалници (QueryOptimization)
Во оваа фаза се анализирани перформансите на погледите од фаза 2Б, по потреба се преуредени прашалниците и се додадени индекси. Погледите се нумерирани како на DatabaseCreation; опфатени се погледите 3–14 (1 и 2 се помошни, а 15 и 16 се додадени по оваа фаза).
Мерењата се направени врз целото податочно множество со EXPLAIN (ANALYZE, BUFFERS); секој прашалник е извршен три пати, а наведено е просечното време од второто и третото извршување, кога податоците се веќе во баферите. „Без индексите“ е базата само со индексите од шемата (примарни клучеви и уникатни ограничувања), „со индексите“ е базата и со двата индекса од оваа фаза. Без филтер планот е ист во двата случаја и разликите во времето се варијација меѓу извршувањата.
Репрезентативни вредности за филтрите:
vendor_id = 4242: 47 договори, 276 проектиclient_id = 4242: 7 договори, 52 проектиindustry_id = 7: 968 клиентиproject_id = 142160
Содржина
- Индекси
- Поглед 3: Проекти по софтверска агенција (vw_projects_per_vendor)
- Поглед 4: Проекти по клиент (vw_projects_per_client)
- Поглед 5: Вкупен буџет по софтверска агенција (vw_budget_per_vendor)
- Поглед 6: Вкупен буџет по клиент (vw_budget_per_client)
- Поглед 7: Клиенти по индустрија (vw_clients_per_industry)
- Поглед 8: Просечна оценка по софтверска агенција (vw_avg_rating_per_vendor)
- Поглед 9: Нерешени спорови (vw_unresolved_dispute_tickets)
- Поглед 10: Број на проекти по статус по софтверска агенција (vw_project_count_by_status_per_vendor)
- Поглед 11: Број на проекти по статус по клиент (vw_project_count_by_status_per_client)
- Поглед 12: Договори по софтверска агенција (vw_contracts_per_vendor)
- Поглед 13: Договори по клиент (vw_contracts_per_client)
- Поглед 14: Претплати на софтверски агенции (vw_vendor_subscriptions)
Индекси
CREATE INDEX IF NOT EXISTS idx_cvc_vendor_id ON Client_Vendor_Contract (vendor_id); CREATE INDEX IF NOT EXISTS idx_cvc_client_id ON Client_Vendor_Contract (client_id);
| Индекс | Големина | Намена |
|---|---|---|
idx_cvc_vendor_id | 1,7 MB | погледите филтрирани по софтверска агенција (3, 5, 8, 10, 12) |
idx_cvc_client_id | 2,1 MB | погледите филтрирани по клиент (4, 6, 11, 13) |
Секој поглед филтриран по агенција или по клиент почнува со договорите на таа агенција или на тој клиент. Без индекс тоа е sequential scan на целата табела Client_Vendor_Contract (250.000 редици, 5.652 страници) за да се задржат 47, односно 12 редици; со индексот се читаат само тие редици. Одлуката е според планот, а не според милисекундите: план што чита цела табела за неколку десетици редици е погрешен независно од големината на табелата, а индексот чини 2 MB. Останатите чекори на тие погледи (проектите на договорот, статусот, двете страни) веќе одат преку индексите од шемата: примарните клучеви и uq_project_contract_name, чија прва колона е contract_id.
Цена при запишување
По 10.000 редици INSERT и UPDATE во Client_Vendor_Contract, единствената табела со индекс од оваа фаза, во трансакција што се поништува; UPDATE менува неиндексирана колона (наслов на договор).
| Наредба (10.000 редици) | Без индексите | Со индексите |
|---|---|---|
INSERT во Client_Vendor_Contract | 225 ms | 240 ms |
UPDATE на Client_Vendor_Contract | 285 ms | 321 ms |
Индексите го поскапуваат запишувањето за 7 до 13 %; најголемиот дел од времето отпаѓа на проверките на надворешните клучеви и тригерите.
Поглед 3: Проекти по софтверска агенција (vw_projects_per_vendor)
1. Примарен филтер за погледот vw_projects_per_vendor ќе биде според vendor_id на софтверската агенција.
2. Примарен случај на употреба ќе биде преглед на сите проекти на одредена софтверска агенција, на нејзината контролна табла и на профилот што клиентот го разгледува. Перформансите на овој поглед се важни, бидејќи се чита при секое отворање на профилот.
3. Иницијална состојба: најбавната операција е sequential scan на табелата Client_Vendor_Contract (250.000 редици, 5.652 страници), паралелен, со отфрлање на 249.953 редици; проектите на секој договор се читаат преку уникатниот индекс uq_project_contract_name (contract_id, project_name) од шемата.
Sort (actual time=11.781..14.928 rows=276 loops=1)
Sort Key: v.agency_name, ps.status_name, p.project_name
-> Nested Loop (actual time=0.912..14.484 rows=276 loops=1)
-> Index Scan using vendor_pkey on vendor v (actual time=0.010..0.011 rows=1 loops=1)
Index Cond: (vendor_id = 4242)
-> Gather (actual time=0.900..14.436 rows=276 loops=1)
Workers Launched: 2
-> Hash Join (actual time=0.451..8.896 rows=92 loops=3)
Hash Cond: (p.status_id = ps.status_id)
-> Nested Loop (actual time=0.364..8.790 rows=92 loops=3)
-> Nested Loop (actual time=0.338..8.549 rows=16 loops=3)
-> Parallel Seq Scan on client_vendor_contract cvc (actual time=0.321..8.451 rows=16 loops=3)
Filter: (vendor_id = 4242)
Rows Removed by Filter: 83318
-> Index Only Scan using client_pkey on client c (actual time=0.005..0.005 rows=1 loops=47)
Index Cond: (client_id = cvc.client_id)
-> Index Scan using uq_project_contract_name on project p (actual time=0.013..0.014 rows=6 loops=47)
Index Cond: (contract_id = cvc.contract_id)
-> Hash (actual time=0.019..0.019 rows=6 loops=3)
[...]
Execution Time: 14.978 ms
4. Индексирање: idx_cvc_vendor_id за договорите на агенцијата. Проектите на секој договор и понатаму се читаат преку uq_project_contract_name, уникатниот индекс од шемата чија прва колона е contract_id.
Sort (actual time=0.851..0.862 rows=276 loops=1)
Sort Key: v.agency_name, ps.status_name, p.project_name
-> Nested Loop (actual time=0.042..0.421 rows=276 loops=1)
-> Nested Loop (actual time=0.038..0.333 rows=276 loops=1)
-> Nested Loop (actual time=0.031..0.139 rows=47 loops=1)
-> Index Scan using vendor_pkey on vendor v (actual time=0.008..0.009 rows=1 loops=1)
Index Cond: (vendor_id = 4242)
-> Nested Loop (actual time=0.022..0.125 rows=47 loops=1)
-> Bitmap Heap Scan on client_vendor_contract cvc (actual time=0.016..0.051 rows=47 loops=1)
Recheck Cond: (vendor_id = 4242)
-> Bitmap Index Scan on idx_cvc_vendor_id (actual time=0.008..0.008 rows=47 loops=1)
Index Cond: (vendor_id = 4242)
-> Index Only Scan using client_pkey on client c (actual time=0.001..0.001 rows=1 loops=47)
Index Cond: (client_id = cvc.client_id)
-> Index Scan using uq_project_contract_name on project p (actual time=0.003..0.003 rows=6 loops=47)
Index Cond: (contract_id = cvc.contract_id)
-> Memoize (actual time=0.000..0.000 rows=1 loops=276)
[...]
Execution Time: 0.892 ms
5. Време на извршување:
| Читање | Без индексите | Со индексите | Забрзување |
|---|---|---|---|
| филтрирано по софтверска агенција (276 редици) | 15,7 ms | 0,85 ms | 18 пати |
| нефилтрирано (1.400.000 редици) | 2,28 s | 2,40 s | ист план |
6. Времето на извршување на операциите insert и update врз табелите на погледот е во табелата „Цена при запишување“ на почетокот на страницата.
Поглед 4: Проекти по клиент (vw_projects_per_client)
1. Примарен филтер за погледот vw_projects_per_client ќе биде според client_id на клиентот.
2. Примарен случај на употреба ќе биде преглед на сите проекти на одреден клиент, на неговата контролна табла.
3. Иницијална состојба: најбавната операција е sequential scan на табелата Client_Vendor_Contract со филтер по client_id, со отфрлање на 249.993 редици; проектите се читаат преку uq_project_contract_name.
Gather Merge (actual time=11.490..14.589 rows=52 loops=1)
Workers Launched: 2
-> Sort (actual time=8.920..8.923 rows=17 loops=3)
Sort Key: c.company_name, ps.status_name, p.project_name
-> Hash Join (actual time=2.665..8.868 rows=17 loops=3)
Hash Cond: (p.status_id = ps.status_id)
-> Nested Loop (actual time=2.587..8.785 rows=17 loops=3)
-> Nested Loop (actual time=2.543..8.713 rows=2 loops=3)
-> Nested Loop (actual time=2.526..8.689 rows=2 loops=3)
-> Parallel Seq Scan on client_vendor_contract cvc (actual time=2.506..8.661 rows=2 loops=3)
Filter: (client_id = 4242)
Rows Removed by Filter: 83331
-> Index Scan using client_pkey on client c (actual time=0.010..0.010 rows=1 loops=7)
Index Cond: (client_id = 4242)
-> Index Only Scan using vendor_pkey on vendor v (actual time=0.009..0.009 rows=1 loops=7)
Index Cond: (vendor_id = cvc.vendor_id)
-> Index Scan using uq_project_contract_name on project p (actual time=0.026..0.028 rows=7 loops=7)
Index Cond: (contract_id = cvc.contract_id)
-> Hash (actual time=0.014..0.014 rows=6 loops=3)
[...]
Execution Time: 14.625 ms
4. Индексирање: idx_cvc_client_id за договорите на клиентот; проектите се читаат преку uq_project_contract_name. Колоната client_id има 20.000 различни вредности, просечно 12,5 договори по клиент, па е поселективна од vendor_id (5.000 вредности, 50 договори по агенција).
Sort (actual time=0.154..0.157 rows=52 loops=1)
Sort Key: c.company_name, ps.status_name, p.project_name
-> Nested Loop (actual time=0.038..0.096 rows=52 loops=1)
-> Nested Loop (actual time=0.032..0.070 rows=52 loops=1)
-> Nested Loop (actual time=0.024..0.038 rows=7 loops=1)
-> Nested Loop (actual time=0.017..0.023 rows=7 loops=1)
-> Index Scan using client_pkey on client c (actual time=0.006..0.007 rows=1 loops=1)
Index Cond: (client_id = 4242)
-> Bitmap Heap Scan on client_vendor_contract cvc (actual time=0.009..0.013 rows=7 loops=1)
Recheck Cond: (client_id = 4242)
-> Bitmap Index Scan on idx_cvc_client_id (actual time=0.005..0.005 rows=7 loops=1)
Index Cond: (client_id = 4242)
-> Index Only Scan using vendor_pkey on vendor v (actual time=0.002..0.002 rows=1 loops=7)
Index Cond: (vendor_id = cvc.vendor_id)
-> Index Scan using uq_project_contract_name on project p (actual time=0.003..0.004 rows=7 loops=7)
Index Cond: (contract_id = cvc.contract_id)
-> Memoize (actual time=0.000..0.000 rows=1 loops=52)
[...]
Execution Time: 0.182 ms
5. Време на извршување:
| Читање | Без индексите | Со индексите | Забрзување |
|---|---|---|---|
| филтрирано по клиент (52 редици) | 14,5 ms | 0,17 ms | 86 пати |
| нефилтрирано (1.400.000 редици) | 2,15 s | 2,23 s | ист план |
6. Времето на извршување на операциите insert и update врз табелите на погледот е во табелата „Цена при запишување“ на почетокот на страницата.
Поглед 5: Вкупен буџет по софтверска агенција (vw_budget_per_vendor)
1. Примарен филтер за погледот vw_budget_per_vendor ќе биде според vendor_id на софтверската агенција.
2. Примарен случај на употреба ќе биде преглед на вкупниот буџет на проектите на агенцијата, по валута. Погледот е аналитички (SUM, COUNT, GROUP BY).
3. Иницијална состојба: филтрирано, најбавната операција е sequential scan на Client_Vendor_Contract; нефилтрирано, планот е паралелен hash join на sequential scan на Project и Client_Vendor_Contract со агрегација (HashAggregate), кај кој индексите не помагаат.
Sort (actual time=11.086..14.206 rows=6 loops=1)
Sort Key: v.agency_name, cvc.currency_code
-> GroupAggregate (actual time=10.951..14.198 rows=6 loops=1)
Group Key: cvc.currency_code
-> Nested Loop (actual time=10.935..14.157 rows=276 loops=1)
-> Gather Merge (actual time=10.911..14.071 rows=276 loops=1)
Workers Launched: 2
-> Sort (actual time=8.099..8.104 rows=92 loops=3)
Sort Key: cvc.currency_code
-> Hash Join (actual time=0.938..8.045 rows=92 loops=3)
Hash Cond: (p.status_id = ps.status_id)
-> Nested Loop (actual time=0.837..7.923 rows=92 loops=3)
-> Nested Loop (actual time=0.810..7.718 rows=16 loops=3)
-> Parallel Seq Scan on client_vendor_contract cvc (actual time=0.790..7.622 rows=16 loops=3)
Filter: (vendor_id = 4242)
Rows Removed by Filter: 83318
-> Index Only Scan using client_pkey on client c (actual time=0.005..0.005 rows=1 loops=47)
Index Cond: (client_id = cvc.client_id)
-> Index Scan using uq_project_contract_name on project p (actual time=0.011..0.012 rows=6 loops=47)
Index Cond: (contract_id = cvc.contract_id)
-> Hash (actual time=0.018..0.018 rows=6 loops=3)
[...]
-> Materialize (actual time=0.000..0.000 rows=1 loops=276)
[...]
Execution Time: 14.262 ms
4. Индексирање: idx_cvc_vendor_id; агрегацијата по валута потоа работи врз 276 редици.
Sort (actual time=0.548..0.549 rows=6 loops=1)
Sort Key: v.agency_name, cvc.currency_code
-> GroupAggregate (actual time=0.486..0.545 rows=6 loops=1)
Group Key: cvc.currency_code
-> Sort (actual time=0.480..0.490 rows=276 loops=1)
Sort Key: cvc.currency_code
-> Nested Loop (actual time=0.032..0.407 rows=276 loops=1)
-> Nested Loop (actual time=0.026..0.321 rows=276 loops=1)
-> Nested Loop (actual time=0.021..0.129 rows=47 loops=1)
-> Index Scan using vendor_pkey on vendor v (actual time=0.005..0.006 rows=1 loops=1)
Index Cond: (vendor_id = 4242)
-> Nested Loop (actual time=0.015..0.118 rows=47 loops=1)
-> Bitmap Heap Scan on client_vendor_contract cvc (actual time=0.010..0.047 rows=47 loops=1)
Recheck Cond: (vendor_id = 4242)
-> Bitmap Index Scan on idx_cvc_vendor_id (actual time=0.004..0.004 rows=47 loops=1)
Index Cond: (vendor_id = 4242)
-> Index Only Scan using client_pkey on client c (actual time=0.001..0.001 rows=1 loops=47)
Index Cond: (client_id = cvc.client_id)
-> Index Scan using uq_project_contract_name on project p (actual time=0.003..0.003 rows=6 loops=47)
Index Cond: (contract_id = cvc.contract_id)
-> Memoize (actual time=0.000..0.000 rows=1 loops=276)
[...]
Execution Time: 0.574 ms
5. Време на извршување:
| Читање | Без индексите | Со индексите | Забрзување |
|---|---|---|---|
| филтрирано по софтверска агенција (6 редици) | 14,3 ms | 0,52 ms | 27 пати |
| нефилтрирано (28.738 редици) | 605 ms | 608 ms | ист план |
6. Времето на извршување на операциите insert и update врз табелите на погледот е во табелата „Цена при запишување“ на почетокот на страницата.
Поглед 6: Вкупен буџет по клиент (vw_budget_per_client)
1. Примарен филтер за погледот vw_budget_per_client ќе биде според client_id на клиентот.
2. Примарен случај на употреба ќе биде преглед на вкупниот буџет на клиентот низ сите проекти и договори, по валута. Погледот е аналитички (SUM, COUNT, GROUP BY).
3. Иницијална состојба: филтрирано, најбавната операција е sequential scan на Client_Vendor_Contract; нефилтрирано, планот е hash join на sequential scan на Project и Client_Vendor_Contract со агрегација (HashAggregate), кај кој индексите не помагаат.
Sort (actual time=12.035..15.191 rows=3 loops=1)
Sort Key: c.company_name, cvc.currency_code
-> GroupAggregate (actual time=11.979..15.182 rows=3 loops=1)
Group Key: cvc.currency_code
-> Nested Loop (actual time=11.960..15.155 rows=52 loops=1)
-> Gather Merge (actual time=11.929..15.097 rows=52 loops=1)
Workers Launched: 2
-> Sort (actual time=9.209..9.212 rows=17 loops=3)
Sort Key: cvc.currency_code
-> Hash Join (actual time=2.709..9.165 rows=17 loops=3)
Hash Cond: (p.status_id = ps.status_id)
-> Nested Loop (actual time=2.616..9.067 rows=17 loops=3)
-> Nested Loop (actual time=2.591..9.009 rows=2 loops=3)
-> Parallel Seq Scan on client_vendor_contract cvc (actual time=2.569..8.978 rows=2 loops=3)
Filter: (client_id = 4242)
Rows Removed by Filter: 83331
-> Index Only Scan using vendor_pkey on vendor v (actual time=0.011..0.011 rows=1 loops=7)
Index Cond: (vendor_id = cvc.vendor_id)
-> Index Scan using uq_project_contract_name on project p (actual time=0.021..0.022 rows=7 loops=7)
Index Cond: (contract_id = cvc.contract_id)
-> Hash (actual time=0.019..0.019 rows=6 loops=3)
[...]
-> Materialize (actual time=0.001..0.001 rows=1 loops=52)
[...]
Execution Time: 15.248 ms
Без филтер (ист план со и без индексите):
Sort (actual time=1664.838..1669.234 rows=81810 loops=1)
Sort Key: c.company_name, cvc.currency_code
-> HashAggregate (actual time=1491.535..1523.486 rows=81810 loops=1)
Group Key: cvc.currency_code, c.client_id
-> Hash Join (actual time=70.462..1108.790 rows=1400000 loops=1)
Hash Cond: (p.status_id = ps.status_id)
-> Hash Join (actual time=70.445..927.241 rows=1400000 loops=1)
Hash Cond: (cvc.vendor_id = v.vendor_id)
-> Hash Join (actual time=69.670..726.839 rows=1400000 loops=1)
Hash Cond: (cvc.client_id = c.client_id)
-> Hash Join (actual time=66.014..486.544 rows=1400000 loops=1)
Hash Cond: (p.contract_id = cvc.contract_id)
-> Seq Scan on project p (actual time=0.005..92.911 rows=1400000 loops=1)
-> Hash (actual time=65.901..65.903 rows=250000 loops=1)
-> Seq Scan on client_vendor_contract cvc (actual time=0.004..32.272 rows=250000 loops=1)
-> Hash (actual time=3.641..3.642 rows=20000 loops=1)
[...]
-> Hash (actual time=0.770..0.770 rows=5000 loops=1)
[...]
-> Hash (actual time=0.011..0.012 rows=6 loops=1)
[...]
Execution Time: 1672.542 ms
4. Индексирање: idx_cvc_client_id; агрегацијата по валута потоа работи врз 52 редици.
Sort (actual time=0.134..0.136 rows=3 loops=1)
Sort Key: c.company_name, cvc.currency_code
-> GroupAggregate (actual time=0.120..0.129 rows=3 loops=1)
Group Key: cvc.currency_code
-> Sort (actual time=0.114..0.117 rows=52 loops=1)
Sort Key: cvc.currency_code
-> Nested Loop (actual time=0.040..0.103 rows=52 loops=1)
-> Nested Loop (actual time=0.033..0.079 rows=52 loops=1)
-> Nested Loop (actual time=0.025..0.041 rows=7 loops=1)
-> Nested Loop (actual time=0.020..0.027 rows=7 loops=1)
-> Index Scan using client_pkey on client c (actual time=0.007..0.008 rows=1 loops=1)
Index Cond: (client_id = 4242)
-> Bitmap Heap Scan on client_vendor_contract cvc (actual time=0.011..0.017 rows=7 loops=1)
Recheck Cond: (client_id = 4242)
-> Bitmap Index Scan on idx_cvc_client_id (actual time=0.007..0.007 rows=7 loops=1)
Index Cond: (client_id = 4242)
-> Index Only Scan using vendor_pkey on vendor v (actual time=0.002..0.002 rows=1 loops=7)
Index Cond: (vendor_id = cvc.vendor_id)
-> Index Scan using uq_project_contract_name on project p (actual time=0.003..0.004 rows=7 loops=7)
Index Cond: (contract_id = cvc.contract_id)
-> Memoize (actual time=0.000..0.000 rows=1 loops=52)
[...]
Execution Time: 0.162 ms
5. Време на извршување:
| Читање | Без индексите | Со индексите | Забрзување |
|---|---|---|---|
| филтрирано по клиент (3 редици) | 16,1 ms | 0,14 ms | 114 пати |
| нефилтрирано (81.810 редици) | 1,68 s | 1,63 s | ист план |
6. Времето на извршување на операциите insert и update врз табелите на погледот е во табелата „Цена при запишување“ на почетокот на страницата.
Поглед 7: Клиенти по индустрија (vw_clients_per_industry)
1. Примарен филтер за погледот vw_clients_per_industry ќе биде според industry_id на индустријата.
2. Примарен случај на употреба ќе биде преглед на сите клиенти од одредена индустрија.
3. Иницијална состојба: главната операција е sequential scan на табелата Client (209 страници, 1,7 MB), по што следат поврзувањето со Industry и сортирањето; времето е прифатливо за апликацијата.
Sort (actual time=2.515..2.549 rows=968 loops=1)
Sort Key: i.industry_name, c.company_name
-> Nested Loop (actual time=0.013..1.203 rows=968 loops=1)
-> Seq Scan on industry i (actual time=0.005..0.006 rows=1 loops=1)
Filter: (industry_id = 7)
Rows Removed by Filter: 19
-> Seq Scan on client c (actual time=0.007..1.095 rows=968 loops=1)
Filter: (industry_id = 7)
Rows Removed by Filter: 19032
Execution Time: 2.599 ms
4. Тестиран е индекс idx_client_industry_id ON Client (industry_id); 968-те клиенти на една индустрија (5 % од табелата) се распоредени низ речиси сите страници, па и со индексот речиси сите страници пак се читаат; добивката е мала и индексот не е креиран. Нема потреба да се преуреди прашалникот.
5. Време на извршување:
| Читање | Без индекс | Со тестираниот idx_client_industry_id | Забрзување |
|---|---|---|---|
| филтрирано по индустрија (968 редици) | 2,65 ms | 1,75 ms | 1,5 пати |
| нефилтрирано (20.000 редици) | 39,1 ms | не е мерено |
6. Времето на извршување на операциите insert и update останува исто; врз табелата Client нема индекси од оваа фаза.
Поглед 8: Просечна оценка по софтверска агенција (vw_avg_rating_per_vendor)
1. Примарен филтер за погледот vw_avg_rating_per_vendor ќе биде според vendor_id на софтверската агенција; за јавната ранг-листа се чита и без филтер.
2. Примарен случај на употреба ќе биде приказ на просечната оценка на агенцијата на нејзиниот профил и ранг-листата на сите агенции. Овој поглед е аналитички (агрегира 10.000.000 оценки), па за нефилтрираното читање индексите не помагаат.
3. Иницијална состојба: најбавните операции се сортирањето на 1.000.000 парови (агенција, рецензија), кое COUNT(DISTINCT r.review_id) го бара пред агрегирањето, и читањето на сите 10.000.000 оценки од Review_Score по рецензија, вклучително и оценките на необјавените рецензии. Првобитната дефиниција:
CREATE OR REPLACE VIEW vw_avg_rating_per_vendor AS
SELECT v.vendor_id,
v.agency_name,
COUNT(DISTINCT r.review_id) AS review_count, -- DISTINCT бара сортиран влез
ROUND(AVG(rs.score_value), 2) AS avg_rating -- сите оценки, и на необјавени рецензии
FROM Review_Score rs
JOIN Review r ON r.review_id = rs.review_id
JOIN Project p ON p.project_id = r.project_id
JOIN Client_Vendor_Contract cvc ON cvc.contract_id = p.contract_id
JOIN Vendor v ON v.vendor_id = cvc.vendor_id
GROUP BY v.vendor_id, v.agency_name
ORDER BY avg_rating DESC NULLS LAST;
Sort (actual time=5017.736..5025.135 rows=5000 loops=1)
Sort Key: (round(avg(rs.score_value), 2)) DESC NULLS LAST
-> GroupAggregate (actual time=762.816..5021.663 rows=5000 loops=1)
Group Key: v.vendor_id
-> Nested Loop (actual time=761.859..4450.418 rows=10000000 loops=1)
-> Gather Merge (actual time=761.803..936.599 rows=1000000 loops=1)
Workers Launched: 2
-> Sort (actual time=742.829..774.390 rows=333333 loops=3)
Sort Key: v.vendor_id, r.review_id
-> Hash Join (actual time=364.168..632.521 rows=333333 loops=3)
Hash Cond: (cvc.vendor_id = v.vendor_id)
[...]
-> Index Scan using review_score_pkey on review_score rs (actual time=0.002..0.003 rows=10 loops=1000000)
Index Cond: (review_id = r.review_id)
Execution Time: 5028.229 ms
4. Преуредување на прашалникот, со промена на резултатот: се бројат само објавени рецензии (659.458 од 1.000.000), а LEFT JOIN од Vendor ги задржува и агенциите без рецензија. Оценките прво се собираат по рецензија (score_sum, score_cnt), а просекот на агенцијата е SUM(score_sum) / SUM(score_cnt), што е истиот просек како AVG врз сите оценки, но без COUNT(DISTINCT), па двете агрегации се хеширани. Филтрирано по агенција, оптимизаторот го спушта условот во потпрашалникот и преку idx_cvc_vendor_id ги чита само нејзините рецензии; без филтер Review_Score се чита целосно во двата случаја.
CREATE OR REPLACE VIEW vw_avg_rating_per_vendor AS
SELECT v.vendor_id,
v.agency_name,
COUNT(vr.review_id) AS review_count,
-- просек на сите оценки на агенцијата; NULLIF штити од делење со нула
ROUND(SUM(vr.score_sum) / NULLIF(SUM(vr.score_cnt), 0), 2) AS avg_rating
FROM Vendor v
-- LEFT JOIN: агенција без објавена рецензија останува со review_count = 0
-- потпрашалник: збир и број на оценки по рецензија, само објавени рецензии
LEFT JOIN (SELECT pd.vendor_id,
r.review_id,
SUM(rs.score_value)::numeric AS score_sum,
COUNT(*) AS score_cnt
FROM Review r
JOIN Review_Score rs ON rs.review_id = r.review_id
JOIN vw_project_details pd ON pd.project_id = r.project_id
WHERE r.is_published = true
GROUP BY pd.vendor_id, r.review_id) vr ON vr.vendor_id = v.vendor_id
GROUP BY v.vendor_id, v.agency_name
ORDER BY avg_rating DESC NULLS LAST;
Без филтер:
Sort (actual time=4183.548..4183.741 rows=5000 loops=1)
Sort Key: (round((sum(((sum(rs.score_value))::numeric)) / NULLIF(sum((count(*))), '0'::numeric)), 2)) DESC NULLS LAST
-> HashAggregate (actual time=4179.979..4181.793 rows=5000 loops=1)
Group Key: v.vendor_id
-> Hash Right Join (actual time=3831.864..4079.611 rows=659458 loops=1)
Hash Cond: (v_1.vendor_id = v.vendor_id)
-> HashAggregate (actual time=3617.949..3787.674 rows=659458 loops=1)
Group Key: v_1.vendor_id, r.review_id
-> Hash Join (actual time=1209.193..2820.624 rows=6594580 loops=1)
Hash Cond: (rs.review_id = r.review_id)
-> Seq Scan on review_score rs (actual time=0.041..458.448 rows=10000000 loops=1)
-> Hash (actual time=1205.593..1205.600 rows=659458 loops=1)
[...]
-> Hash (actual time=213.903..213.903 rows=5000 loops=1)
[...]
Execution Time: 4219.610 ms
Филтрирано по софтверска агенција, со индексите:
Sort (actual time=1.615..1.617 rows=1 loops=1)
Sort Key: (round((sum(((sum(rs.score_value))::numeric)) / NULLIF(sum((count(*))), '0'::numeric)), 2)) DESC NULLS LAST
-> GroupAggregate (actual time=1.611..1.613 rows=1 loops=1)
-> Nested Loop Left Join (actual time=1.569..1.600 rows=123 loops=1)
-> Index Scan using vendor_pkey on vendor v (actual time=0.006..0.007 rows=1 loops=1)
Index Cond: (vendor_id = 4242)
-> HashAggregate (actual time=1.562..1.584 rows=123 loops=1)
Group Key: r.review_id
-> Nested Loop (actual time=0.080..1.420 rows=1230 loops=1)
-> Nested Loop (actual time=0.075..0.881 rows=123 loops=1)
Join Filter: (ps.status_id = p.status_id)
Rows Removed by Join Filter: 369
-> Nested Loop (actual time=0.066..0.814 rows=123 loops=1)
-> Nested Loop (actual time=0.033..0.385 rows=276 loops=1)
-> Nested Loop (actual time=0.025..0.155 rows=47 loops=1)
[...]
-> Nested Loop (actual time=0.021..0.146 rows=47 loops=1)
-> Bitmap Heap Scan on client_vendor_contract cvc (actual time=0.015..0.062 rows=47 loops=1)
Recheck Cond: (vendor_id = 4242)
-> Bitmap Index Scan on idx_cvc_vendor_id (actual time=0.005..0.005 rows=47 loops=1)
Index Cond: (vendor_id = 4242)
-> Index Only Scan using client_pkey on client c (actual time=0.001..0.001 rows=1 loops=47)
Index Cond: (client_id = cvc.client_id)
-> Index Scan using uq_project_contract_name on project p (actual time=0.003..0.004 rows=6 loops=47)
Index Cond: (contract_id = cvc.contract_id)
-> Index Scan using review_project_id_key on review r (actual time=0.001..0.001 rows=0 loops=276)
Index Cond: (project_id = p.project_id)
Filter: is_published
Rows Removed by Filter: 0
-> Materialize (actual time=0.000..0.000 rows=4 loops=123)
-> Seq Scan on project_status ps (actual time=0.005..0.006 rows=4 loops=1)
-> Index Scan using review_score_pkey on review_score rs (actual time=0.002..0.003 rows=10 loops=123)
Index Cond: (review_id = r.review_id)
Execution Time: 1.671 ms
5. Време на извршување:
Првобитната и новата дефиниција не даваат ист резултат; табелата споредува два различни прашалници.
| Читање | Првобитна, без индексите | Првобитна, со индексите | Нова, без индексите | Нова, со индексите |
|---|---|---|---|---|
| филтрирано по софтверска агенција (1 редица) | 16,2 ms | 2,06 ms | 14,0 ms | 1,90 ms |
| нефилтрирано (5.000 редици) | 4,97 s | 5,11 s | 4,19 s | 4,35 s |
6. Времето на извршување на операциите insert и update врз табелите на погледот е во табелата „Цена при запишување“ на почетокот на страницата.
Поглед 9: Нерешени спорови (vw_unresolved_dispute_tickets)
1. Примарен филтер за погледот vw_unresolved_dispute_tickets ќе биде според project_id на проектот или vendor_id на софтверската агенција; менаџментот го чита и без филтер, како ред на задачи.
2. Примарен случај на употреба ќе биде преглед на отворените спорови за еден проект или една агенција и редот на нерешени спорови за менаџментот. Перформансите на овој поглед се важни, бидејќи се чита при секоја работа со спорови.
3. Иницијална состојба: најбавната операција не е sequential scan, туку функцијата fn_get_full_name(), повикана двапати по редица за имињата на поднесувачот и на доделениот менаџмент корисник: 58.002 извршени повици, секој посебен прашалник врз "User" (вториот повик добива NULL за недоделен спор, а функцијата е STRICT и тогаш не се извршува). Повикот на функција во листата на колони го спречува и паралелното извршување. Првобитната дефиниција:
CREATE OR REPLACE VIEW vw_unresolved_dispute_tickets AS
SELECT dt.ticket_id,
dt.filed_at,
dt.reason,
r.review_id,
p.project_id,
p.project_name,
vu.user_id AS filed_by_vendor_user_id,
fn_get_full_name(vu.user_id) AS filed_by_vendor_user, -- повик по редица
mu.user_id AS assigned_management_user_id,
fn_get_full_name(mu.user_id) AS assigned_to_management_user, -- повик по редица
dt.created_at,
dt.updated_at
FROM Dispute_Ticket dt
JOIN Review r ON r.review_id = dt.review_id
JOIN Project p ON p.project_id = r.project_id
JOIN Vendor_User vu ON vu.user_id = dt.vendor_user_id
LEFT JOIN Management_User mu ON mu.user_id = dt.assigned_management_user_id
WHERE dt.is_resolved = false
ORDER BY dt.filed_at;
Sort (actual time=781.820..788.679 rows=58002 loops=1)
Sort Key: dt.filed_at
-> Hash Left Join (actual time=38.322..751.524 rows=58002 loops=1)
Hash Cond: (dt.assigned_management_user_id = mu.user_id)
-> Hash Join (actual time=37.851..471.846 rows=58002 loops=1)
Hash Cond: (dt.vendor_user_id = vu.user_id)
-> Nested Loop (actual time=24.480..434.236 rows=58002 loops=1)
-> Hash Join (actual time=24.459..318.522 rows=58002 loops=1)
Hash Cond: (r.review_id = dt.review_id)
-> Seq Scan on review r (actual time=0.009..66.118 rows=1000000 loops=1)
-> Hash (actual time=24.391..24.392 rows=58002 loops=1)
[...]
-> Index Scan using project_pkey on project p (actual time=0.002..0.002 rows=1 loops=58002)
Index Cond: (project_id = r.project_id)
-> Hash (actual time=13.353..13.354 rows=25000 loops=1)
[...]
-> Hash (actual time=0.318..0.318 rows=2000 loops=1)
[...]
Execution Time: 792.358 ms
4. Преуредување на прашалникот: имињата се добиваат со две поврзувања со "User" наместо со повици на функција, поврзувањето со Management_User е отстрането (надворешниот клуч гарантира дека доделениот корисник е менаџмент корисник), а додадени се колоните vendor_id и agency_name за филтрирање по агенција. Планот без филтер е паралелен; филтрирано по проект се чита преку уникатниот индекс review_project_id_key и делумниот уникатен индекс uq_dispute_open_per_vendor_review од шемата. Дополнителен индекс не го подобрува планот на овој поглед: условот is_resolved = false избира 40 % од табелата, па индекс врз таа колона не би се користел.
CREATE VIEW vw_unresolved_dispute_tickets AS
SELECT dt.ticket_id,
dt.filed_at,
dt.reason,
r.review_id,
p.project_id,
p.project_name,
dt.vendor_user_id AS filed_by_vendor_user_id,
-- имињата на поднесувачот (vusr) и на доделениот корисник (musr) од две
-- поврзувања со "User"; musr е NULL додека спорот не е доделен (LEFT JOIN)
vusr.first_name || ' ' || vusr.last_name AS filed_by_vendor_user,
dt.assigned_management_user_id,
musr.first_name || ' ' || musr.last_name AS assigned_to_management_user,
dt.created_at,
dt.updated_at,
ven.vendor_id,
ven.agency_name
FROM Dispute_Ticket dt
JOIN Review r ON r.review_id = dt.review_id
JOIN Project p ON p.project_id = r.project_id
JOIN Vendor_User vu ON vu.user_id = dt.vendor_user_id
JOIN Vendor ven ON ven.vendor_id = vu.vendor_id
JOIN "User" vusr ON vusr.user_id = vu.user_id
LEFT JOIN "User" musr ON musr.user_id = dt.assigned_management_user_id
WHERE dt.is_resolved = false
ORDER BY dt.filed_at;
Без филтер:
Gather Merge (actual time=194.937..215.581 rows=58002 loops=1)
Workers Launched: 2
-> Sort (actual time=191.770..193.702 rows=19334 loops=3)
Sort Key: dt.filed_at
-> Nested Loop Left Join (actual time=37.737..182.784 rows=19334 loops=3)
-> Nested Loop (actual time=37.661..174.551 rows=19334 loops=3)
[...]
-> Index Scan using project_pkey on project p (actual time=0.002..0.002 rows=1 loops=58002)
Index Cond: (project_id = r.project_id)
-> Memoize (actual time=0.000..0.000 rows=0 loops=58002)
[...]
Execution Time: 217.633 ms
Филтрирано по проект:
Sort (actual time=0.034..0.035 rows=1 loops=1)
Sort Key: dt.filed_at
-> Nested Loop Left Join (actual time=0.030..0.032 rows=1 loops=1)
-> Nested Loop (actual time=0.027..0.030 rows=1 loops=1)
-> Nested Loop (actual time=0.025..0.027 rows=1 loops=1)
-> Nested Loop (actual time=0.022..0.023 rows=1 loops=1)
-> Nested Loop (actual time=0.018..0.019 rows=1 loops=1)
-> Nested Loop (actual time=0.013..0.014 rows=1 loops=1)
-> Index Scan using review_project_id_key on review r (actual time=0.008..0.008 rows=1 loops=1)
Index Cond: (project_id = 142160)
-> Index Scan using uq_dispute_open_per_vendor_review on dispute_ticket dt (actual time=0.004..0.004 rows=1 loops=1)
Index Cond: (review_id = r.review_id)
-> Index Scan using project_pkey on project p (actual time=0.004..0.005 rows=1 loops=1)
Index Cond: (project_id = 142160)
-> Index Scan using "User_pkey" on "User" vusr (actual time=0.003..0.003 rows=1 loops=1)
Index Cond: (user_id = dt.vendor_user_id)
-> Index Scan using vendor_user_pkey on vendor_user vu (actual time=0.003..0.003 rows=1 loops=1)
Index Cond: (user_id = dt.vendor_user_id)
-> Index Scan using vendor_pkey on vendor ven (actual time=0.002..0.002 rows=1 loops=1)
Index Cond: (vendor_id = vu.vendor_id)
-> Index Scan using "User_pkey" on "User" musr (actual time=0.000..0.000 rows=0 loops=1)
Index Cond: (user_id = dt.assigned_management_user_id)
Execution Time: 0.062 ms
5. Време на извршување:
| Читање | Првобитна дефиниција | Нова дефиниција | Забрзување |
|---|---|---|---|
| филтрирано по проект (1 редица) | 0,10 ms | 0,06 ms | 1,8 пати |
| нефилтрирано (58.002 редици) | 689 ms | 210 ms | 3,3 пати |
6. Времето на извршување на операциите insert и update врз табелата Dispute_Ticket е во табелата „Цена при запишување“ на почетокот на страницата.
Поглед 10: Број на проекти по статус по софтверска агенција (vw_project_count_by_status_per_vendor)
1. Примарен филтер за погледот vw_project_count_by_status_per_vendor ќе биде според vendor_id на софтверската агенција.
2. Примарен случај на употреба ќе биде преглед на бројот на проекти на агенцијата по статус, на нејзината контролна табла. Погледот е аналитички (COUNT, GROUP BY).
3. Иницијална состојба: филтрирано, најбавната операција е sequential scan на Client_Vendor_Contract; нефилтрирано, планот е hash join на sequential scan на табелите со агрегација (HashAggregate), кај кој индексите не помагаат.
Sort (actual time=11.804..15.019 rows=6 loops=1)
Sort Key: v.agency_name, ps.status_name
-> GroupAggregate (actual time=11.645..15.008 rows=6 loops=1)
Group Key: ps.status_name, p.status_id
-> Incremental Sort (actual time=11.571..14.973 rows=276 loops=1)
Sort Key: ps.status_name, p.status_id
-> Nested Loop (actual time=0.954..14.889 rows=276 loops=1)
Join Filter: (ps.status_id = p.status_id)
Rows Removed by Join Filter: 1380
-> Index Scan using project_status_status_name_key on project_status ps (actual time=0.006..0.009 rows=6 loops=1)
-> Materialize (actual time=0.158..2.461 rows=276 loops=6)
-> Nested Loop (actual time=0.943..14.647 rows=276 loops=1)
-> Index Scan using vendor_pkey on vendor v (actual time=0.010..0.012 rows=1 loops=1)
Index Cond: (vendor_id = 4242)
-> Gather (actual time=0.932..14.598 rows=276 loops=1)
Workers Launched: 2
-> Nested Loop (actual time=0.470..9.084 rows=92 loops=3)
-> Nested Loop (actual time=0.445..8.875 rows=16 loops=3)
-> Parallel Seq Scan on client_vendor_contract cvc (actual time=0.428..8.784 rows=16 loops=3)
Filter: (vendor_id = 4242)
Rows Removed by Filter: 83318
-> Index Only Scan using client_pkey on client c (actual time=0.005..0.005 rows=1 loops=47)
Index Cond: (client_id = cvc.client_id)
-> Index Scan using uq_project_contract_name on project p (actual time=0.011..0.012 rows=6 loops=47)
Index Cond: (contract_id = cvc.contract_id)
Execution Time: 15.056 ms
4. Индексирање: idx_cvc_vendor_id; агрегацијата по статус потоа работи врз 276 редици.
Sort (actual time=0.566..0.568 rows=6 loops=1)
Sort Key: v.agency_name, ps.status_name
-> GroupAggregate (actual time=0.524..0.561 rows=6 loops=1)
Group Key: ps.status_name, p.status_id
-> Sort (actual time=0.518..0.529 rows=276 loops=1)
Sort Key: ps.status_name, p.status_id
-> Nested Loop (actual time=0.040..0.461 rows=276 loops=1)
-> Nested Loop (actual time=0.034..0.378 rows=276 loops=1)
-> Nested Loop (actual time=0.027..0.156 rows=47 loops=1)
-> Index Scan using vendor_pkey on vendor v (actual time=0.005..0.006 rows=1 loops=1)
Index Cond: (vendor_id = 4242)
-> Nested Loop (actual time=0.021..0.144 rows=47 loops=1)
-> Bitmap Heap Scan on client_vendor_contract cvc (actual time=0.015..0.063 rows=47 loops=1)
Recheck Cond: (vendor_id = 4242)
-> Bitmap Index Scan on idx_cvc_vendor_id (actual time=0.009..0.009 rows=47 loops=1)
Index Cond: (vendor_id = 4242)
-> Index Only Scan using client_pkey on client c (actual time=0.001..0.001 rows=1 loops=47)
Index Cond: (client_id = cvc.client_id)
-> Index Scan using uq_project_contract_name on project p (actual time=0.003..0.004 rows=6 loops=47)
Index Cond: (contract_id = cvc.contract_id)
-> Memoize (actual time=0.000..0.000 rows=1 loops=276)
[...]
Execution Time: 0.653 ms
5. Време на извршување:
| Читање | Без индексите | Со индексите | Забрзување |
|---|---|---|---|
| филтрирано по софтверска агенција (6 редици) | 14,9 ms | 0,60 ms | 25 пати |
| нефилтрирано (29.907 редици) | 1,36 s | 1,32 s | ист план |
6. Времето на извршување на операциите insert и update врз табелите на погледот е во табелата „Цена при запишување“ на почетокот на страницата.
Поглед 11: Број на проекти по статус по клиент (vw_project_count_by_status_per_client)
1. Примарен филтер за погледот vw_project_count_by_status_per_client ќе биде според client_id на клиентот.
2. Примарен случај на употреба ќе биде преглед на бројот на проекти на клиентот по статус, на неговата контролна табла. Погледот е аналитички (COUNT, GROUP BY).
3. Иницијална состојба: филтрирано, најбавната операција е sequential scan на Client_Vendor_Contract; нефилтрирано, планот е hash join на sequential scan на табелите со агрегација (HashAggregate), кај кој индексите не помагаат.
Sort (actual time=11.627..15.317 rows=5 loops=1)
Sort Key: c.company_name, ps.status_name
-> GroupAggregate (actual time=11.591..15.305 rows=5 loops=1)
Group Key: ps.status_name, p.status_id
-> Incremental Sort (actual time=11.571..15.279 rows=52 loops=1)
Sort Key: ps.status_name, p.status_id
-> Nested Loop (actual time=0.612..15.253 rows=52 loops=1)
Join Filter: (ps.status_id = p.status_id)
Rows Removed by Join Filter: 260
-> Index Scan using project_status_status_name_key on project_status ps (actual time=0.007..0.009 rows=6 loops=1)
-> Materialize (actual time=0.100..2.533 rows=52 loops=6)
-> Nested Loop (actual time=0.598..15.170 rows=52 loops=1)
-> Index Scan using client_pkey on client c (actual time=0.008..0.010 rows=1 loops=1)
Index Cond: (client_id = 4242)
-> Gather (actual time=0.589..15.151 rows=52 loops=1)
Workers Launched: 2
-> Nested Loop (actual time=3.807..8.919 rows=17 loops=3)
-> Nested Loop (actual time=3.778..8.856 rows=2 loops=3)
-> Parallel Seq Scan on client_vendor_contract cvc (actual time=3.729..8.794 rows=2 loops=3)
Filter: (client_id = 4242)
Rows Removed by Filter: 83331
-> Index Only Scan using vendor_pkey on vendor v (actual time=0.023..0.023 rows=1 loops=7)
Index Cond: (vendor_id = cvc.vendor_id)
-> Index Scan using uq_project_contract_name on project p (actual time=0.023..0.025 rows=7 loops=7)
Index Cond: (contract_id = cvc.contract_id)
Execution Time: 15.362 ms
4. Индексирање: idx_cvc_client_id; агрегацијата по статус потоа работи врз 52 редици.
Sort (actual time=0.122..0.124 rows=5 loops=1)
Sort Key: c.company_name, ps.status_name
-> GroupAggregate (actual time=0.111..0.120 rows=5 loops=1)
Group Key: ps.status_name, p.status_id
-> Sort (actual time=0.108..0.111 rows=52 loops=1)
Sort Key: ps.status_name, p.status_id
-> Nested Loop (actual time=0.038..0.097 rows=52 loops=1)
-> Nested Loop (actual time=0.030..0.071 rows=52 loops=1)
-> Nested Loop (actual time=0.026..0.040 rows=7 loops=1)
-> Nested Loop (actual time=0.018..0.025 rows=7 loops=1)
-> Index Scan using client_pkey on client c (actual time=0.005..0.006 rows=1 loops=1)
Index Cond: (client_id = 4242)
-> Bitmap Heap Scan on client_vendor_contract cvc (actual time=0.012..0.017 rows=7 loops=1)
Recheck Cond: (client_id = 4242)
-> Bitmap Index Scan on idx_cvc_client_id (actual time=0.008..0.008 rows=7 loops=1)
Index Cond: (client_id = 4242)
-> Index Only Scan using vendor_pkey on vendor v (actual time=0.002..0.002 rows=1 loops=7)
Index Cond: (vendor_id = cvc.vendor_id)
-> Index Scan using uq_project_contract_name on project p (actual time=0.003..0.003 rows=7 loops=7)
Index Cond: (contract_id = cvc.contract_id)
-> Memoize (actual time=0.000..0.000 rows=1 loops=52)
[...]
Execution Time: 0.148 ms
5. Време на извршување:
| Читање | Без индексите | Со индексите | Забрзување |
|---|---|---|---|
| филтрирано по клиент (5 редици) | 15,4 ms | 0,13 ms | 115 пати |
| нефилтрирано (103.080 редици) | 1,55 s | 1,56 s | ист план |
6. Времето на извршување на операциите insert и update врз табелите на погледот е во табелата „Цена при запишување“ на почетокот на страницата.
Поглед 12: Договори по софтверска агенција (vw_contracts_per_vendor)
1. Примарен филтер за погледот vw_contracts_per_vendor ќе биде според vendor_id на софтверската агенција.
2. Примарен случај на употреба ќе биде преглед на сите активни и историски договори на одредена софтверска агенција. Перформансите на овој поглед се важни, бидејќи се чита при секое отворање на профилот на агенцијата.
3. Иницијална состојба: најбавната операција е sequential scan на табелата Client_Vendor_Contract (250.000 редици, 5.652 страници), паралелен, со отфрлање на 249.953 редици.
Sort (actual time=11.459..15.086 rows=47 loops=1)
Sort Key: v.agency_name, cvc.is_active DESC, cvc.start_date DESC
-> Nested Loop (actual time=0.870..15.011 rows=47 loops=1)
-> Index Scan using vendor_pkey on vendor v (actual time=0.006..0.008 rows=1 loops=1)
Index Cond: (vendor_id = 4242)
-> Gather (actual time=0.863..14.992 rows=47 loops=1)
Workers Launched: 2
-> Nested Loop (actual time=0.414..8.904 rows=16 loops=3)
-> Parallel Seq Scan on client_vendor_contract cvc (actual time=0.389..8.749 rows=16 loops=3)
Filter: (vendor_id = 4242)
Rows Removed by Filter: 83318
-> Index Scan using client_pkey on client c (actual time=0.009..0.009 rows=1 loops=47)
Index Cond: (client_id = cvc.client_id)
Execution Time: 15.108 ms
4. Индексирање: idx_cvc_vendor_id; наместо sequential scan на 5.652 страници се читаат неколку страници од индексот и по една страница за секој од 47-те договори.
Sort (actual time=0.204..0.217 rows=47 loops=1)
Sort Key: v.agency_name, cvc.is_active DESC, cvc.start_date DESC
-> Nested Loop (actual time=0.020..0.147 rows=47 loops=1)
-> Index Scan using vendor_pkey on vendor v (actual time=0.005..0.005 rows=1 loops=1)
Index Cond: (vendor_id = 4242)
-> Nested Loop (actual time=0.014..0.136 rows=47 loops=1)
-> Bitmap Heap Scan on client_vendor_contract cvc (actual time=0.010..0.046 rows=47 loops=1)
Recheck Cond: (vendor_id = 4242)
-> Bitmap Index Scan on idx_cvc_vendor_id (actual time=0.004..0.004 rows=47 loops=1)
Index Cond: (vendor_id = 4242)
-> Index Scan using client_pkey on client c (actual time=0.002..0.002 rows=1 loops=47)
Index Cond: (client_id = cvc.client_id)
Execution Time: 0.238 ms
5. Време на извршување:
| Читање | Без индексите | Со индексите | Забрзување |
|---|---|---|---|
| филтрирано по софтверска агенција (47 редици) | 15,0 ms | 0,28 ms | 53 пати |
| нефилтрирано (250.000 редици) | 819 ms | 808 ms | ист план |
6. Времето на извршување на операциите insert и update врз табелите на погледот е во табелата „Цена при запишување“ на почетокот на страницата.
Поглед 13: Договори по клиент (vw_contracts_per_client)
1. Примарен филтер за погледот vw_contracts_per_client ќе биде според client_id на клиентот.
2. Примарен случај на употреба ќе биде преглед на сите активни и историски договори на одреден клиент, на контролната табла на клиентот.
3. Иницијална состојба: најбавната операција е sequential scan на табелата Client_Vendor_Contract со филтер по client_id, со отфрлање на 249.993 редици.
Gather Merge (actual time=10.854..14.183 rows=7 loops=1)
Workers Launched: 2
-> Sort (actual time=8.551..8.553 rows=2 loops=3)
Sort Key: c.company_name, cvc.is_active DESC, cvc.start_date DESC
-> Nested Loop (actual time=3.035..8.512 rows=2 loops=3)
-> Nested Loop (actual time=3.020..8.486 rows=2 loops=3)
-> Parallel Seq Scan on client_vendor_contract cvc (actual time=3.001..8.459 rows=2 loops=3)
Filter: (client_id = 4242)
Rows Removed by Filter: 83331
-> Index Scan using client_pkey on client c (actual time=0.009..0.010 rows=1 loops=7)
Index Cond: (client_id = 4242)
-> Index Scan using vendor_pkey on vendor v (actual time=0.010..0.010 rows=1 loops=7)
Index Cond: (vendor_id = cvc.vendor_id)
Execution Time: 14.209 ms
4. Индексирање: idx_cvc_client_id. Колоната client_id има 20.000 различни вредности, просечно 12,5 договори по клиент, па е поселективна од vendor_id (5.000 вредности, 50 договори по агенција).
Sort (actual time=0.066..0.067 rows=7 loops=1)
Sort Key: c.company_name, cvc.is_active DESC, cvc.start_date DESC
-> Nested Loop (actual time=0.026..0.041 rows=7 loops=1)
-> Nested Loop (actual time=0.019..0.025 rows=7 loops=1)
-> Index Scan using client_pkey on client c (actual time=0.007..0.008 rows=1 loops=1)
Index Cond: (client_id = 4242)
-> Bitmap Heap Scan on client_vendor_contract cvc (actual time=0.010..0.014 rows=7 loops=1)
Recheck Cond: (client_id = 4242)
-> Bitmap Index Scan on idx_cvc_client_id (actual time=0.005..0.005 rows=7 loops=1)
Index Cond: (client_id = 4242)
-> Index Scan using vendor_pkey on vendor v (actual time=0.002..0.002 rows=1 loops=7)
Index Cond: (vendor_id = cvc.vendor_id)
Execution Time: 0.113 ms
5. Време на извршување:
| Читање | Без индексите | Со индексите | Забрзување |
|---|---|---|---|
| филтрирано по клиент (7 редици) | 14,2 ms | 0,08 ms | 173 пати |
| нефилтрирано (250.000 редици) | 817 ms | 805 ms | ист план |
6. Времето на извршување на операциите insert и update врз табелите на погледот е во табелата „Цена при запишување“ на почетокот на страницата.
Поглед 14: Претплати на софтверски агенции (vw_vendor_subscriptions)
1. Примарен филтер за погледот vw_vendor_subscriptions ќе биде според vendor_id на софтверската агенција.
2. Примарен случај на употреба ќе биде преглед на активните и историските претплати на одредена софтверска агенција.
3. Иницијална состојба: главната операција е sequential scan на табелата Vendor_Subscription (5.916 редици на 57 страници) со отфрлање на 5.914 редици; времето е прифатливо за апликацијата.
Sort (actual time=0.333..0.334 rows=2 loops=1)
Sort Key: v.agency_name, vs.is_active DESC, vs.start_date DESC
-> Nested Loop (actual time=0.279..0.328 rows=2 loops=1)
Join Filter: (st.tier_id = vs.tier_id)
Rows Removed by Join Filter: 5
-> Nested Loop (actual time=0.276..0.323 rows=2 loops=1)
-> Seq Scan on vendor_subscription vs (actual time=0.270..0.315 rows=2 loops=1)
Filter: (vendor_id = 4242)
Rows Removed by Filter: 5914
-> Index Scan using vendor_pkey on vendor v (actual time=0.003..0.003 rows=1 loops=2)
Index Cond: (vendor_id = 4242)
-> Seq Scan on subscription_tier st (actual time=0.001..0.001 rows=4 loops=2)
Execution Time: 0.347 ms
4. За табела од 456 kB индексот не би донел мерлива добивка; нема потреба да се преуреди прашалникот ниту да се додаде индекс.
5. Време на извршување:
| Читање | Време |
|---|---|
| филтрирано по софтверска агенција (2 редици) | 0,32 ms |
| нефилтрирано (5.916 редици) | 11,4 ms |
6. Времето на извршување на операциите insert и update останува исто; врз табелата Vendor_Subscription нема индекси од оваа фаза.
Attachments (1)
- indexes.sql (379 bytes ) - added by 13 days ago.
Download all attachments as: .zip
