You need to sign in or sign up before continuing.
Remove .processed scope from package model and finders
🏛 Context
We recently introduced a status column to the Packages::Package model and table to identify what state a package is in (default, processing, error).
Previously, only NuGet packages had a state that was not the default package state. When a NuGet package is uploaded, a package is created with the name 'NuGet.Temporary.Package'. The uploaded package file goes through a background job that extracts metadata, including the actual name of the package, and then the record is updated. So during this time, the package is said to be processing.
With the new status column, we just set the status to processing when these packages are first created, and then update it to default when the metadata extraction is complete.
When users are installing packages or searching for packages to install, we need to be sure packages in processing are not included. Before we had this status column, we handled this for NuGet packages with the .processed scope, which filtered out NuGet packages with the temporary name 'NuGet.Temporary.Package'.
We now use the status column and have scopes for .installable, which filters on the status column to only include packages that are consumable by users, and .displayable, which filters to include packages that can be displayed in the GitLab UI.
🔎 What does this MR do?
The new scopes are already added to the various package finders and queries, so this MR simply removes the now redundant .processed scope.
🐘 Database
By removing the .processed scope, we are removing this condition from all of the queries being updated:
WHERE ("packages_packages"."package_type" != 4
OR "packages_packages"."name" != 'NuGet.Temporary.Package')All of these queries already have either the .installable or .displayable scope, which shows up as:
-- .installable
WHERE status = 0
-- .displayable
WHERE status in (0,3)NuGet packages that would have matched the old .processed scope now have a status of processing (which is 2 in the enum), so they will continue to be filtered out in these queries.
Overall, the query plans and timings don't change much.
NuGet Finder
Before
SELECT "packages_packages".*
FROM "packages_packages"
WHERE "packages_packages"."project_id" = 15835259
AND "packages_packages"."package_type" = 4
AND "packages_packages"."version" IS NOT NULL
AND "packages_packages"."status" = 0
AND ("packages_packages"."package_type" != 4
OR "packages_packages"."name" != 'NuGet.Temporary.Package')
AND "packages_packages"."name" ILIKE 'issue-reproduce.packages.nuget.nuget-package.MyThirdPackage'
ORDER BY "packages_packages"."created_at" DESC
LIMIT 300;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=132.87..132.87 rows=1 width=83) (actual time=2.438..2.446 rows=48 loops=1)
Buffers: shared hit=619
-> Sort (cost=132.87..132.87 rows=1 width=83) (actual time=2.437..2.441 rows=48 loops=1)
Sort Key: created_at DESC
Sort Method: quicksort Memory: 31kB
Buffers: shared hit=619
-> Index Scan using index_packages_packages_on_project_id_and_package_type on packages_packages (cost=0.43..132.86 rows=1 width=83) (actual time=0.878..2.418 rows=48 loops=1)
Index Cond: ((project_id = 15835259) AND (package_type = 4))
Filter: ((version IS NOT NULL) AND ((name)::text ~~* 'issue-reproduce.packages.nuget.nuget-package.MyThirdPackage'::text) AND (status = 0) AND ((package_type <> 4) OR ((name)::text <> 'NuGet.Temporary.Package'::text)))
Rows Removed by Filter: 931
Buffers: shared hit=619
Planning Time: 3.585 ms
Execution Time: 2.482 ms
(13 rows)
After
SELECT "packages_packages".* FROM "packages_packages" WHERE "packages_packages"."project_id" = 15835259 AND "packages_packages"."package_type" = 4 AND "packages_packages"."version" IS NOT NULL AND "packages_packages"."status" = 0 AND "packages_packages"."name" ILIKE 'issue-reproduce.packages.nuget.nuget-package.MyThirdPackage' ORDER BY "packages_packages"."created_at" DESC LIMIT 300;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=139.65..139.65 rows=1 width=83) (actual time=3.885..3.897 rows=48 loops=1)
Buffers: shared hit=618 read=4
I/O Timings: read=1.554
-> Sort (cost=139.65..139.65 rows=1 width=83) (actual time=3.884..3.890 rows=48 loops=1)
Sort Key: created_at DESC
Sort Method: quicksort Memory: 31kB
Buffers: shared hit=618 read=4
I/O Timings: read=1.554
-> Index Scan using index_packages_packages_on_project_id_and_package_type on packages_packages (cost=0.43..139.64 rows=1 width=83) (actual time=1.255..3.835 rows=48 loops=1)
Index Cond: ((project_id = 15835259) AND (package_type = 4))
Filter: ((version IS NOT NULL) AND ((name)::text ~~* 'issue-reproduce.packages.nuget.nuget-package.MyThirdPackage'::text) AND (status = 0))
Rows Removed by Filter: 931
Buffers: shared hit=615 read=4
I/O Timings: read=1.554
Planning Time: 5.374 ms
Execution Time: 4.008 ms
(16 rows)
Group Packages Finder
Before
SELECT "packages_packages".*
FROM "packages_packages"
WHERE "packages_packages"."project_id" IN (
SELECT "projects"."id"
FROM "projects"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE "projects"."namespace_id" IN (WITH RECURSIVE "base_and_descendants" AS (
(
SELECT "namespaces".*
FROM "namespaces"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."id" = 9970)
UNION (
SELECT "namespaces".*
FROM "namespaces",
"base_and_descendants"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."parent_id" = "base_and_descendants"."id"))
SELECT "namespaces"."id"
FROM "base_and_descendants" AS "namespaces")
AND (EXISTS (
SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 4059254
AND (project_authorizations.project_id = projects.id))
OR projects.visibility_level IN (0, 10, 20))
AND ("project_features"."repository_access_level" > 0
OR "project_features"."repository_access_level" IS NULL))
AND "packages_packages"."version" IS NOT NULL
AND "packages_packages"."status" IN (0, 3)
AND ("packages_packages"."package_type" != 4
OR "packages_packages"."name" != 'NuGet.Temporary.Package')
ORDER BY "packages_packages"."created_at" ASC;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=31468.29..31468.89 rows=239 width=83) (actual time=23.157..23.223 rows=712 loops=1)
Sort Key: packages_packages.created_at
Sort Method: quicksort Memory: 125kB
Buffers: shared hit=21597
-> Nested Loop (cost=23490.20..31458.85 rows=239 width=83) (actual time=18.138..22.851 rows=712 loops=1)
Buffers: shared hit=21597
-> HashAggregate (cost=23489.77..23524.96 rows=3519 width=4) (actual time=17.776..18.052 rows=1361 loops=1)
Group Key: projects.id
Buffers: shared hit=16871
-> Nested Loop Left Join (cost=1591.68..23480.98 rows=3519 width=4) (actual time=2.519..17.251 rows=1361 loops=1)
Filter: ((project_features.repository_access_level > 0) OR (project_features.repository_access_level IS NULL))
Rows Removed by Filter: 14
Buffers: shared hit=16871
-> Nested Loop (cost=1591.12..21270.19 rows=3522 width=4) (actual time=2.503..10.377 rows=1375 loops=1)
Buffers: shared hit=9995
-> HashAggregate (cost=1590.68..1592.59 rows=191 width=4) (actual time=2.467..2.526 rows=232 loops=1)
Group Key: namespaces.id
Buffers: shared hit=1162
-> CTE Scan on base_and_descendants namespaces (cost=1584.47..1588.29 rows=191 width=4) (actual time=0.030..2.365 rows=232 loops=1)
Buffers: shared hit=1162
CTE base_and_descendants
-> Recursive Union (cost=0.43..1584.47 rows=191 width=344) (actual time=0.027..2.021 rows=232 loops=1)
Buffers: shared hit=1162
-> Index Scan using index_namespaces_on_type_and_id_partial on namespaces namespaces_1 (cost=0.43..3.45 rows=1 width=344) (actual time=0.018..0.019 rows=1 loops=1)
Index Cond: (((type)::text = 'Group'::text) AND (id = 9970))
Buffers: shared hit=4
-> Nested Loop (cost=0.56..157.72 rows=19 width=344) (actual time=0.016..0.230 rows=38 loops=6)
Buffers: shared hit=1158
-> WorkTable Scan on base_and_descendants (cost=0.00..0.20 rows=10 width=4) (actual time=0.000..0.005 rows=39 loops=6)
-> Index Scan using index_namespaces_on_parent_id_and_id on namespaces namespaces_2 (cost=0.56..15.73 rows=2 width=344) (actual time=0.004..0.005 rows=1 loops=232)
Index Cond: (parent_id = base_and_descendants.id)
Filter: ((type)::text = 'Group'::text)
Buffers: shared hit=1158
-> Index Scan using index_projects_on_namespace_id_and_id on projects (cost=0.44..102.84 rows=18 width=8) (actual time=0.008..0.033 rows=6 loops=232)
Index Cond: (namespace_id = namespaces.id)
Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (visibility_level = ANY ('{0,10,20}'::integer[])))
Buffers: shared hit=8833
SubPlan 1
-> Index Only Scan using project_authorizations_pkey on project_authorizations (cost=0.57..3.59 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=1375)
Index Cond: ((user_id = 4059254) AND (project_id = projects.id))
Heap Fetches: 449
Buffers: shared hit=6677
SubPlan 2
-> Index Only Scan using project_authorizations_pkey on project_authorizations project_authorizations_1 (cost=0.57..1395.09 rows=4988 width=4) (never executed)
Index Cond: (user_id = 4059254)
Heap Fetches: 0
-> Index Scan using index_project_features_on_project_id on project_features (cost=0.56..0.62 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=1375)
Index Cond: (project_id = projects.id)
Buffers: shared hit=6876
-> Index Scan using index_packages_packages_on_project_id_and_status on packages_packages (cost=0.43..1.75 rows=50 width=83) (actual time=0.003..0.003 rows=1 loops=1361)
Index Cond: (project_id = projects.id)
Filter: ((version IS NOT NULL) AND (status = ANY ('{0,3}'::integer[])) AND ((package_type <> 4) OR ((name)::text <> 'NuGet.Temporary.Package'::text)))
Rows Removed by Filter: 0
Buffers: shared hit=4726
Planning Time: 2.809 ms
Execution Time: 23.547 ms
(56 rows)
After
SELECT "packages_packages".*
FROM "packages_packages"
WHERE "packages_packages"."project_id" IN (
SELECT "projects"."id"
FROM "projects"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE "projects"."namespace_id" IN (WITH RECURSIVE "base_and_descendants" AS (
(
SELECT "namespaces".*
FROM "namespaces"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."id" = 9970)
UNION (
SELECT "namespaces".*
FROM "namespaces",
"base_and_descendants"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."parent_id" = "base_and_descendants"."id"))
SELECT "namespaces"."id"
FROM "base_and_descendants" AS "namespaces")
AND (EXISTS (
SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 4059254
AND (project_authorizations.project_id = projects.id))
OR projects.visibility_level IN (0, 10, 20))
AND ("project_features"."repository_access_level" > 0
OR "project_features"."repository_access_level" IS NULL))
AND "packages_packages"."version" IS NOT NULL
AND "packages_packages"."status" IN (0, 3)
ORDER BY "packages_packages"."created_at" ASC;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=30535.80..30536.40 rows=240 width=83) (actual time=24.033..24.127 rows=712 loops=1)
Sort Key: packages_packages.created_at
Sort Method: quicksort Memory: 125kB
Buffers: shared hit=21598
-> Nested Loop (cost=23490.20..30526.31 rows=240 width=83) (actual time=18.885..23.776 rows=712 loops=1)
Buffers: shared hit=21598
-> HashAggregate (cost=23489.77..23524.96 rows=3519 width=4) (actual time=18.518..18.832 rows=1361 loops=1)
Group Key: projects.id
Buffers: shared hit=16872
-> Nested Loop Left Join (cost=1591.68..23480.98 rows=3519 width=4) (actual time=2.053..17.853 rows=1361 loops=1)
Filter: ((project_features.repository_access_level > 0) OR (project_features.repository_access_level IS NULL))
Rows Removed by Filter: 14
Buffers: shared hit=16872
-> Nested Loop (cost=1591.12..21270.19 rows=3522 width=4) (actual time=2.040..10.489 rows=1375 loops=1)
Buffers: shared hit=9996
-> HashAggregate (cost=1590.68..1592.59 rows=191 width=4) (actual time=2.006..2.077 rows=232 loops=1)
Group Key: namespaces.id
Buffers: shared hit=1162
-> CTE Scan on base_and_descendants namespaces (cost=1584.47..1588.29 rows=191 width=4) (actual time=0.021..1.930 rows=232 loops=1)
Buffers: shared hit=1162
CTE base_and_descendants
-> Recursive Union (cost=0.43..1584.47 rows=191 width=344) (actual time=0.019..1.680 rows=232 loops=1)
Buffers: shared hit=1162
-> Index Scan using index_namespaces_on_type_and_id_partial on namespaces namespaces_1 (cost=0.43..3.45 rows=1 width=344) (actual time=0.012..0.014 rows=1 loops=1)
Index Cond: (((type)::text = 'Group'::text) AND (id = 9970))
Buffers: shared hit=4
-> Nested Loop (cost=0.56..157.72 rows=19 width=344) (actual time=0.014..0.204 rows=38 loops=6)
Buffers: shared hit=1158
-> WorkTable Scan on base_and_descendants (cost=0.00..0.20 rows=10 width=4) (actual time=0.000..0.003 rows=39 loops=6)
-> Index Scan using index_namespaces_on_parent_id_and_id on namespaces namespaces_2 (cost=0.56..15.73 rows=2 width=344) (actual time=0.003..0.005 rows=1 loops=232)
Index Cond: (parent_id = base_and_descendants.id)
Filter: ((type)::text = 'Group'::text)
Buffers: shared hit=1158
-> Index Scan using index_projects_on_namespace_id_and_id on projects (cost=0.44..102.84 rows=18 width=8) (actual time=0.008..0.035 rows=6 loops=232)
Index Cond: (namespace_id = namespaces.id)
Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (visibility_level = ANY ('{0,10,20}'::integer[])))
Buffers: shared hit=8834
SubPlan 1
-> Index Only Scan using project_authorizations_pkey on project_authorizations (cost=0.57..3.59 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=1375)
Index Cond: ((user_id = 4059254) AND (project_id = projects.id))
Heap Fetches: 449
Buffers: shared hit=6677
SubPlan 2
-> Index Only Scan using project_authorizations_pkey on project_authorizations project_authorizations_1 (cost=0.57..1395.09 rows=4988 width=4) (never executed)
Index Cond: (user_id = 4059254)
Heap Fetches: 0
-> Index Scan using index_project_features_on_project_id on project_features (cost=0.56..0.62 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=1375)
Index Cond: (project_id = projects.id)
Buffers: shared hit=6876
-> Index Scan using index_packages_packages_on_project_id_and_status on packages_packages (cost=0.43..1.49 rows=50 width=83) (actual time=0.003..0.003 rows=1 loops=1361)
Index Cond: (project_id = projects.id)
Filter: ((version IS NOT NULL) AND (status = ANY ('{0,3}'::integer[])))
Rows Removed by Filter: 0
Buffers: shared hit=4726
Planning Time: 2.087 ms
Execution Time: 24.385 ms
(56 rows)
Package Finder
Before
SELECT "packages_packages".*
FROM "packages_packages"
WHERE "packages_packages"."project_id" = 17012483
AND "packages_packages"."status" IN (0, 3)
AND ("packages_packages"."package_type" != 4
OR "packages_packages"."name" != 'NuGet.Temporary.Package')
AND "packages_packages"."id" = 1349425
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using index_packages_packages_on_id_and_created_at on packages_packages (cost=0.43..3.46 rows=1 width=83) (actual time=1.631..1.634 rows=1 loops=1)
Index Cond: (id = 1349425)
Filter: ((status = ANY ('{0,3}'::integer[])) AND (project_id = 17012483) AND ((package_type <> 4) OR ((name)::text <> 'NuGet.Temporary.Package'::text)))
Buffers: shared hit=5 read=2
I/O Timings: read=1.586
Planning Time: 0.314 ms
Execution Time: 1.655 ms
(7 rows)
After
SELECT "packages_packages".* FROM "packages_packages" WHERE "packages_packages"."project_id" = 17012483 AND "packages_packages"."status" IN (0, 3) AND "packages_packages"."id" = 1349425
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using index_packages_packages_on_id_and_created_at on packages_packages (cost=0.43..3.45 rows=1 width=83) (actual time=0.014..0.015 rows=1 loops=1)
Index Cond: (id = 1349425)
Filter: ((status = ANY ('{0,3}'::integer[])) AND (project_id = 17012483))
Buffers: shared hit=4
Planning Time: 0.175 ms
Execution Time: 0.031 ms
(6 rows)
Packages Finder
Before
SELECT "packages_packages".*
FROM "packages_packages"
WHERE "packages_packages"."project_id" = 17012483
AND "packages_packages"."version" IS NOT NULL
AND "packages_packages"."status" IN (0, 3)
AND ("packages_packages"."package_type" != 4
OR "packages_packages"."name" != 'NuGet.Temporary.Package')
ORDER BY "packages_packages"."created_at" ASC
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using index_packages_packages_on_project_id_and_created_at on packages_packages (cost=0.43..293.71 rows=211 width=83) (actual time=1.652..41.069 rows=169 loops=1)
Index Cond: (project_id = 17012483)
Filter: ((version IS NOT NULL) AND (status = ANY ('{0,3}'::integer[])) AND ((package_type <> 4) OR ((name)::text <> 'NuGet.Temporary.Package'::text)))
Rows Removed by Filter: 128
Buffers: shared hit=202 read=96
I/O Timings: read=40.063
Planning Time: 0.423 ms
Execution Time: 41.131 ms
(8 rows)
After
SELECT "packages_packages".* FROM "packages_packages" WHERE "packages_packages"."project_id" = 17012483 AND "packages_packages"."version" IS NOT NULL AND "packages_packages"."status" IN (0, 3) ORDER BY "packages_packages"."created_at" ASC
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using index_packages_packages_on_project_id_and_created_at on packages_packages (cost=0.43..292.61 rows=211 width=83) (actual time=0.017..0.338 rows=169 loops=1)
Index Cond: (project_id = 17012483)
Filter: ((version IS NOT NULL) AND (status = ANY ('{0,3}'::integer[])))
Rows Removed by Filter: 128
Buffers: shared hit=298
Planning Time: 0.171 ms
Execution Time: 0.360 ms
(7 rows)
PyPI Packages Finder
The explain plan does not change because the postgres query planner is smart enough to filter out the redundant condition that is overridden by `status = 0 AND package_type = 5`.
Before
SELECT 1 AS one
FROM "packages_packages"
WHERE "packages_packages"."project_id" = 18291791
AND "packages_packages"."status" = 0
AND ("packages_packages"."package_type" != 4
OR "packages_packages"."name" != 'NuGet.Temporary.Package')
AND "packages_packages"."package_type" = 5
AND "packages_packages"."version"
IS NOT NULL AND (LOWER(regexp_replace(name, '[-_.]+', '-', 'g')) = 'mypkg')
LIMIT 1
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.43..3.46 rows=1 width=4) (actual time=0.090..0.090 rows=1 loops=1)
Buffers: shared hit=4
-> Index Scan using index_packages_packages_on_project_id_and_package_type on packages_packages (cost=0.43..3.46 rows=1 width=4) (actual time=0.089..0.089 rows=1 loops=1)
Index Cond: ((project_id = 18291791) AND (package_type = 5))
Filter: ((version IS NOT NULL) AND (status = 0) AND (lower(regexp_replace((name)::text, '[-_.]+'::text, '-'::text, 'g'::text)) = 'mypkg'::text))
Buffers: shared hit=4
Planning Time: 0.414 ms
Execution Time: 0.107 ms
(8 rows)
After
SELECT 1 AS one FROM "packages_packages" WHERE "packages_packages"."project_id" = 18291791 AND "packages_packages"."status" = 0 AND "packages_packages"."package_type" = 5 AND "packages_packages"."version" IS NOT NULL AND (LOWER(regexp_replace(name, '[-_.]+', '-', 'g')) = 'mypkg') LIMIT 1
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.43..3.46 rows=1 width=4) (actual time=0.080..0.081 rows=1 loops=1)
Buffers: shared hit=4
-> Index Scan using index_packages_packages_on_project_id_and_package_type on packages_packages (cost=0.43..3.46 rows=1 width=4) (actual time=0.079..0.080 rows=1 loops=1)
Index Cond: ((project_id = 18291791) AND (package_type = 5))
Filter: ((version IS NOT NULL) AND (status = 0) AND (lower(regexp_replace((name)::text, '[-_.]+'::text, '-'::text, 'g'::text)) = 'mypkg'::text))
Buffers: shared hit=4
Planning Time: 0.256 ms
Execution Time: 0.107 ms
(8 rows)
PyPI Package Finder
The explain plan does not change because the postgres query planner is smart enough to filter out the redundant condition that is overridden by `status = 0 AND package_type = 5`.
Before
SELECT "packages_packages".*
FROM "packages_packages"
INNER JOIN "packages_package_files" ON "packages_package_files"."package_id" = "packages_packages"."id"
WHERE "packages_packages"."project_id" = 18291791
AND "packages_packages"."status" = 0
AND ("packages_packages"."package_type" != 4
OR "packages_packages"."name" != 'NuGet.Temporary.Package')
AND "packages_packages"."package_type" = 5
AND "packages_packages"."version" IS NOT NULL
AND "packages_package_files"."file_name" = 'mypkg-0.1.tar.gz'
AND "packages_package_files"."file_sha256" = '\x66633964663031326136386538663436323834333631633962623137376331613561363336333134616663313532363033663732383665316666343533653033'
ORDER BY "packages_packages"."id" DESC
LIMIT 1
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=7.06..7.06 rows=1 width=83) (actual time=0.047..0.047 rows=1 loops=1)
Buffers: shared hit=10
-> Sort (cost=7.06..7.06 rows=1 width=83) (actual time=0.046..0.046 rows=1 loops=1)
Sort Key: packages_packages.id DESC
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=10
-> Nested Loop (cost=0.99..7.05 rows=1 width=83) (actual time=0.035..0.038 rows=2 loops=1)
Buffers: shared hit=10
-> Index Scan using index_packages_packages_on_project_id_and_package_type on packages_packages (cost=0.43..3.46 rows=1 width=83) (actual time=0.015..0.015 rows=1 loops=1)
Index Cond: ((project_id = 18291791) AND (package_type = 5))
Filter: ((version IS NOT NULL) AND (status = 0))
Buffers: shared hit=4
-> Index Scan using index_packages_package_files_on_package_id_and_file_name on packages_package_files (cost=0.56..3.58 rows=1 width=8) (actual time=0.018..0.020 rows=2 loops=1)
Index Cond: ((package_id = packages_packages.id) AND ((file_name)::text = 'mypkg-0.1.tar.gz'::text))
Filter: (file_sha256 = '\x66633964663031326136386538663436323834333631633962623137376331613561363336333134616663313532363033663732383665316666343533653033'::bytea)
Buffers: shared hit=6
Planning Time: 0.976 ms
Execution Time: 0.084 ms
(18 rows)
After
SELECT "packages_packages".* FROM "packages_packages" INNER JOIN "packages_package_files" ON "packages_package_files"."package_id" = "packages_packages"."id" WHERE "packages_packages"."project_id" = 18291791 AND "packages_packages"."status" = 0 AND "packages_packages"."package_type" = 5 AND "packages_packages"."version" IS NOT NULL AND "packages_package_files"."file_name" = 'mypkg-0.1.tar.gz' AND "packages_package_files"."file_sha256" = '\x66633964663031326136386538663436323834333631633962623137376331613561363336333134616663313532363033663732383665316666343533653033' ORDER BY "packages_packages"."id" DESC LIMIT 1
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=7.05..7.06 rows=1 width=83) (actual time=0.086..0.087 rows=1 loops=1)
Buffers: shared hit=13
-> Sort (cost=7.05..7.06 rows=1 width=83) (actual time=0.085..0.086 rows=1 loops=1)
Sort Key: packages_packages.id DESC
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=13
-> Nested Loop (cost=0.99..7.04 rows=1 width=83) (actual time=0.034..0.037 rows=2 loops=1)
Buffers: shared hit=10
-> Index Scan using index_packages_packages_on_project_id_and_package_type on packages_packages (cost=0.43..3.45 rows=1 width=83) (actual time=0.010..0.011 rows=1 loops=1)
Index Cond: ((project_id = 18291791) AND (package_type = 5))
Filter: ((version IS NOT NULL) AND (status = 0))
Buffers: shared hit=4
-> Index Scan using index_packages_package_files_on_package_id_and_file_name on packages_package_files (cost=0.56..3.58 rows=1 width=8) (actual time=0.021..0.023 rows=2 loops=1)
Index Cond: ((package_id = packages_packages.id) AND ((file_name)::text = 'mypkg-0.1.tar.gz'::text))
Filter: (file_sha256 = '\x66633964663031326136386538663436323834333631633962623137376331613561363336333134616663313532363033663732383665316666343533653033'::bytea)
Buffers: shared hit=6
Planning Time: 0.986 ms
Execution Time: 0.133 ms
(18 rows)
☑ Does this MR meet the acceptance criteria?
Conformity
-
I have included a changelog entry, or it's not needed. (Does this MR need a changelog?) -
I have added/updated documentation, or it's not needed. (Is documentation required?) -
I have properly separated EE content from FOSS, or this MR is FOSS only. (Where should EE code go?) -
I have added information for database reviewers in the MR description, or it's not needed. (Does this MR have database related changes?) -
I have self-reviewed this MR per code review guidelines. -
This MR does not harm performance, or I have asked a reviewer to help assess the performance impact. (Merge request performance guidelines) -
I have followed the style guides.
Availability and Testing
-
I have added/updated tests following the Testing Guide, or it's not needed. (Consider all test levels. See the Test Planning Process.) -
I have tested this MR in all supported browsers, or it's not needed. -
I have informed the Infrastructure department of a default or new setting change per definition of done, or it's not needed.
Related to #324206 (closed)
Edited by Steve Abrams