Postgresql

慢點陣圖堆掃描，通過殺死執行時間來排序

  • November 7, 2019

對錶使用者來說會變慢的查詢，表有 500 萬條記錄，為了提高查詢性能，我經常索引聚合數據的函式輸出，方便每種情況，函式輸出不是jsonbs。

Postgres 版本 10.1

架構：

CREATE TABLE users (
 id SERIAL PRIMARY KEY NOT NULL,
 social jsonb,
 flags text[],
 full_name text,
 email text,
 location jsonb,
 contact_info jsonb,
 created_at TIMESTAMP_WITHOUT TIME ZONE
);
CREATE INDEX available_channels_idx ON public.users USING gin (public.available_channels(social, contact_info));
CREATE INDEX mixed_frequent_locations_idx ON public.users USING gin (public.mixed_frequent_locations(location));
CREATE INDEX idx_in_social_follower_count ON public.users USING btree (public.social_follower_count(social) DESC NULLS LAST);
CREATE INDEX created_at_idx ON public.users USING btree (created_at);
CREATE INDEX idx_in_social_follower_count_and_created_at ON public.users USING btree (public.social_follower_count(social) DESC, created_at);

慢查詢（這裡 Bitmap Heap Scan 通過索引 Recheck 刪除太多行）：

EXPLAIN ANALYZE SELECT *  FROM users 
WHERE (social_follower_count(social) > '30000') 
 AND (engagement_level(social) <= 3) 
 AND (available_channels(social, contact_info) <@ array['yt']) 
 AND (has_emails(contact_info) = TRUE) 
 AND (not_business(social) = TRUE) 
 AND (array['United States'] <@ mixed_frequent_locations(location)) 
 AND (is_visible(social, flags) = TRUE)  
ORDER BY social_follower_count(social) DESC, "users"."created_at" ASC 
LIMIT 12 OFFSET 0;

此查詢返回 11616 個結果而不應用任何限制。查詢的變數輸入是數組

$$ ‘United States’ $$, has_emails(contact_info) = TRUE, 數組$$ ‘yt’ $$, social_follower_count(social) > ‘30000’ 查詢計劃：

Limit  (cost=59629.20..59629.23 rows=12 width=1531) (actual time=330055.413..330055.418 rows=12 loops=1)
  ->  Sort  (cost=59629.20..59629.69 rows=199 width=1531) (actual time=330055.411..330055.412 rows=12 loops=1)
        Sort Key: (social_follower_count(social)) DESC, created_at
        Sort Method: top-N heapsort  Memory: 65kB
        ->  Bitmap Heap Scan on users  (cost=24767.69..59624.63 rows=199 width=1531) (actual time=551.864..330000.716 rows=11616 loops=1)
              Recheck Cond: ((available_channels(social, contact_info) <@ '{yt}'::text[]) AND ('{"United States"}'::text[] <@ mixed_frequent_locations(location)) AND (social_follower_count(social) > 30000))
              Rows Removed by Index Recheck: 883451
              Filter: (has_emails(contact_info) AND not_business(social) AND is_visible(social, flags) AND (engagement_level(social) <= 3))
              Rows Removed by Filter: 9775
              Heap Blocks: exact=17001 lossy=132075
              ->  BitmapAnd  (cost=24767.69..24767.69 rows=6344 width=0) (actual time=422.660..422.660 rows=0 loops=1)
                    ->  Bitmap Index Scan on available_channels_idx  (cost=0.00..4046.06 rows=363475 width=0) (actual time=116.792..116.792 rows=442083 loops=1)
                          Index Cond: (available_channels(social, contact_info) <@ '{yt}'::text[])
                    ->  Bitmap Index Scan on mixed_frequent_locations_idx  (cost=0.00..5550.85 rows=617447 width=0) (actual time=143.090..143.090 rows=620980 loops=1)
                          Index Cond: ('{"United States"}'::text[] <@ mixed_frequent_locations(location))
                    ->  Bitmap Index Scan on idx_in_social_follower_count  (cost=0.00..15170.13 rows=821559 width=0) (actual time=132.214..132.215 rows=834091 loops=1)
                          Index Cond: (social_follower_count(social) > 30000)
Planning time: 0.534 ms
Execution time: 393793.472 ms

伺服器規格： 63GB RAM，Intel Core i7-6700K，250 GB SSD

增加 work_mem 會減少有損塊，但不會減少實際的 Recheck。

   Limit  (cost=59629.20..59629.23 rows=12 width=1531) (actual time=42330.685..42330.691 rows=12 loops=1)
 ->  Sort  (cost=59629.20..59629.69 rows=199 width=1531) (actual time=42330.662..42330.665 rows=12 loops=1)
       Sort Key: (social_follower_count(social)) DESC, created_at
       Sort Method: top-N heapsort  Memory: 65kB
       ->  Bitmap Heap Scan on users  (cost=24767.69..59624.63 rows=199 width=1531) (actual time=846.650..42281.071 rows=11616 loops=1)
             Recheck Cond: ((available_channels(social, contact_info) <@ '{yt}'::text[]) AND ('{"United States"}'::text[] <@ mixed_frequent_locations(location)) AND (social_follower_count(social) > 30000))
             Rows Removed by Index Recheck: 7149
             Filter: (has_emails(contact_info) AND not_business(social) AND is_visible(social, flags) AND (engagement_level(social) <= 3))
             Rows Removed by Filter: 9775
             Heap Blocks: exact=28018
             ->  BitmapAnd  (cost=24767.69..24767.69 rows=6344 width=0) (actual time=820.608..820.608 rows=0 loops=1)
                   ->  Bitmap Index Scan on available_channels_idx  (cost=0.00..4046.06 rows=363475 width=0) (actual time=207.050..207.050 rows=442083 loops=1)
                         Index Cond: (available_channels(social, contact_info) <@ '{yt}'::text[])
                   ->  Bitmap Index Scan on mixed_frequent_locations_idx  (cost=0.00..5550.85 rows=617447 width=0) (actual time=276.099..276.099 rows=620980 loops=1)
                         Index Cond: ('{"United States"}'::text[] <@ mixed_frequent_locations(location))
                   ->  Bitmap Index Scan on idx_in_social_follower_count  (cost=0.00..15170.13 rows=821559 width=0) (actual time=290.351..290.351 rows=834091 loops=1)
                         Index Cond: (social_follower_count(social) > 30000)
  Planning time: 20.168 ms
  Execution time: 42338.700 ms

更改設置 enable_bitmapscan=off; 改變了很多查詢計劃：

Limit  (cost=0.43..192792.76 rows=12 width=1526) (actual time=25.710..145.877 rows=12 loops=1)
  Buffers: shared hit=8508
    ->  Index Scan using idx_in_social_follower_count_and_created_at2 on users  (cost=0.43..3454196.26 rows=215 width=1526) (actual time=25.707..145.864 rows=12 loops=1)
      Index Cond: (social_follower_count(social) > 30000)
      Filter: (has_emails(contact_info) AND not_business(social) AND 
         is_visible(social, flags) AND (engagement_level(social) <= 3) 
         AND (available_channels(social, contact_info) <@ '{yt}'::text[]) AND ('{"United States"}'::text[] <@ mixed_frequent_locations(location)))
    Rows Removed by Filter: 346
    Buffers: shared hit=8508

計劃時間：0.830 ms 執行時間：145.949 ms

執行時間發生了巨大變化，是否可以根據查詢輸入啟用或禁用點陣圖掃描？

我也嘗試將 default_statistics_target 增加到更高的值 2000 和 1000，但沒有大的改進。查詢計劃保持不變：

   Limit  (cost=122386.75..122386.78 rows=12 width=1528) (actual time=106296.479..106296.484 rows=12 loops=1)
 Buffers: shared hit=5010705 read=408947 written=467
 ->  Sort  (cost=122386.75..122390.13 rows=1349 width=1528) (actual time=106296.477..106296.478 rows=12 loops=1)
       Sort Key: (social_follower_count(social)) DESC, created_at
       Sort Method: top-N heapsort  Memory: 61kB
       Buffers: shared hit=5010705 read=408947 written=467
       ->  Bitmap Heap Scan on users  (cost=45119.25..122355.83 rows=1349 width=1528) (actual time=556.192..106183.697 rows=41736 loops=1)
             Recheck Cond: ((available_channels(social, contact_info) <@ '{yt}'::text[]) AND ('{"United States"}'::text[] <@ mixed_frequent_locations(location)))
             Rows Removed by Index Recheck: 17700
             Filter: (has_emails(contact_info) AND not_business(social) AND is_visible(social, flags) AND (engagement_level(instagram) <= 3))
             Rows Removed by Filter: 20977
             Heap Blocks: exact=76531
             Buffers: shared hit=5010705 read=408947 written=467
             ->  BitmapAnd  (cost=45119.25..45119.25 rows=15170 width=0) (actual time=533.751..533.751 rows=0 loops=1)
                   Buffers: shared hit=4 read=5492
                   ->  Bitmap Index Scan on available_channels_idx  (cost=0.00..4094.86 rows=369981 width=0) (actual time=100.521..100.522 rows=442083 loops=1)
                         Index Cond: (available_channels(social, contact_info) <@ '{yt}'::text[])
                         Buffers: shared hit=3 read=105
                   ->  Bitmap Index Scan on mixed_frequent_locations_idx  (cost=0.00..5576.65 rows=620353 width=0) (actual time=156.311..156.311 rows=620980 loops=1)
                         Index Cond: ('{"United States"}'::text[] <@ mixed_frequent_locations(location))
                         Buffers: shared hit=1 read=141
                   ->  Bitmap Index Scan on idx_in_contact_info_has_emails  (cost=0.00..35446.23 rows=1919173 width=0) (actual time=243.802..243.803 rows=1918927 loops=1)
                         Index Cond: (has_emails(contact_info) = true)
                         Buffers: shared read=5246
Planning time: 13.691 ms
Execution time: 106296.586 ms

條件選擇性分析：

(social_follower_count(social) > '30000')  - 834091 records
(engagement_level(social) <= 3) - 4311859 records
(available_channels(social, contact_info) <@ array['yt']) - 342815 records
(has_emails(contact_info) = TRUE) - 1918927 records
(not_business(social) = TRUE) -  3869626 records
(array['United States'] <@ mixed_frequent_locations(location)) - 620980 records
(is_visible(social, flags) = TRUE) - 4959302 records

正如@jjanes 建議刪除該子句social_follower_count(social) > 30000，查詢計劃發生了巨大變化，現在應用了全索引掃描並且不應用點陣圖堆掃描。

Limit  (cost=0.43..88024.52 rows=12 width=1436) (actual time=20.779..181.868 rows=12 loops=1)
  ->  Index Scan using idx_in_social_follower_count_and_created_at on users  (cost=0.43..9543278.28 rows=1301 width=1436) (actual time=20.777..181.858 rows=12 loops=1)
        Filter: (has_emails(contact_info) AND not_business(social) AND is_visible(social, flags) AND (engagement_level(social) <= 3) AND (available_channels(social, contact_info) <@ '{yt}'::text[]) AND ('{"United States"}'::text[] <@ mixed_frequent_locations(
location)))
        Rows Removed by Filter: 347
Planning time: 0.523 ms
Execution time: 181.963 ms

從 postgresql 9.1 升級到 9.6 後，我遇到了同樣的問題，包括禁用 enable_bitmapscan 時性能更好。

您可能正在使用低 default_statistics_target 執行：

SHOW default_statistics_target ;

對於新的 postgresql 版本，這應該增加，因為許多新的查詢計劃功能使預設值 100 荒謬，嘗試在啟用 enable_bitmapscan 的情況下增加它，然後再次嘗試查詢，直到它選擇最佳查詢計劃。

您可以通過發出以下命令嘗試不同的值集：

set default_statistics_target to '2000'; --for example --this is a reasonable value for most of databases...

進而 ：

analyze;  --(or vacuum analyze)

然後一次又一次地執行您的查詢，直到您找到最適合您的統計目標…

然後您可以將預設值放入 postgresql.conf 並重新載入/重新啟動服務。

需要重新檢查，因為完整的點陣圖無法放入允許的記憶體中，因此它必須通過僅儲存塊而不是塊和塊內的行偏移量來使用有損壓縮。增加“work_mem”的設置，使整個點陣圖適合記憶體，即直到不再有“Heap Blocks:…lossy=…”。

現在，這可能還不夠好，但至少它會有一個戰鬥的機會。

看起來上面的幫助大約是 10 倍，但仍然不夠好。

當然可以set enable_bitmapscan = off在特定查詢之前，然後reset enable_bitmapscan在該查詢之後。這樣做的潛在問題是，一些試圖抽像出 SQL 的程式框架使您很難掌握查詢以做到這一點；並且很難知道您需要該設置用於哪些查詢。如果 PostgreSQL 規劃器很好地理解了這一點，那麼你一開始就不會遇到這個問題；所以你在那裡有點自己。

根本問題可能是這一行：

->  Bitmap Heap Scan on users  (cost=24767.69..59624.63 rows=199 width=1531) (actual time=846.650..42281.071 rows=11616 loops=1)

它找到的行數是預期的 60 倍。造成這種情況的原因可能是列之間的相關性，例如擁有 YouTube 頻道的人比隨機機會建議的更受歡迎。在較新版本的 postgres 中，有一種方法可以收集有關跨列相關性的統計資訊，但這在這裡對您不起作用，因為（目前）它們必須是實際的列，而不是表達式，而且我認為它只適用在標量上，而不是數組上。

一種想法是刪除social_follower_count(social) > '30000'查詢的一部分。既然您已經按該值排序並且只選擇了前 12 名，那麼是否真的有必要也限制數量限制？如果您省略了該部分，它將不再具有與其他值相關的能力，因此會導致估計問題。

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

相關問答

Postgresql

優化對 690,000 行表的昂貴的 GROUP BY / ORDER BY 查詢

  • March 3, 2022
檢視問答
Postgresql

為什麼我的索引 DISTINCT ON 比我的 INNER JOIN 慢得多？

  • May 18, 2020
檢視問答
Postgresql

優化點陣圖堆掃描

  • August 2, 2018
檢視問答
Postgresql

如何優化 Postgres 查詢中的排序

  • July 30, 2015
檢視問答
Postgresql

優化查詢以在分區表中跨多天獲取數據

  • August 4, 2022
檢視問答
Postgresql

FROM 子句中的相關函式是否針對每一行執行？

  • July 10, 2022
檢視問答