Optimize performance of depended_by_gems view by replacing improper join with subquery

[xmisao]

I discovered an extremely slow query, so I am modifying the view definition. The execution plans before and after the changes are as follows.

Before

> EXPLAIN ANALYZE SELECT * FROM "depended_by_gems" WHERE "gem_id" = 1 ORDER BY "total_ranking" LIMIT 10;
                                                                                         QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=2406.54..2406.54 rows=1 width=30) (actual time=3145.787..3145.789 rows=0 loops=1)
   ->  Sort  (cost=2406.54..2406.54 rows=1 width=30) (actual time=3145.778..3145.781 rows=0 loops=1)
         Sort Key: gems.latest_total_ranking
         Sort Method: quicksort  Memory: 25kB
         ->  Nested Loop  (cost=0.85..2406.53 rows=1 width=30) (actual time=3145.511..3145.513 rows=0 loops=1)
               ->  Nested Loop  (cost=0.42..867.97 rows=406 width=30) (actual time=0.353..775.751 rows=180689 loops=1)
                     ->  Seq Scan on master  (cost=0.00..1.01 rows=1 width=4) (actual time=0.043..0.046 rows=1 loops=1)
                     ->  Index Scan using gems_latest_update_date_latest_total_ranking_index on gems  (cost=0.42..862.90 rows=406 width=30) (actual time=0.299..667.214 rows=180689 loops=1)
                           Index Cond: (latest_update_date = master.date)
               ->  Index Scan using dependencies_gem_id_index on dependencies  (cost=0.43..3.78 rows=1 width=8) (actual time=0.013..0.013 rows=0 loops=180689)
                     Index Cond: (gem_id = gems.id)
                     Filter: (depend_on_gem_id = 1)
                     Rows Removed by Filter: 4
 Planning Time: 48.327 ms
 Execution Time: 3146.425 ms
(15 rows)

After

> EXPLAIN ANALYZE SELECT * FROM depended_by_gems WHERE gem_id = 1 ORDER BY total_ranking LIMIT 10;
                                                                               QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=3413.60..3413.60 rows=1 width=30) (actual time=0.064..0.066 rows=0 loops=1)
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.00..1.01 rows=1 width=4) (never executed)
           ->  Seq Scan on master  (cost=0.00..1.01 rows=1 width=4) (never executed)
   ->  Sort  (cost=3412.59..3412.59 rows=1 width=30) (actual time=0.063..0.064 rows=0 loops=1)
         Sort Key: gems.latest_total_ranking
         Sort Method: quicksort  Memory: 25kB
         ->  Hash Join  (cost=868.41..3412.58 rows=1 width=30) (actual time=0.033..0.033 rows=0 loops=1)
               Hash Cond: (dependencies.gem_id = gems.id)
               ->  Index Scan using dependencies_depend_on_gem_id_index on dependencies  (cost=0.43..2542.67 rows=735 width=8) (actual time=0.032..0.032 rows=0 loops=1)
                     Index Cond: (depend_on_gem_id = 1)
               ->  Hash  (cost=862.90..862.90 rows=406 width=30) (never executed)
                     ->  Index Scan using gems_latest_update_date_latest_total_ranking_index on gems  (cost=0.42..862.90 rows=406 width=30) (never executed)
                           Index Cond: (latest_update_date = $0)
 Planning Time: 11.323 ms
 Execution Time: 0.218 ms
(16 rows)