Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Features
Speaker Deck
PRO
Sign in
Sign up for free
Search
Search
How Postgres Could Index Itself
Search
Andrew Kane
September 08, 2017
Programming
0
3.1k
How Postgres Could Index Itself
Andrew Kane
September 08, 2017
Tweet
Share
Other Decks in Programming
See All in Programming
マルチアカウント環境での、そこまでがんばらない RI/SP 運用設計
wa6sn
0
290
php-fpm がリクエスト処理する仕組みを追う / Tracing-How-php-fpm-Handles-Requests
shin1x1
4
770
SideKiqでジョブが二重起動した事象を深堀りしました
t_hatachi
0
190
CRE Meetup!ユーザー信頼性を支えるエンジニアリング実践例の発表資料です
tmnb
0
150
Compose Navigation実装の見通しを良くする
hiroaki404
0
170
AHC 044 混合整数計画ソルバー解法
kiri8128
0
290
20250326_生成AIによる_レビュー承認システムの実現.pdf
takahiromatsui
17
4.8k
List とは何か? / PHPerKaigi 2025
meihei3
0
450
Going Structural with Named Tuples
bishabosha
0
150
WordPress Playground for Developers
iambherulal
0
120
PsySHから紐解くREPLの仕組み
muno92
PRO
1
510
CQRS+ES勉強会#1
rechellatek
0
380
Featured
See All Featured
Art, The Web, and Tiny UX
lynnandtonic
298
20k
Scaling GitHub
holman
459
140k
YesSQL, Process and Tooling at Scale
rocio
172
14k
Refactoring Trust on Your Teams (GOTO; Chicago 2020)
rmw
34
2.9k
Building Adaptive Systems
keathley
40
2.5k
Visualizing Your Data: Incorporating Mongo into Loggly Infrastructure
mongodb
45
9.5k
Statistics for Hackers
jakevdp
797
220k
Code Review Best Practice
trishagee
67
18k
XXLCSS - How to scale CSS and keep your sanity
sugarenia
248
1.3M
What’s in a name? Adding method to the madness
productmarketing
PRO
22
3.4k
Fashionably flexible responsive web design (full day workshop)
malarkey
406
66k
GraphQLとの向き合い方2022年版
quramy
45
14k
Transcript
How Postgres Could Index Itself
None
github.com/ankane
None
Read speed vs Write speed Space
None
v1
Collect queries Analyze queries
pg_stat_statements Query Total Time (ms) Calls Average Time (ms) SELECT
… 40,000 80,000 0.5 SELECT … 30,000 300 100
SELECT * FROM products WHERE store_id = 1
pg query github.com/lfittl/pg_query _
SELECT * FROM products WHERE store_id = 1
SELECT * FROM products WHERE store_id = 1 AND brand_id
= 2
Stores have many products Brands have a few products
id store_id brand_id 1 1 2 2 4 8 3
1 9 4 1 3 fetch store_id = 1 id store_id brand_id 1 1 2 2 4 8 3 1 9 4 1 3 filter brand_id = 2 id store_id brand_id 1 1 2 2 4 8 3 1 9 4 1 3 id store_id brand_id 1 1 2 2 4 8 3 1 9 4 1 3 filter store_id = 1 fetch brand_id = 2
pg_stats n_distinct null_frac
store_id brand_id Rows 100,000 100,000 null_frac 0 0.10 n_distinct 100
9,000 Estimated Rows 1,000 10
store_id brand_id Rows 100,000,000 100,000,000 null_frac 0 0.10 n_distinct 100
9,000 Estimated Rows 1,000,000 10,000
store_id Rows 10,000 null_frac 0 n_distinct 100 Estimated Rows 100
SELECT * FROM products ORDER BY created_at DESC LIMIT 10
SELECT * FROM products WHERE store_id = 1 ORDER BY
created_at DESC LIMIT 10
None
Shortcomings
Single table plus Simple WHERE clause and/or Simple ORDER BY
clause
Duplicating planner logic
pg_stats n_distinct null_frac ✗ most_common_vals ✗ most_common_freqs ✗ histogram_bounds
most_common_vals {2, 5, 1} most_common_freqs {0.9, 0.05, 0.01} store_id =
1 vs store_id = 2
histogram_bounds {0, 9, 25, 60, 99} qty < 5 vs
qty > 5
SELECT * FROM products WHERE store_id = ?
v2
log_min_statement_duration duration: 100 ms statement: SELECT * FROM products WHERE
store_id = 1
Given a query and a set of indexes best indexes
to use
Given a query and all possible indexes best indexes possible
/* Allow a plugin to editorialize on the info we
obtained from the catalogs. Actions might include altering the assumed relation size, removing an index, or adding a hypothetical index to the indexlist. */ get_relation_info_hook 604ffd2
hypopg github.com/dalibo/hypopg
SELECT * FROM products WHERE store_id = 1 AND brand_id
= 2
EXPLAIN Seq Scan on products (cost=0.00..1000.00 rows=100 width=108) Filter: (store_id
= 1 AND brand_id = 2) Final Cost
Cost Hypothetical Indexes Original 1000
Add hypothetical indexes store_id brand_id
EXPLAIN Index Scan using <41072>hypo_btree on products (cost=0.28..50.29 rows=1 width=108)
Index Cond: (brand_id = 2) Filter: (store_id = 1) Final Cost Index
Cost Hypothetical Indexes Original 1000 Single Column 50 brand_id
Add hypothetical indexes store_id, brand_id brand_id, store_id (does not try
different sort orders right now)
Cost Hypothetical Indexes Original 1000 Single Column 50 brand_id Multi
Column 45 brand_id, store_id
Dexter github.com/ankane/dexter
tail -F -n +1 <log-file> | dexter <conn-opts>
--create --exclude big_table --min-time 10
Shortcomings
SELECT * FROM products WHERE a = 1 AND b
= 2 SELECT * FROM products WHERE b = 2
B-TREE Only No Expressions No Partial
SELECT * FROM products WHERE qty = 0
DROP INDEX Unused indexes
HypoPG Extension Support
None
pg_query HypoPG
Get Involved github.com/ankane/dexter