$30 off During Our Annual Pro Sale. View Details »

How Postgres Could Index Itself

Andrew Kane
September 08, 2017

How Postgres Could Index Itself

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