How Postgres Could Index Itself

Transcript

1. How Postgres Could Index Itself
3. github.com/ankane
5. Read speed vs Write speed Space
7. v1
8. Collect queries Analyze queries
9. pg_stat_statements Query Total Time (ms) Calls Average Time (ms) SELECT … 40,000 80,000 0.5 SELECT … 30,000 300 100
10. SELECT * FROM products WHERE store_id = 1
11. pg query github.com/lfittl/pg_query _
12. SELECT * FROM products WHERE store_id = 1
13. SELECT * FROM products WHERE store_id = 1 AND brand_id = 2
14. Stores have many products Brands have a few products
15. 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
16. pg_stats n_distinct null_frac
17. store_id brand_id Rows 100,000 100,000 null_frac 0 0.10 n_distinct 100 9,000 Estimated Rows 1,000 10
18. 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
19. store_id Rows 10,000 null_frac 0 n_distinct 100 Estimated Rows 100
20. SELECT * FROM products ORDER BY created_at DESC LIMIT 10
21. SELECT * FROM products WHERE store_id = 1 ORDER BY created_at DESC LIMIT 10
23. Shortcomings
24. Single table plus Simple WHERE clause and/or Simple ORDER BY clause
25. Duplicating planner logic
26. pg_stats n_distinct null_frac ✗ most_common_vals ✗ most_common_freqs ✗ histogram_bounds
27. most_common_vals {2, 5, 1} most_common_freqs {0.9, 0.05, 0.01} store_id = 1 vs store_id = 2
28. histogram_bounds {0, 9, 25, 60, 99} qty < 5 vs qty > 5
29. SELECT * FROM products WHERE store_id = ?
30. v2
31. log_min_statement_duration duration: 100 ms statement: SELECT * FROM products WHERE store_id = 1
32. Given a query and a set of indexes best indexes to use
33. Given a query and all possible indexes best indexes possible
34. /* 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
35. hypopg github.com/dalibo/hypopg
36. SELECT * FROM products WHERE store_id = 1 AND brand_id = 2
37. EXPLAIN Seq Scan on products (cost=0.00..1000.00 rows=100 width=108) Filter: (store_id = 1 AND brand_id = 2) Final Cost
38. Cost Hypothetical Indexes Original 1000
39. Add hypothetical indexes store_id brand_id
40. 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
41. Cost Hypothetical Indexes Original 1000 Single Column 50 brand_id
42. Add hypothetical indexes store_id, brand_id brand_id, store_id (does not try different sort orders right now)
43. Cost Hypothetical Indexes Original 1000 Single Column 50 brand_id Multi Column 45 brand_id, store_id
44. Dexter github.com/ankane/dexter
45. tail -F -n +1 <log-file> | dexter <conn-opts>
46. --create --exclude big_table --min-time 10
47. Shortcomings
48. SELECT * FROM products WHERE a = 1 AND b = 2 SELECT * FROM products WHERE b = 2
49. B-TREE Only No Expressions No Partial
50. SELECT * FROM products WHERE qty = 0
51. DROP INDEX Unused indexes
52. HypoPG Extension Support
54. pg_query HypoPG
55. Get Involved github.com/ankane/dexter