選擇distinct很慢-有解無憂

我有一個表，用于存盤帶有外部 ID 的行。我經常需要為給定的外部 ID 選擇最新的時間戳。現在它是我的應用程式的瓶頸

詢問：

SELECT DISTINCT ON ("T1"."external_id") "T1"."external_id", "T1"."timestamp" 
FROM "T1" 
WHERE "T1"."external_id" IN ('825889935', '825904511')
ORDER BY "T1"."external_id" ASC, "T1"."timestamp" DESC

解釋：

Unique  (cost=169123.13..169123.19 rows=12 width=18) (actual time=1327.443..1334.118 rows=2 loops=1)
   ->  Sort  (cost=169123.13..169123.16 rows=12 width=18) (actual time=1327.441..1334.112 rows=2 loops=1)
         Sort Key: external_id, timestamp DESC
         Sort Method: quicksort  Memory: 25kB
         ->  Gather  (cost=1000.00..169122.91 rows=12 width=18) (actual time=752.577..1334.056 rows=2 loops=1)
               Workers Planned: 2
               Workers Launched: 2
               ->  Parallel Seq Scan on T1  (cost=0.00..168121.71 rows=5 width=18) (actual time=921.649..1300.556 rows=1 loops=3)
                     Filter: ((external_id)::text = ANY ('{825889935,825904511}'::text[]))
                     Rows Removed by Filter: 1168882
 Planning Time: 0.592 ms
 Execution Time: 1334.159 ms

我該怎么做才能使這個查詢更快？或者我可能應該使用完全不同的查詢？

更新：

按照@jahrl 的要求添加了新的查詢計劃。看起來查詢速度更快，但以前的查詢計劃是在負載下制定的，現在它的作業時間相似

Finalize GroupAggregate  (cost=169121.80..169123.21 rows=12 width=18) (actual time=321.009..322.410 rows=2 loops=1)
   Group Key: external_id
   ->  Gather Merge  (cost=169121.80..169123.04 rows=10 width=18) (actual time=321.003..322.403 rows=2 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial GroupAggregate  (cost=168121.77..168121.86 rows=5 width=18) (actual time=318.671..318.672 rows=1 loops=3)
               Group Key: external_id
               ->  Sort  (cost=168121.77..168121.78 rows=5 width=18) (actual time=318.664..318.665 rows=1 loops=3)
                     Sort Key: external_id
                     Sort Method: quicksort  Memory: 25kB
                     Worker 0:  Sort Method: quicksort  Memory: 25kB
                     Worker 1:  Sort Method: quicksort  Memory: 25kB
                     ->  Parallel Seq Scan on T1  (cost=0.00..168121.71 rows=5 width=18) (actual time=144.338..318.611 rows=1 loops=3)
                           Filter: ((external_id)::text = ANY ('{825889935,825904511}'::text[]))
                           Rows Removed by Filter: 1170827
 Planning Time: 0.093 ms
 Execution Time: 322.441 ms

uj5u.com熱心網友回復：

也許一個基本的GROUP BY查詢會表現得更好？

SELECT "T1"."external_id", MAX("T1"."timestamp") as "timestamp"
FROM "T1" 
WHERE "T1"."external_id" IN ('825889935', '825904511')
GROUP BY "T1"."external_id"
ORDER BY "T1"."external_id" ASC

而且，正如@melcher 所說，不要忘記 ("external_id", "timestamp") 索引！

uj5u.com熱心網友回復：

查看過濾器洗掉的行數并在上創建索引external_id。

轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/383306.html

標籤：sql PostgreSQL的查询优化数据库性能

上一篇：PostgreSQL：按兩列值查找行的最佳方法是什么？

下一篇：SQL-省略重復表名