softwareheritage=> explain select * from origin_visit ov INNER JOIN origin o ON o.id = ov.origin WHERE o.url = 'svn://scm.gforge.inria.fr/svnroot/fpg-ipc/' ORDER BY ov.visit LIMIT 1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Limit (cost=633.57..633.57 rows=1 width=89)
-> Sort (cost=633.57..633.59 rows=7 width=89)
Sort Key: ov.visit
-> Nested Loop (cost=0.58..633.53 rows=7 width=89)
-> Index Scan using origin_url_idx1 on origin o (cost=0.00..2.02 rows=1 width=53)
Index Cond: (url = 'svn://scm.gforge.inria.fr/svnroot/fpg-ipc/'::text)
-> Index Scan using origin_visit_pkey on origin_visit ov (cost=0.58..625.15 rows=637 width=28)
Index Cond: (origin = o.id)
For comparison, when removing the join:
softwareheritage=> explain select * from origin_visit ov WHERE ov.origin=162351914 ORDER BY ov.visit DESC LIMIT 1;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Limit (cost=0.58..1.56 rows=1 width=28)
-> Index Scan Backward using origin_visit_pkey on origin_visit ov (cost=0.58..625.15 rows=637 width=28)
Index Cond: (origin = 162351914)