search

nais / api

MIT License
1 stars 0 forks source link

Initial work on materialized views #13

Open thokra-nav opened 4 months ago

thokra-nav commented 4 months ago

Using materialized views will create a copy of the data in a preprocessed and stored way for postgresql to do more efficient queries against.

Some quick numbers from my local setup, non-scientific, using NAV data:

Query Before After QI Avg
MonthlyCostForTeam 236.52ms 514μs 2.288s
DailyEnvCostForTeam 16.9ms 14.96ms -
ResourceUtilizationRangeForTeam 254.88ms 462μs 1.439s

QI Avg: Average execution time as reported by Query Insights for live NAV environment

Pros

Cons

Some more testing needs to be done to verify the usefulness of each materialized view. Need to create trigger when the source changes.

thokra-nav commented 4 months ago

Laget litt benchmark for de forskjellige spørringene, er litt mer jobb å gjøre:

                                             │   before.txt    │               after.txt                │
                                             │     sec/op      │    sec/op      vs base                 │
ResourceUtilizationRangeForTeam_exists-8        80314.0µ ± 33%    100.3µ ±  7%   -99.88% (p=0.000 n=10)
ResourceUtilizationRangeForTeam_not_exists-8      283.6µ ±  1%    104.9µ ±  4%   -63.00% (p=0.000 n=10)
MonthlyCostForApp-8                              2005.1µ ± 14%    159.4µ ±  5%   -92.05% (p=0.000 n=10)
MonthlyCostForTeam-8                           136728.1µ ±  1%    172.2µ ±  4%   -99.87% (p=0.000 n=10)
DailyEnvCostForTeam-8                             268.0µ ±  8%    139.6µ ±  5%   -47.90% (p=0.000 n=10)
DailyEnvCostForTeam_with_env-8                    7.402m ±  6%    7.755m ±  2%    +4.76% (p=0.007 n=10)
ResourceUtilizationRangeForTeam-8              103422.0µ ±  9%    155.9µ ± 26%   -99.85% (p=0.000 n=10)
ResourceUtilizationRangeForApp-8                15345.6µ ±  4%    167.2µ ±  5%   -98.91% (p=0.000 n=10)
ResourceUtilizationForTeam-8                   200905.8µ ±  3%    900.7µ ±  1%   -99.55% (p=0.000 n=10)
SpecificResourceUtilizationForTeam-8              933.1µ ±  9%   2413.5µ ±  3%  +158.65% (p=0.000 n=10)
AverageResourceUtilizationForTeam-8              47.553m ±  7%    2.740m ±  2%   -94.24% (p=0.000 n=10)
thokra-nav commented 4 months ago

Fra datasettet jeg har, som tilsvarer NAV 6. mars 2024, så er følgende disk i bruk:

Tabell/View Størrelse
cost 627 MB
cost_monthly_team* 424 kB
cost_monthly_app* 12 MB
cost_daily_team* 317 MB
resource_utilization_metrics 6650 MB
resource_team_range* 40 kB
resource_app_range* 800 kB
resource_utilization_team* 361 MB

Så for cost er det en økning på ~50%, mens det er ~5% for resource_utilization_metrics

Data hentet via 

SELECT pg_size_pretty(pg_total_relation_size('"public"."<table/view>"'));`