有沒有辦法加快 DISTINCT 查詢？

我在數據庫（PostgreSQL 10.4）中有一個表 t：

\d t;
               Table "public.t"
 Column  |          Type          | Collation | Nullable | Default 
----------+------------------------+-----------+----------+---------
sn       | character varying(11)  |           |          | 
site     | character varying(50)  |           |          | 
Indexes:
   "site_2018_idx" btree (site), tablespace "indexspace"
   "sn_2018_idx" btree (sn), tablespace "indexspace"

我需要為特定站點找到不同的’sn’，我這樣做：

SELECT DISTINCT sn FROM t WHERE site='a_b301_1' ORDER BY sn ;

它可以工作，但是很慢，要返回 75 個不同的 ‘sn’ 值大約需要 8 分鐘！有沒有辦法加快速度？解釋分析給出以下輸出：

QUERY PLAN                                                                                 
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort  (cost=42873094.21..42873103.25 rows=3615 width=12) (actual time=190431.413..190431.417 rows=75 loops=1)
  Output: sn
  Sort Key: t.sn
  Sort Method: quicksort  Memory: 28kB
  ->  HashAggregate  (cost=42872844.42..42872880.57 rows=3615 width=12) (actual time=190431.233..190431.263 rows=75 loops=1)
        Output: sn
        Group Key: t.sn
        ->  Bitmap Heap Scan on public.t  (cost=874850.36..42695793.24 rows=70820471 width=12) (actual time=8755.163..168773.143 rows=43096912 loops=1)
              Output: sn, site
              Recheck Cond: ((t.site)::text = 'a_b301_1'::text)
              Heap Blocks: exact=783666
              ->  Bitmap Index Scan on site_2018_idx  (cost=0.00..857145.24 rows=70820471 width=0) (actual time=8540.835..8540.835 rows=43096912 loops=1)
                    Index Cond: ((t.site)::text = 'a_b301_1'::text)
Planning time: 0.466 ms
Execution time: 190433.289 ms
(15 rows)

附加資訊

在我按照建議創建另一個索引後，(site, sn)時間顯著減少，從 8 分鐘減少到 30 秒。太好了，我只是不明白為什麼會這樣？在這種情況下，一個多列索引比兩個單獨的索引好多少？

正如所建議的那樣，索引(site, sn)可以加快這一速度，特別是如果表是充分清空的，因此它可以從僅索引掃描中受益。

如果這還不足以加快速度，那麼有一種方法可以在“跳過掃描”或“鬆散索引掃描”中使用此索引來加速不同值的數量遠低於索引大小的查詢. 不幸的是，PostgreSQL 的規劃器不會自動檢測到這種情況的機會，但您可以通過編寫遞歸公用表表達式自己強制它。您可以在PostgreSQL wiki上找到有關此技術的討論，儘管您需要將其調整到您的參數化版本。

結果查詢非常難看，因此您可能希望將它們包裝到視圖中。或者在這種情況下，因為它是參數化的，所以變成了一個集合返回函式。像這樣的東西對我有用：

CREATE FUNCTION public.foobar(text) RETURNS SETOF text
   LANGUAGE sql
   AS $_$ with recursive r as (
 (select sn from t where site = $1 order by sn limit 1)
  union all
 SELECT (SELECT t.sn FROM t WHERE site=$1 and t.sn > r.sn ORDER BY sn LIMIT 1) from r where sn is not null
)
select * from r where r.sn is not null $_$;

設置表和索引：

create table t as select 
   floor(random()*1.2)::int::varchar as site, 
   (-floor(50*log(random()))::int)::varchar as sn 
from generate_series(1,10000000);

create index on t (site ,sn);

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