Page MenuHomeSoftware Heritage
Paste P1451

(An Untitled Masterwork)
ActivePublic
Actions

Authored by olasd on Sep 13 2022, 1:29 PM.
2:13 guest@softwareheritage => explain analyze with closest_two_visits as ((
select ov, (date - '2021-05-03 10:00:00+00'), visit as interval
from origin_visit ov
where ov.origin = 2695882
and ov.date >= '2021-05-03 10:00:00+00'
order by ov.date asc, ov.visit desc
limit 1
) union (
select ov, ('2021-05-03 10:00:00+00' - date), visit as interval
from origin_visit ov
where ov.origin = 2695882
and ov.date < '2021-05-03 10:00:00+00'
order by ov.date desc, ov.visit desc
limit 1
)) select (ov).* from closest_two_visits order by interval, visit limit 1;
QUERY PLAN
────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
Limit (cost=4134.89..4134.89 rows=1 width=64) (actual time=338.578..338.586 rows=1 loops=1)
-> Sort (cost=4134.89..4134.90 rows=2 width=64) (actual time=338.576..338.584 rows=1 loops=1)
Sort Key: closest_two_visits."interval", ((closest_two_visits.ov).visit)
Sort Method: quicksort Memory: 25kB
-> Subquery Scan on closest_two_visits (cost=4134.84..4134.88 rows=2 width=64) (actual time=338.553..338.568 rows=2 loops=1)
-> Unique (cost=4134.84..4134.86 rows=2 width=56) (actual time=338.545..338.557 rows=2 loops=1)
-> Sort (cost=4134.84..4134.85 rows=2 width=56) (actual time=338.543..338.549 rows=2 loops=1)
Sort Key: "*SELECT* 1".ov, "*SELECT* 1"."?column?", "*SELECT* 1"."interval"
Sort Method: quicksort Memory: 25kB
-> Append (cost=2064.12..4134.83 rows=2 width=56) (actual time=336.115..338.440 rows=2 loops=1)
-> Subquery Scan on "*SELECT* 1" (cost=2064.12..2064.13 rows=1 width=76) (actual time=336.113..336.119 rows=1 loops=1)
-> Limit (cost=2064.12..2064.12 rows=1 width=84) (actual time=336.101..336.105 rows=1 loops=1)
-> Sort (cost=2064.12..2064.37 rows=103 width=84) (actual time=336.099..336.101 rows=1 loops=1)
Sort Key: ov.date, ov.visit DESC
Sort Method: top-N heapsort Memory: 25kB
-> Index Scan using origin_visit_pkey on origin_visit ov (cost=0.58..2063.60 rows=103 width=84) (actual time=30.165..334.786 rows=562 loops=1)
Index Cond: (origin = 2695882)
Filter: (date >= '2021-05-03 10:00:00+00'::timestamp with time zone)
Rows Removed by Filter: 50
-> Subquery Scan on "*SELECT* 2" (cost=2070.68..2070.69 rows=1 width=76) (actual time=2.311..2.314 rows=1 loops=1)
-> Limit (cost=2070.68..2070.68 rows=1 width=84) (actual time=2.296..2.298 rows=1 loops=1)
-> Sort (cost=2070.68..2073.12 rows=978 width=84) (actual time=2.294..2.295 rows=1 loops=1)
Sort Key: ov_1.date DESC, ov_1.visit DESC
Sort Method: top-N heapsort Memory: 25kB
-> Index Scan using origin_visit_pkey on origin_visit ov_1 (cost=0.58..2065.79 rows=978 width=84) (actual time=0.083..2.210 rows=50 loops=1)
Index Cond: (origin = 2695882)
Filter: (date < '2021-05-03 10:00:00+00'::timestamp with time zone)
Rows Removed by Filter: 562
Planning Time: 0.576 ms
Execution Time: 339.250 ms
(30 lignes)

Event Timeline

olasd created this paste.Sep 13 2022, 1:29 PM
douardda added a subscriber: douardda.Sep 13 2022, 1:43 PM

FTR, using order by age():

guest@softwareheritage => explain analyze select * from origin_visit inner join origin on origin_visit.origin=origin.id where origin.url='https://pypi.org/project/cubicweb-preview/' order by abs(extract(epoch from age(date, '2020-01-01'))) limit 1;
                                                                       QUERY PLAN                                                                          
-------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=1978.19..1978.19 rows=1 width=89) (actual time=457.474..457.478 rows=1 loops=1)
   ->  Sort  (cost=1978.19..1978.21 rows=7 width=89) (actual time=457.472..457.474 rows=1 loops=1)
         Sort Key: (abs(date_part('epoch'::text, age(origin_visit.date, '2020-01-01 00:00:00+00'::timestamp with time zone))))
         Sort Method: top-N heapsort  Memory: 25kB
         ->  Nested Loop  (cost=1.27..1978.16 rows=7 width=89) (actual time=1.542..455.003 rows=814 loops=1)
               ->  Index Scan using origin_url_idx on origin  (cost=0.70..4.71 rows=1 width=53) (actual time=0.111..0.114 rows=1 loops=1)
                     Index Cond: (url = 'https://pypi.org/project/cubicweb-preview/'::text)
               ->  Index Scan using origin_visit_pkey on origin_visit  (cost=0.58..1963.05 rows=1034 width=28) (actual time=1.417..450.791 rows=814 loops=1)
                     Index Cond: (origin = origin.id)
 Planning Time: 1.032 ms
 Execution Time: 457.550 ms
(11 rows)
About · Developer information · Legal