Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Speaker Deck
PRO
Sign in
Sign up for free
How Postgres Could Index Itself
Andrew Kane
September 08, 2017
Programming
0
2.8k
How Postgres Could Index Itself
Andrew Kane
September 08, 2017
Tweet
Share
More Decks by Andrew Kane
See All by Andrew Kane
Grocery Hacking
ankane
1
2.8k
From the Store to Your Door
ankane
1
2.6k
Other Decks in Programming
See All in Programming
MapLibre GL JS とCSSアニメーションでできること
satoshi7190
0
200
スタック・オーバーフローに コントリビュートしはじめて良かったこと🐣
takuyakikuchi
1
120
Refactor with using `available` and `deprecated`
417_72ki
3
370
僕が考えた超最強のKMMアプリの作り方
spbaya0141
0
170
Hono v3 - Do Everything, Run Anywhere, But Small, And Faster
yusukebe
4
120
Remote SSHで行うVS Codeリモートホスト開発とトラブルシューティング
smt7174
1
390
Spring BootとKubernetesで実現する今どきのDevOps入門
xblood
0
320
Findy - エンジニア向け会社紹介 / Findy Letter for Engineers
findyinc
2
42k
10年以上続くプロダクトの フロントエンド刷新プロジェクトのふりかえり
yotahada3
2
290
はてなリモートインターンシップ2022 インフラ 講義資料
hatena
4
2.1k
Micro Frontends with Module Federation @MicroFrontend Summit 2023
manfredsteyer
PRO
0
410
量子コンピュータ時代のプログラミングセミナー / 20230119_Amplify_seminar _shift_optimization
fixstars
0
150
Featured
See All Featured
XXLCSS - How to scale CSS and keep your sanity
sugarenia
236
1.1M
Fontdeck: Realign not Redesign
paulrobertlloyd
74
4.3k
Design and Strategy: How to Deal with People Who Don’t "Get" Design
morganepeng
109
16k
Atom: Resistance is Futile
akmur
256
24k
Gamification - CAS2011
davidbonilla
75
4.1k
What's in a price? How to price your products and services
michaelherold
233
9.7k
How to name files
jennybc
46
73k
Six Lessons from altMBA
skipperchong
15
2.3k
Building a Modern Day E-commerce SEO Strategy
aleyda
6
4.5k
Embracing the Ebb and Flow
colly
75
3.6k
Writing Fast Ruby
sferik
613
58k
It's Worth the Effort
3n
177
26k
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