Differential D4615 Diff 16363 swh/storage/sql/40-funcs.sql

Changeset View

Standalone View

swh/storage/sql/40-funcs.sql

Show First 20 Lines • Show All 651 Lines • ▼ Show 20 Lines	create type snapshot_result as (
snapshot_id sha1_git,		snapshot_id sha1_git,
name bytea,		name bytea,
target bytea,		target bytea,
target_type snapshot_target		target_type snapshot_target
);		);

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,
		branches_name_pattern text default NULL)
returns setof snapshot_result		returns setof snapshot_result
language sql		language sql
stable		stable
as $$		as $$
-- with small limits, the "naive" version of this query can degenerate into		-- with small limits, the "naive" version of this query can degenerate into
-- using the deduplication index on snapshot_branch (name, target,		-- using the deduplication index on snapshot_branch (name, target,
-- target_type); The planner happily scans several hundred million rows.		-- target_type); The planner happily scans several hundred million rows.

-- Do the query in two steps: first pull the relevant branches for the given		-- 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		-- snapshot (filtering them by type), then do the limiting. This two-step
-- process guides the planner into using the proper index.		-- process guides the planner into using the proper index.
with filtered_snapshot_branches as (		with filtered_snapshot_branches as (
select swh_snapshot_get_by_id.id as snapshot_id, name, target, target_type		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))
order by name		order by name
)		)
select snapshot_id, name, target, target_type		select snapshot_id, name, target, target_type
from filtered_snapshot_branches		from filtered_snapshot_branches
where name >= branches_from		where name >= branches_from
		and (branches_name_pattern is null or convert_from(name, 'utf-8') ~* branches_name_pattern)
order by name limit branches_count;		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
);		);

▲ Show 20 Lines • Show All 270 Lines • Show Last 20 Lines