How Postgres Could Index Itself

A2f2287b6054926438eb65f8726cc77d?s=47 Andrew Kane
September 08, 2017

How Postgres Could Index Itself

A2f2287b6054926438eb65f8726cc77d?s=128

Andrew Kane

September 08, 2017
Tweet

Transcript

  1. 2.
  2. 4.
  3. 6.
  4. 7.

    v1

  5. 9.

    pg_stat_statements Query Total Time (ms) Calls Average Time (ms) SELECT

    … 40,000 80,000 0.5 SELECT … 30,000 300 100
  6. 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
  7. 22.
  8. 30.

    v2

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

    SELECT * FROM products WHERE a = 1 AND b

    = 2 SELECT * FROM products WHERE b = 2
  12. 53.