Differential D3320 Diff 11764 swh/storage/sql/40-swh-func.sql

Changeset View

Standalone View

swh/storage/sql/40-swh-func.sql

	Show First 20 Lines • Show All 644 Lines • ▼ Show 20 Lines

	create or replace function swh_snapshot_get_by_id(id sha1_git,			create or replace function swh_snapshot_get_by_id(id sha1_git,
	branches_from bytea default '', branches_count bigint default null,			branches_from bytea default '', branches_count bigint default null,
	target_types snapshot_target[] default NULL)			target_types snapshot_target[] default NULL)
	returns setof snapshot_result			returns setof snapshot_result
	language sql			language sql
	stable			stable
	as $$			as $$
	select			-- with small limits, the "naive" version of this query can degenerate into
	swh_snapshot_get_by_id.id as snapshot_id, name, target, target_type			-- using the deduplication index on snapshot_branch (name, target,
				-- target_type); The planner happily scans several hundred million rows.

				-- Do the query in two steps: first pull the relevant branches for the given
				-- snapshot (filtering them by type), then do the limiting. This two-step
				-- process guides the planner into using the proper index.
				with filtered_snapshot_branches as (
				select swh_snapshot_get_by_id.id as snapshot_id, name, target, target_type
	from snapshot_branches			from snapshot_branches
	inner join snapshot_branch on snapshot_branches.branch_id = snapshot_branch.object_id			inner join snapshot_branch on snapshot_branches.branch_id = snapshot_branch.object_id
	where snapshot_id = (select object_id from snapshot where snapshot.id = swh_snapshot_get_by_id.id)			where snapshot_id = (select object_id from snapshot where snapshot.id = swh_snapshot_get_by_id.id)
	and (target_types is null or target_type = any(target_types))			and (target_types is null or target_type = any(target_types))
	and name >= branches_from			order by name
	order by name limit branches_count			)
				select snapshot_id, name, target, target_type
				from filtered_snapshot_branches
				where name >= branches_from
				order by name limit branches_count;
	$$;			$$;

	create type snapshot_size as (			create type snapshot_size as (
	target_type snapshot_target,			target_type snapshot_target,
	count bigint			count bigint
	);			);

	create or replace function swh_snapshot_count_branches(id sha1_git)			create or replace function swh_snapshot_count_branches(id sha1_git)
	▲ Show 20 Lines • Show All 269 Lines • Show Last 20 Lines