How Postgres Could Index Itself

Transcript

1. 1.

   How Postgres Could Index Itself

2. 2.

   None
3. 3.

   github.com/ankane

4. 4.

   None
5. 5.

   Read speed vs Write speed Space

6. 6.

   None
7. 7.

   v1

8. 8.

   Collect queries Analyze queries

9. 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. 10.

    SELECT * FROM products WHERE store_id = 1

11. 11.

    pg query github.com/lfittl/pg_query _

12. 12.

    SELECT * FROM products WHERE store_id = 1

13. 13.

    SELECT * FROM products WHERE store_id = 1 AND brand_id

    = 2
14. 14.

    Stores have many products Brands have a few products

15. 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. 16.

    pg_stats n_distinct null_frac

17. 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. 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. 19.

    store_id Rows 10,000 null_frac 0 n_distinct 100 Estimated Rows 100

20. 20.

    SELECT * FROM products ORDER BY created_at DESC LIMIT 10

21. 21.

    SELECT * FROM products WHERE store_id = 1 ORDER BY

    created_at DESC LIMIT 10
22. 22.

    None
23. 23.

    Shortcomings

24. 24.

    Single table plus Simple WHERE clause and/or Simple ORDER BY

    clause
25. 25.

    Duplicating planner logic

26. 26.

    pg_stats n_distinct null_frac ✗ most_common_vals ✗ most_common_freqs ✗ histogram_bounds

27. 27.

    most_common_vals {2, 5, 1} most_common_freqs {0.9, 0.05, 0.01} store_id =

    1 vs store_id = 2
28. 28.

    histogram_bounds {0, 9, 25, 60, 99} qty < 5 vs

    qty > 5
29. 29.

    SELECT * FROM products WHERE store_id = ?

30. 30.

    v2

31. 31.

    log_min_statement_duration duration: 100 ms statement: SELECT * FROM products WHERE

    store_id = 1
32. 32.

    Given a query and a set of indexes best indexes

    to use
33. 33.

    Given a query and all possible indexes best indexes possible

34. 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. 35.

    hypopg github.com/dalibo/hypopg

36. 36.

    SELECT * FROM products WHERE store_id = 1 AND brand_id

    = 2
37. 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. 38.

    Cost Hypothetical Indexes Original 1000

39. 39.

    Add hypothetical indexes store_id brand_id

40. 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. 41.

    Cost Hypothetical Indexes Original 1000 Single Column 50 brand_id

42. 42.

    Add hypothetical indexes store_id, brand_id brand_id, store_id (does not try

    different sort orders right now)
43. 43.

    Cost Hypothetical Indexes Original 1000 Single Column 50 brand_id Multi

    Column 45 brand_id, store_id
44. 44.

    Dexter github.com/ankane/dexter

45. 45.

    tail -F -n +1 <log-file> | dexter <conn-opts>

46. 46.

    --create --exclude big_table --min-time 10

47. 47.

    Shortcomings

48. 48.

    SELECT * FROM products WHERE a = 1 AND b

    = 2 SELECT * FROM products WHERE b = 2
49. 49.

    B-TREE Only No Expressions No Partial

50. 50.

    SELECT * FROM products WHERE qty = 0

51. 51.

    DROP INDEX Unused indexes

52. 52.

    HypoPG Extension Support

53. 53.

    None
54. 54.

    pg_query HypoPG

55. 55.

    Get Involved github.com/ankane/dexter