PostgreSQL執行計劃:Bitmap scan VS index only scan

来源:https://www.cnblogs.com/wy123/archive/2022/04/04/16088442.html
-Advertisement-
Play Games

之前瞭解過postgresql的Bitmap scan,只是粗略地瞭解到是通過標記數據頁面來實現數據檢索的,執行計劃中的的Bitmap scan一些細節並不十分清楚。這裡藉助一個執行計劃來分析bitmap scan以及index only scan,以及兩者的一些區別。這裡有關於Bitmap sca ...


之前瞭解過postgresql的Bitmap scan,只是粗略地瞭解到是通過標記數據頁面來實現數據檢索的,執行計劃中的的Bitmap scan一些細節並不十分清楚。這裡藉助一個執行計劃來分析bitmap scan以及index only scan,以及兩者的一些區別。
這裡有關於Bitmap scan的一些實現過程,https://dba.stackexchange.com/questions/119386/understanding-bitmap-heap-scan-and-bitmap-index-scan

0. 構建測試環境

PG版本為11,如下測試腳本,構建一個簡單的測試表

create table my_test_table01
(
    c1     serial not null primary key,
    c2    varchar(100),
    c3     timestamp 
)
--c3欄位上建索引
create index ix_c3 on my_test_table01(c3);

truncate table my_test_table01;

--寫入300W行測試數據,c3列生成隨機時間
insert into my_test_table01 (c2,c3)
select  uuid_generate_v1(),NOW() - (random() * (NOW()+'1000 days' - NOW())) from generate_series(1,3000000);

 

1. Bitmap Scan的剖析

用最最容易理解的場景來測試Bitmap Index Scan,執行如下sql,來分析bitma scan這個執行計劃的含義。

explain (analyze, buffers,verbose,timing)
select count(1) from my_test_table01 a 
where a.c3 >'20220328' or a.c1 < 100;

對以上的執行計劃,有幾個問題先弄清楚:
1,Bitmap Index Scan做了什麼?
2,Bitmap Heap Scan做了什麼?
3,Recheck Cond的目的是什麼?

第一個問題:Bitmap Index Scan做了什麼?
Bitmap scan的目標是一個bit數組,bit數組中的每一位映射到表中的一個數據頁IdOne bit per heap page, in the same order as the heap)。
Bitmap Index Scan對於所有的查詢條件,從掃描索引的所有頁面,如果數據頁面中有符合條件的數據,那麼就將bit為標記為1,否則標記為0。
其他的查詢條件依次創建一個一樣的bit數組,同樣掃描索引的所有頁面,將符合條件的page的bit位標記為1。
最後多個條件生成的多個bit數組進行與(或)操作(取決於where多個條件是and組合或者or組合,上面截圖中的BitmapOr),合併成一個最終的bit數組。
此時最終的bit數組標記的符合條件的數據頁,而不是最終的數據行,所以最終還要去數據頁中進行篩選。

第二個問題:Bitmap Heap Scan做了什麼
而BitMap Index Scan一次性將滿足條件的索引項全部取出,併在記憶體中進行排序, 然後根據排序後的索引項訪問表數據,也就是執行計劃中的Bitmap Heap Scan。
bitmap index scan 內部優化機制:https://www.postgresql.org/message-id/[email protected]

第三個問題:Recheck Cond的目的是什麼
BitMap Heap Scan指示找到符合條件的數據頁面,而不是具體的記錄,此時找到數據頁後再用where條件進行篩選其中的數據行,也就是執行計劃中的Recheck Cond。
https://stackoverflow.com/questions/50959814/what-does-recheck-cond-in-explain-result-mean
If the bitmap gets too large we convert it to "lossy" style, in which we only remember which pages contain matching tuples instead of remembering each tuple individually. When that happens, the table-visiting phase has to examine each tuple on the page and recheck the scan condition to see which tuples to return.


bitmap scan示例圖
postgresql bitmap scan
圖片來源:https://www.cybertec-postgresql.com/en/postgresql-indexing-index-scan-vs-bitmap-scan-vs-sequential-scan-basics/
bitmap index scan不僅僅發生在where條件中有多個篩選條件的場景(比如where c1 = m and c2 =n),其實對於一個條件的範圍查詢,也同樣適用bitmap index scan,見下例。

 

2. 為什麼執行計划走Bitmap Index Scan,而不是Index only Scan?

對於如下這個查詢,表中有300W測試數據符合條件的數據比例很少,很明顯,ix_c3上的索引掃描才是更優化的執行計劃,為什麼在預設情況下是Bitmap Index Scan?

select count(1) from my_test_table01 a 
where a.c3 >'20220328' ;

從如下截圖可以看到,vacuum是打開的,在造完測試數據後,預設情況下上述sql查詢走了bitmap Index scan,因為c3上有索引,預期是走ix_c3上的索引。
原本以為vacuum是非同步的,或者說有滯後性,但是這個case在測試數據構造完之後幾個小時甚至幾天,該查詢都依舊走bitmap Index scan的方式。
當關閉enable_bitmapscan和enable_seqscan,強制優化器走ix_c3上的index only scan,代價明顯更大,這就有點說不通了,原因下文會具體分析。

本人對該現象一開始也是百思不得其解,難道是bitmap scan有什麼魔法?

看到這裡有一個提到這個問題:https://www.datadoghq.com/blog/postgresql-vacuum-monitoring/,裡面相關的內容的是這麼說的:
1. Large insert-only tables.  Large insert-only tables are not automatically vacuumed (except for transaction-ID wraparound), because autovacuum is triggered by updates and deletes.  This is generally a good thing, because it saves a great deal of not-very-useful work.  However, it's problematic for index-only scans, because it also means the visibility map bits won't get set.  I don't have a very clear idea what to do about this, but it's likely to need thought and work.  For a first version of this feature, we'll likely have to rely on users to do a manual VACUUM in this case.

既然這種場景無法主從出發vacuum,那麼這裡就手動vacuum測試表,然後打開bitmap scan選項,繼續觀察此時的預設情況下,該查詢是不是可以走index only scan,這一次終於是預期的ix_c3上的index only scan了。

同時還有一個疑問:對錶執行vacuum前後,index only scan的shared hit差別這麼大?
上述得知在large-insert的情況下,不會觸發表上的vacuum,此時如果強制使用index only scan,因為索引上的沒有數據行的可見性信息(Index Only Scan operation must visit the heap to check if the row is visible.)所以在vacuum之前,強制使用index only scan的過程中,對於任何一行數據都要回表進行可見性判斷,因此會產生大量的shared hit。一旦vacuum之後,由於索引上更新了數據行的可見性,不需要回表判斷,因此shared hit會大幅度地降低。

 

3. 主動觸發vacuum.
 Large insert-only tables are not automatically vacuumed,也就是大批量的插入無法主動發出vacuum,vacuum由update和delete產生,那麼嘗試對錶執行一些update或者delete操作,會不會主動觸發vacuum?
基於第一步的腳本,重新初始化測試表,在插入300W行數據後,刪除其中一部分數據,目前是讓delete操作觸發vacuum,然後再通過執行計劃,觀察是否會想手動vacuum一樣,走index only scan。
經過三次刪除,完美觸發vacuum,執行計劃有一開始bitmap scan更新為index only scan。

 

4. bitmp index scan VS index-only scan
參考這裡https://www.cybertec-postgresql.com/en/killed-index-tuples/ 對 bitmap  index scan 和 index-only scan的解釋

PostgreSQL 8.1 introduced the “bitmp index scan”. This scan method first creates a list of heap blocks to visit and then scans them sequentially. 
This not only reduces the random I/O, but also avoids that the same block is visited several times during an index scan. PostgreSQL 9.2 introduced the “index-only scan”, which avoids fetching the heap tuple.
This requires that all the required columns are in the index and the “visibility map” shows that all tuples in the table block are visible to everybody.

bitmp index scan不僅可以避免隨機的IO操作,而且可以避免同一個頁面(在一個查詢執行過程中)被重覆讀取(一個頁面中可能存在多條滿足查詢條件的元組,其他方式可能會多次讀取同一個頁面)。
index-only scan避免了從堆中讀取數據,但是他要求所有請求的欄位都在索引中,並且“visibility map” 中顯示表塊中的所有元組對所有事物都是可見的,但是索引中並不包含元組的可見性。

 

本文通過一個看似不起眼的問題sql執行計劃的分析,嘗試分析bitmap scan 和index only scan的差異以及選擇二者的原因,同時會涉index索引元組的可見性及vacuum沒有觸發的一些特殊場景。一個問題往往不是一個點,是一系列問題的合集,此事要躬行。

參考鏈接:

https://stackoverflow.com/questions/55651068/why-is-bitmap-scan-faster-than-index-scan-when-fetching-a-moderately-large-perce
https://ask.use-the-index-luke.com/questions/148/why-is-this-postgres-query-doing-a-bitmap-heap-scan-after-the-index-scan
http://rhaas.blogspot.com/2010/11/index-only-scans.html



您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 三類設計模式的對比 英文名 設計模式數量 用途、意義 創建型模式 Creational Pattern 5 創建型模式關註對象的創建過程,將對象的創建和使用分離,降低系統耦合度,讓設計方案更易於修改和擴展 結構型模式 Structural Pattern 7 結構型模式關註如何將類或對象組織在一起, ...
  • 問題描述 考慮這樣一個需求:畫布上的對象支持手勢操作,手勢操作模式有平移、縮放、旋轉,對象可以支持一種或多種手勢,如何定義這個手勢操作模式? 就像文件的許可權一樣,只讀、只寫、讀寫,手勢操作也可以這樣設計。將手勢操作模式定義為簡單的枚舉類型是不夠的,我們需要表示不同模式的組合,需要支持位運算,因此每個 ...
  • 系統調用 系統調用: 操作系統提供給用戶程式調用的一組“特殊”介面,用戶程式可以通過這組“特殊”介面來獲得操作系統內核提供的服務 為什麼用戶程式不能直接訪問系統內核提供的服務為了更好地保護內核空間,將程式的運行空間分為 內核空間 和 用戶空間(也就是常稱的內核態和用戶態),它們分別運行在不同的級別上 ...
  • Ubuntu系統報錯:The system is running in low-graphics mode 我遇到過兩次這種請況,這次解決了。很nice! 在csdn上搜到的大部分操作是: 滑鼠進入系統 使用快捷鍵 Ctrl+Alt+F1 進入用戶 輸入密碼 然後按照以下代碼進行 cd /etc/X ...
  • 在介紹網路模式之前,關於網路的幾個簡單命令的使用 ifup eth0 //啟動網卡eth0 ifdown eth0 //關閉網卡eth0 /etc/network/interfaces //網路配置文件 /etc/init.d/networking //網路服務位置 /etc/init.d/netw ...
  • 一、Hadoop概述 Hadoop是Apache軟體基金會下一個開源分散式計算平臺,以HDFS(Hadoop Distributed File System)、MapReduce(Hadoop2.0加入了YARN,Yarn是資源調度框架,能夠細粒度的管理和調度任務,還能夠支持其他的計算框架,比如sp ...
  • 這兩天都是在跟文件打交道,很有趣,每一步都不會順心如意,但每一步的解決都有所獲益,首先是對文件變化的監測,能找到很多辦法,例如通過ELK家族的Filebeat工具來探測,但是外部工具不好融合進Storm,最好是自己寫Java程式來監測。 引入Java NIO 監控文件 其實jdk7以上版本就有一個比 ...
  • 一、概述 Hadoop是Apache軟體基金會下一個開源分散式計算平臺,以hdfs(Hadoop Distributed File System)、MapReduce(Hadoop2.0加入了YARN,Yarn是資源調度框架,能夠細粒度的管理和調度任務,還能夠支持其他的計算框架,比如spark)為核 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發(一):使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說,近來想嘗試開發一款安卓APP,考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP,畢竟是使用Visual Studio開發工具,使用起來也比較的順手,結合微軟官方的教程進行了安卓 ...
  • 前言 QuestPDF 是一個開源 .NET 庫,用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫,它徹底改變了我們生成 PDF 文檔的方 ...
  • 項目地址 項目後端地址: https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址: ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址: http://tree ...
  • 話不多說,直接開乾 一.下載 1.官方鏈接下載: https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe,運行開始下載SQL server; 二. ...
  • 前言 隨著物聯網(IoT)技術的迅猛發展,MQTT(消息隊列遙測傳輸)協議憑藉其輕量級和高效性,已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫,為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog支持多種接收器用於日誌存儲,增強器用於添加屬性,LogContext管理動態屬性,支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項,適用於不同需求。 ...
  • 目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據,爬取信息時需要模擬瀏覽器行為,否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下: 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • 1.前言 什麼是熱更新 游戲或者軟體更新時,無需重新下載客戶端進行安裝,而是在應用程式啟動的情況下,在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR,Xlua,ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles,Addressable, ...
  • 本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺,其實像阿裡雲,騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後,它會送 ...
  • 通過以下方式可以高效,並保證數據同步的可靠性 1.API設計 使用RESTful設計,確保API端點明確,並使用適當的HTTP方法(如POST用於創建,PUT用於更新)。 設計清晰的請求和響應模型,以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證,確保接收到的數據符合預期格 ...