Geo: Improve performance of package files queries
What does this MR do?
Removes a CTE which was written to improve performance of cross-database queries using FDW. Since we rearchitected the backend to no longer perform those particular cross-database queries, we should remove this CTE to:
- give PostgreSQL the opportunity to optimize the whole query
- improve readability and maintainability
Resolves #222617 (closed)
Queries
Summary:
- Selective sync by shard query was 43s => now 0.1s
- Selective sync by namespace query was 6s => now 0.3s
Before - selective sync by shard
EXPLAIN WITH "restricted_packages" AS (
SELECT
"packages_packages"."id"
FROM
"packages_packages"
WHERE
"packages_packages"."project_id" IN (
SELECT
"projects"."id"
FROM
"projects"
WHERE
"projects"."repository_storage" = 'nfs-file22'
)
)
SELECT
"packages_package_files"."id"
FROM
"restricted_packages"
INNER JOIN "packages_package_files" ON "restricted_packages"."id" = "packages_package_files"."package_id"
WHERE
"packages_package_files"."id" BETWEEN 1
AND 1000Plan with execution:
Hash Join (cost=283226.65..283861.10 rows=34 width=8) (actual time=43948.639..43949.529 rows=35 loops=1)
Hash Cond: (packages_package_files.package_id = restricted_packages.id)
Buffers: shared hit=553909 read=102654 dirtied=8767
I/O Timings: read=41617.001
CTE restricted_packages
-> Merge Join (cost=6292.38..282935.15 rows=8956 width=8) (actual time=10871.256..43942.639 rows=6477 loops=1)
Merge Cond: (packages_packages.project_id = projects.id)
Buffers: shared hit=553051 read=102654 dirtied=8767
I/O Timings: read=41617.001
-> Index Scan using index_packages_packages_on_project_id_and_package_type on public.packages_packages (cost=0.42..19149.50 rows=397217 width=12) (actual time=3.609..3331.170 rows=398536 loops=1)
Buffers: shared hit=259716 read=2269 dirtied=13
I/O Timings: read=2912.833
-> Index Only Scan using idx_projects_on_repository_storage_last_repository_updated_at on public.projects (cost=0.56..262051.04 rows=343964 width=4) (actual time=6776.332..40465.224 rows=345688 loops=1)
Index Cond: (projects.repository_storage = 'nfs-file22'::text)
Heap Fetches: 26480
Buffers: shared hit=293335 read=100385 dirtied=8754
I/O Timings: read=38704.168
-> Index Scan using packages_package_files_pkey on public.packages_package_files (cost=0.43..99.11 rows=477 width=16) (actual time=0.187..0.910 rows=946 loops=1)
Index Cond: ((packages_package_files.id >= 1) AND (packages_package_files.id <= 1000))
Buffers: shared hit=855
-> Hash (cost=179.12..179.12 rows=8956 width=8) (actual time=43948.204..43948.204 rows=6477 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 382kB
Buffers: shared hit=553051 read=102654 dirtied=8767
I/O Timings: read=41617.001
-> CTE Scan on restricted_packages (cost=0.00..179.12 rows=8956 width=8) (actual time=10871.260..43945.740 rows=6477 loops=1)
Buffers: shared hit=553051 read=102654 dirtied=8767
I/O Timings: read=41617.001
Recommendations:
:exclamation: Query processes too much data to return a relatively small number of rows. – Reduce data cardinality as early as possible during the execution, using one or several of the following techniques: new indexes, partitioning, query rewriting, denormalization. See the visualization of the plan to understand which plan nodes are the main bottlenecks. Show details
:exclamation: VACUUM ANALYZE needed – Visibility map(s) for the table(s) involved in the query are outdated. For better performance: 1) run VACUUM ANALYZE on them as a one-time solution, 2) perform autovacuum tuning as a long-term permanent solution (tuning "vacuum" part of autovacuum). Show details
Summary:
Time: 43.951 s
- planning: 1.590 ms
- execution: 43.950 s
- I/O read: 41.617 s
- I/O write: 0.000 ms
Shared buffers:
- hits: 553909 (~4.20 GiB) from the buffer pool
- reads: 102654 (~802.00 MiB) from the OS file cache, including disk I/O
- dirtied: 8767 (~68.50 MiB)
- writes: 0After - selective sync by shard
EXPLAIN
SELECT
"packages_package_files"."id"
FROM
"packages_package_files"
INNER JOIN "packages_packages" ON "packages_packages"."id" = "packages_package_files"."package_id"
WHERE
(
packages_packages.project_id IN (
SELECT
"projects"."id"
FROM
"projects"
WHERE
"projects"."repository_storage" = 'nfs-file22'
)
)
AND "packages_package_files"."id" BETWEEN 1
AND 1000;Plan with execution:
Nested Loop (cost=1.41..2073.58 rows=11 width=8) (actual time=31.265..146.130 rows=35 loops=1)
Buffers: shared hit=8323 read=101
I/O Timings: read=135.642
-> Nested Loop (cost=0.85..1618.49 rows=477 width=12) (actual time=10.467..120.458 rows=946 loops=1)
Buffers: shared hit=4552 read=87
I/O Timings: read=114.595
-> Index Scan using packages_package_files_pkey on public.packages_package_files (cost=0.43..99.11 rows=477 width=16) (actual time=4.006..98.236 rows=946 loops=1)
Index Cond: ((packages_package_files.id >= 1) AND (packages_package_files.id <= 1000))
Buffers: shared hit=780 read=75
I/O Timings: read=96.320
-> Index Scan using packages_packages_pkey on public.packages_packages (cost=0.42..3.19 rows=1 width=12) (actual time=0.022..0.022 rows=1 loops=946)
Index Cond: (packages_packages.id = packages_package_files.package_id)
Buffers: shared hit=3772 read=12
I/O Timings: read=18.275
-> Index Only Scan using idx_projects_on_repository_storage_last_repository_updated_at on public.projects (cost=0.56..0.95 rows=1 width=4) (actual time=0.027..0.027 rows=0 loops=946)
Index Cond: ((projects.id = packages_packages.project_id) AND (projects.repository_storage = 'nfs-file22'::text))
Heap Fetches: 0
Buffers: shared hit=3771 read=14
I/O Timings: read=21.047
Recommendations:
:exclamation: Query processes too much data to return a relatively small number of rows. – Reduce data cardinality as early as possible during the execution, using one or several of the following techniques: new indexes, partitioning, query rewriting, denormalization. See the visualization of the plan to understand which plan nodes are the main bottlenecks. Show details
Summary:
Time: 148.733 ms
- planning: 2.480 ms
- execution: 146.253 ms
- I/O read: 135.642 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 8323 (~65.00 MiB) from the buffer pool
- reads: 101 (~808.00 KiB) from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0Before - selective sync by namespace
EXPLAIN WITH "restricted_packages" AS (
SELECT
"packages_packages"."id"
FROM
"packages_packages"
WHERE
"packages_packages"."project_id" IN (
SELECT
"projects"."id"
FROM
"projects"
WHERE
"projects"."namespace_id" IN (
WITH RECURSIVE "base_and_descendants" AS (
(
SELECT
9970 AS id
)
UNION
(
SELECT
"namespaces"."id"
FROM
"namespaces",
"base_and_descendants"
WHERE
"namespaces"."parent_id" = "base_and_descendants"."id"
)
)
SELECT
"id"
FROM
"base_and_descendants" AS "namespaces"
)
)
)
SELECT
"packages_package_files"."id"
FROM
"restricted_packages"
INNER JOIN "packages_package_files" ON "restricted_packages"."id" = "packages_package_files"."package_id"
WHERE
"packages_package_files"."id" BETWEEN 1
AND 1000Plan with execution:
Hash Join (cost=391404.09..415203.39 rows=1518 width=8) (actual time=6057.775..6059.786 rows=32 loops=1)
Hash Cond: (packages_package_files.package_id = restricted_packages.id)
Buffers: shared hit=260470 read=5080 dirtied=312
I/O Timings: read=5071.090
CTE restricted_packages
-> Hash Semi Join (cost=215699.65..378494.11 rows=397217 width=8) (actual time=5439.020..6053.534 rows=61 loops=1)
Hash Cond: (packages_packages.project_id = projects.id)
Buffers: shared hit=259612 read=5080 dirtied=312
I/O Timings: read=5071.090
-> Index Scan using index_packages_packages_on_project_id_and_package_type on public.packages_packages (cost=0.42..19149.50 rows=397217 width=12) (actual time=0.038..5112.849 rows=398635 loops=1)
Buffers: shared hit=257641 read=4685 dirtied=234
I/O Timings: read=4498.981
-> Hash (cost=1163.33..1163.33 rows=7627832 width=4) (actual time=589.815..589.816 rows=1073 loops=1)
Buckets: 4194304 Batches: 8 Memory Usage: 32825kB
Buffers: shared hit=1971 read=395 dirtied=78
I/O Timings: read=572.109
-> Nested Loop (cost=364.62..1163.33 rows=7627832 width=4) (actual time=13.289..587.259 rows=1073 loops=1)
Buffers: shared hit=1971 read=395 dirtied=78
I/O Timings: read=572.109
-> HashAggregate (cost=364.19..366.19 rows=200 width=4) (actual time=3.245..3.583 rows=178 loops=1)
Group Key: namespaces_1.id
Buffers: shared hit=678
-> CTE Scan on base_and_descendants namespaces_1 (cost=338.16..354.18 rows=801 width=4) (actual time=0.008..3.141 rows=178 loops=1)
Buffers: shared hit=678
CTE base_and_descendants
-> Recursive Union (cost=0.00..338.16 rows=801 width=4) (actual time=0.006..3.058 rows=178 loops=1)
Buffers: shared hit=678
-> Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=1)
-> Nested Loop (cost=0.43..32.21 rows=80 width=4) (actual time=0.105..0.585 rows=35 loops=5)
Buffers: shared hit=678
-> WorkTable Scan on base_and_descendants (cost=0.00..0.20 rows=10 width=4) (actual time=0.000..0.006 rows=36 loops=5)
-> Index Only Scan using index_namespaces_on_parent_id_and_id on public.namespaces (cost=0.43..3.12 rows=8 width=8) (actual time=0.014..0.016 rows=1 loops=178)
Index Cond: (namespaces.parent_id = base_and_descendants.id)
Heap Fetches: 16
Buffers: shared hit=678
-> Index Only Scan using index_projects_on_namespace_id_and_id on public.projects (cost=0.43..3.81 rows=18 width=8) (actual time=2.168..3.271 rows=6 loops=178)
Index Cond: (projects.namespace_id = namespaces_1.id)
Heap Fetches: 197
Buffers: shared hit=1293 read=395 dirtied=78
I/O Timings: read=572.109
-> Index Scan using packages_package_files_pkey on public.packages_package_files (cost=0.43..99.11 rows=477 width=16) (actual time=0.066..2.028 rows=946 loops=1)
Index Cond: ((packages_package_files.id >= 1) AND (packages_package_files.id <= 1000))
Buffers: shared hit=855
-> Hash (cost=7944.34..7944.34 rows=397217 width=8) (actual time=6053.678..6053.678 rows=61 loops=1)
Buckets: 524288 Batches: 1 Memory Usage: 4099kB
Buffers: shared hit=259612 read=5080 dirtied=312
I/O Timings: read=5071.090
-> CTE Scan on restricted_packages (cost=0.00..7944.34 rows=397217 width=8) (actual time=5439.025..6053.595 rows=61 loops=1)
Buffers: shared hit=259612 read=5080 dirtied=312
I/O Timings: read=5071.090
Recommendations:
:exclamation: Query processes too much data to return a relatively small number of rows. – Reduce data cardinality as early as possible during the execution, using one or several of the following techniques: new indexes, partitioning, query rewriting, denormalization. See the visualization of the plan to understand which plan nodes are the main bottlenecks. Show details
:exclamation: Temporary buffers written – Raise work_mem (currently, the recipe is: exec alter system set work_mem to '100MB', then exec select pg_reload_conf()) Show details
:exclamation: VACUUM ANALYZE needed – Visibility map(s) for the table(s) involved in the query are outdated. For better performance: 1) run VACUUM ANALYZE on them as a one-time solution, 2) perform autovacuum tuning as a long-term permanent solution (tuning "vacuum" part of autovacuum). Show details
Summary:
Time: 6.065 s
- planning: 2.190 ms
- execution: 6.063 s
- I/O read: 5.071 s
- I/O write: 0.000 ms
Shared buffers:
- hits: 260470 (~2.00 GiB) from the buffer pool
- reads: 5080 (~39.70 MiB) from the OS file cache, including disk I/O
- dirtied: 312 (~2.40 MiB)
- writes: 0
Temp buffers:
- reads: 351 (~2.70 MiB)
- writes: 351 (~2.70 MiB)After - selective sync by namespace
EXPLAIN
SELECT
"packages_package_files"."id"
FROM
"packages_package_files"
INNER JOIN "packages_packages" ON "packages_packages"."id" = "packages_package_files"."package_id"
WHERE
(
packages_packages.project_id IN (
SELECT
"projects"."id"
FROM
"projects"
WHERE
"projects"."namespace_id" IN (
WITH RECURSIVE "base_and_descendants" AS (
(
SELECT
9970 AS id
)
UNION
(
SELECT
"namespaces"."id"
FROM
"namespaces",
"base_and_descendants"
WHERE
"namespaces"."parent_id" = "base_and_descendants"."id"
)
)
SELECT
"id"
FROM
"base_and_descendants" AS "namespaces"
)
)
)
AND "packages_package_files"."id" BETWEEN 1
AND 1000; Nested Loop Semi Join (cost=339.44..179982.93 rows=477 width=8) (actual time=277.546..342.000 rows=32 loops=1)
Buffers: shared hit=8846 read=255 dirtied=6
I/O Timings: read=273.810
-> Nested Loop (cost=0.85..1618.49 rows=477 width=12) (actual time=0.639..10.740 rows=946 loops=1)
Buffers: shared hit=4552 read=87
I/O Timings: read=4.436
-> Index Scan using packages_package_files_pkey on public.packages_package_files (cost=0.43..99.11 rows=477 width=16) (actual time=0.232..5.583 rows=946 loops=1)
Index Cond: ((packages_package_files.id >= 1) AND (packages_package_files.id <= 1000))
Buffers: shared hit=780 read=75
I/O Timings: read=3.622
-> Index Scan using packages_packages_pkey on public.packages_packages (cost=0.42..3.19 rows=1 width=12) (actual time=0.004..0.004 rows=1 loops=946)
Index Cond: (packages_packages.id = packages_package_files.package_id)
Buffers: shared hit=3772 read=12
I/O Timings: read=0.813
-> Nested Loop Semi Join (cost=338.59..373.92 rows=1 width=4) (actual time=0.349..0.349 rows=0 loops=946)
Buffers: shared hit=4294 read=168 dirtied=6
I/O Timings: read=269.374
-> Index Scan using projects_pkey on public.projects (cost=0.43..1.72 rows=1 width=8) (actual time=0.049..0.050 rows=1 loops=946)
Index Cond: (projects.id = packages_packages.project_id)
Buffers: shared hit=3765 read=19 dirtied=1
I/O Timings: read=43.353
-> CTE Scan on base_and_descendants namespaces (cost=338.16..354.18 rows=801 width=4) (actual time=0.000..0.273 rows=172 loops=946)
Buffers: shared hit=529 read=149 dirtied=5
I/O Timings: read=226.021
CTE base_and_descendants
-> Recursive Union (cost=0.00..338.16 rows=801 width=4) (actual time=0.005..232.100 rows=178 loops=1)
Buffers: shared hit=529 read=149 dirtied=5
I/O Timings: read=226.021
-> Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)
-> Nested Loop (cost=0.43..32.21 rows=80 width=4) (actual time=5.344..46.357 rows=35 loops=5)
Buffers: shared hit=529 read=149 dirtied=5
I/O Timings: read=226.021
-> WorkTable Scan on base_and_descendants (cost=0.00..0.20 rows=10 width=4) (actual time=0.001..0.019 rows=36 loops=5)
-> Index Only Scan using index_namespaces_on_parent_id_and_id on public.namespaces namespaces_1 (cost=0.43..3.12 rows=8 width=8) (actual time=1.048..1.299 rows=1 loops=178)
Index Cond: (namespaces_1.parent_id = base_and_descendants.id)
Heap Fetches: 16
Buffers: shared hit=529 read=149 dirtied=5
I/O Timings: read=226.021
Recommendations:
:exclamation: Query processes too much data to return a relatively small number of rows. – Reduce data cardinality as early as possible during the execution, using one or several of the following techniques: new indexes, partitioning, query rewriting, denormalization. See the visualization of the plan to understand which plan nodes are the main bottlenecks. Show details
:exclamation: VACUUM ANALYZE needed – Visibility map(s) for the table(s) involved in the query are outdated. For better performance: 1) run VACUUM ANALYZE on them as a one-time solution, 2) perform autovacuum tuning as a long-term permanent solution (tuning "vacuum" part of autovacuum). Show details
Summary:
Time: 344.654 ms
- planning: 2.469 ms
- execution: 342.185 ms
- I/O read: 273.810 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 8846 (~69.10 MiB) from the buffer pool
- reads: 255 (~2.00 MiB) from the OS file cache, including disk I/O
- dirtied: 6 (~48.00 KiB)
- writes: 0Does this MR meet the acceptance criteria?
Conformity
-
Changelog entry -
Documentation (if required) -
Code review guidelines -
Merge request performance guidelines -
Style guides -
Database guides -
Separation of EE specific content
Availability and Testing
-
Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process.
Edited by Michael Kozono