你的 lib 能啟用 Binary Representation 傳遞參數與結果嗎?

Image for post
Image for post

此文章也有於 Golang Taipei Gathering #50 x Dcard 分享的版本,其中差異在於改用 wireshark 展示並補充 Connection Pool 細節:
https://speakerdeck.com/rueian/golang-postgresql-libraries-comparison-with-wireshark

SQL 提供的抽象化讓我們可以很輕鬆的撰寫聲明式的語法,讓資料庫用它認為比較好的方式去幫我們撈資料,這也是 SQL 資料庫熱門的原因之一。

這層抽象偶爾也會帶來一些隱藏的小坑,多半與當下 Query Planner 的實現有關係,例如我前兩篇文章:

除此之外,你有沒有注意到某些情景之下 SQL 本身的 Text Representation 也會造成一些無法忽視的頻寬與效能的浪費呢?

案例

若我們用 PostgreSQL 做一個通用的 kv 儲存…

至於為什麼選用 PostgreSQL,只是因為 kv 儲存比較容易拿來說明。而在 Production 上選用 PostgreSQL 做 kv 也可以有很多理由,例如:

  1. 有 Range Query 需求,用 PostgreSQL 比較容易
  2. 搭配其他既有的基礎建設,比方說定期快照、Change Data Capture…

要做一個 kv 儲存,我們可以開一張這樣的表來實作:

CREATE TABLE kv (
key VARCHAR PRIMARY KEY COLLATE "C",
value BYTEA,
updated_at TIMESTAMP WITH TIME ZONE
);
-- 選用 COLLATE "C" 可以支援 Prefix Range Query
-- 選用 BYETA 儲存 value 可以讓使用者自行決定資料格式,甚至不同的壓縮方法

若我們要往裡面插入一筆 key=foo value=bar 資料,可以寫這樣的 SQL:

INSERT INTO kv (key, value, updated_at) VALUES 
('foo', '\x626172', NOW()) RETURNING…


使用 LIKE 前,先看看 like_support.c

Image for post
Image for post

較好的閱讀體驗可看 Github Issue 版本:https://github.com/rueian/postgres/issues/2

接續在前篇文章中提過,底層 scan node 的 row estimation 結果以及上層其他 plan node 操作會綜合影響資料庫最後使用哪一個 plan,所以 scan node 的 row estimation 相當重要。

前篇:PostgreSQL 如何估算 HashAggregate 的 Return Rows ,以及低估的後果

其中一個很重要的步驟就是預估 Return Rows 的數量,它被用來:
1. 給底層的 Scan Node 比較各種 Access Method (Seq Scan, Index Scan, Bitmap Index Scan + Bitmap Heap Scan, …) 的 Cost

2. 往往更重要的是 Return Rows 的估算對於上層其他 Plan Node 的 Cost 有巨大影響,例如 Nested LoopHash Join,因為預估它們就是會從底下的 Scan Node 拿出這麼多的 rows 數量來處理

這次我們遇到的案例則是 LIKE 的 row estimation 與實際差很多,想要一探究竟

案例

我們有一張表經常用 LIKE 進行 prefix pattern matching 查詢,表的結構如下:

注意 我們在 id 上面設定 COLLATE “C”是為了要讓 PostgreSQL 能直接使用 PRIMARY KEY 的 B-tree 索引就能縮小 prefix pattern matching 的範圍,否則得額外建立一個 text_pattern_ops 的 B-tree…


相信 Query Planner 前,先看看 selfuncs.h

Image for post
Image for post

較好的閱讀體驗可看 Github Issue 版本:https://github.com/rueian/postgres/issues/1

PostgreSQL 資料庫是怎麼決定如何執行 SQL 的呢?其中一個很重要的步驟就是預估 Return Rows 的數量,它被用來:

  1. 往往更重要的是 Return Rows 的估算對於上層其他 Plan Node 的 Cost 有巨大影響,例如 Nested Loop 或 Hash Join,因為預估它們就是會從底下的 Scan Node 拿出這麼多的 rows 數量來處理

最近我們同事在開發時就遇到 HashAggregate Node 的 Retrun Rows 數量被嚴重低估,導致資料庫最終選到一個費時比預估更長的執行路徑。

HashAggregate Node 是做什麼的?

其實我們常常可以在 PostgreSQL 的 EXPLAIN 輸出中看到 HashAggregate Node 的蹤影,它是我們在使用 DISTINCT, GROUP BY, WHERE IN 等 SQL 操作時負責幫我們做聚合操作的 Node。

/src/backend/executor/nodeAgg.c

案例

我們的案例是想要找出所有訂閱某個 playlist 的使用者裝置,步驟是:

  1. 再從 devices 表去 WHERE IN 剛剛的 CTE,找出這些訂閱者的裝置

實際的 Query Plan:

從上面的 EXPLAIN 可以注意到,原本預估會從 playlist_subscriptions 這張表取出 23188 個 rows,但是經過 HashAggregate 這層之後,變成預估只取出 200…

About

Rui An Huang

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store