“內部” Group By Query 順序掃描

我在左連接中有內部查詢，它使用順序掃描而不是索引掃描，我不知道如何改進它。

我有兩張桌子：

• 頁面 - 列：id
• 共享 - 每個頁面有許多共享，列：page_id，view_counter

share 在 page_id 上有一個索引，shares 是一個大表。

查詢是：

SELECT id, 
      shared.views     views_count, 
      is_shared, 
      shared.views > 0 AS tracked 
FROM   pages 
      LEFT JOIN (SELECT shares.page_id, 
                        Sum(shares.view_counter) 
                 FROM   shares 
                 GROUP  BY page_id) AS shared 
             ON shared.page_id = pages.id
WHERE page_id IN (1, 2, 3) 

此查詢的查詢計劃是：

Merge Right Join  (cost=813559.28..895407.83 rows=4 width=21)
 Merge Cond: (shares.page_id = pages.id)
 ->  GroupAggregate  (cost=813558.85..867374.64 rows=2239970 width=8)
       Group Key: shares.page_id
       ->  Sort  (cost=813558.85..824030.88 rows=4188812 width=8)
             Sort Key: shares.page_id
             ->  Seq Scan on shares  (cost=0.00..238288.12 rows=4188812 width=8)
 ->  Index Scan Backward using index_pages_on_id_desc on pages  (cost=0.43..33.53 rows=4 width=13)
       Index Cond: (id = ANY ('{1,2,3}'::integer[]))

如何重新編寫查詢，這樣我就不會對共享進行順序掃描？

我認為這是使用LATERALjoin 的好選擇：

SELECT id, 
      shared.views     AS views_count, 
      is_shared, 
      shared.views > 0 AS tracked 
FROM   pages 
      LEFT JOIN LATERAL
                (SELECT sum(shares.view_counter) AS views
                 FROM   shares 
                 WHERE  shares.page_id = pages.id
                ) AS shared 
             ON TRUE
WHERE pages.id IN (1, 2, 3) ;

引用自：https://dba.stackexchange.com/questions/205876