Upgrade to Pro — share decks privately, control downloads, hide ads and more …

How Postgres Could Index Itself

Avatar for Andrew Kane Andrew Kane
September 08, 2017

How Postgres Could Index Itself

Avatar for Andrew Kane

Andrew Kane

September 08, 2017
Tweet

Other Decks in Programming

Transcript

  1. v1

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

    … 40,000 80,000 0.5 SELECT … 30,000 300 100
  3. 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
  4. v2

  5. /* 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
  6. 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
  7. SELECT * FROM products WHERE a = 1 AND b

    = 2 SELECT * FROM products WHERE b = 2