Page MenuHomeSoftware Heritage
Paste P569

select randomly one origin in swh dataset
ActivePublic

Authored by ardumont on Dec 6 2019, 12:14 PM.
with swh_count_origins as (
select value
from object_counts
where object_type='origin'
),
swh_random_id as (
select floor(random() * (select * from swh_count_origins))::int
)
select * from origin where id=(select * from swh_random_id);

Event Timeline

softwareheritage-> select * from origin where id=(select * from swh_random_id);
                                       QUERY PLAN
-----------------------------------------------------------------------------------------
 Index Scan using origin_pkey on origin  (cost=2.79..4.80 rows=1 width=52)
   Index Cond: (id = $3)
   CTE swh_count_origins
     ->  Bitmap Heap Scan on object_counts  (cost=1.14..2.16 rows=1 width=8)
           Recheck Cond: (object_type = 'origin'::text)
           ->  Bitmap Index Scan on object_counts_pkey  (cost=0.00..1.14 rows=1 width=0)
                 Index Cond: (object_type = 'origin'::text)
   CTE swh_random_id
     ->  Result  (cost=0.02..0.04 rows=1 width=4)
           InitPlan 2 (returns $1)
             ->  CTE Scan on swh_count_origins  (cost=0.00..0.02 rows=1 width=8)
   InitPlan 4 (returns $3)
     ->  CTE Scan on swh_random_id  (cost=0.00..0.02 rows=1 width=4)
(13 rows)

softwareheritage=> with swh_count_origins as (
  select value
  from object_counts
  where object_type='origin'
),
swh_random_id as (
  select floor(random() * (select * from swh_count_origins))::int
)
select * from origin where id=(select * from swh_random_id);
    id    |                url
----------+-----------------------------------
 71997035 | https://github.com/AnnaNOh/aaW2D4
(1 row)