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
Qiita Bash
mercury_dev0517
1
180
Being an ethical software engineer
xgouchet
PRO
0
200
サービスレベルを管理してアジャイルを加速しよう!! / slm-accelerate-agility
tomoyakitaura
1
150
CRE Meetup!ユーザー信頼性を支えるエンジニアリング実践例の発表資料です
tmnb
0
620
Empowering Developers with HTML-Aware ERB Tooling @ RubyKaigi 2025, Matsuyama, Ehime
marcoroth
1
220
Enterprise Web App. Development (1): Build Tool Training Ver. 5
knakagawa
1
110
Youtube Lofier - Chrome拡張開発
ninikoko
0
2.4k
新卒から4年間、20年もののWebサービスと 向き合って学んだソフトウェア考古学
oguri
8
7.2k
AI Coding Agent Enablement - エージェントを自走させよう
yukukotani
13
5.7k
いまさら聞けない生成AI入門: 「生成AIを高速キャッチアップ」
soh9834
15
4.4k
Defying Front-End Inertia: Inertia.js on Rails
skryukov
0
450
Signal-Based Data FetchingWith the New httpResource
manfredsteyer
PRO
0
160
Featured
See All Featured
GraphQLとの向き合い方2022年版
quramy
46
14k
XXLCSS - How to scale CSS and keep your sanity
sugarenia
248
1.3M
YesSQL, Process and Tooling at Scale
rocio
172
14k
How to Think Like a Performance Engineer
csswizardry
23
1.5k
個人開発の失敗を避けるイケてる考え方 / tips for indie hackers
panda_program
104
19k
The Web Performance Landscape in 2024 [PerfNow 2024]
tammyeverts
5
520
No one is an island. Learnings from fostering a developers community.
thoeni
21
3.2k
Making Projects Easy
brettharned
116
6.1k
A Tale of Four Properties
chriscoyier
158
23k
Art, The Web, and Tiny UX
lynnandtonic
298
20k
Why You Should Never Use an ORM
jnunemaker
PRO
55
9.3k
Building an army of robots
kneath
304
45k
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