Class: Shiba::Connection::Postgres

Inherits:
Object
  • Object
show all
Defined in:
lib/shiba/connection/postgres.rb

Instance Method Summary collapse

Constructor Details

#initialize(h) ⇒ Postgres 



6
7
8
9
10
11
 
# File 'lib/shiba/connection/postgres.rb', line 6

def initialize(h)
  @connection = PG.connect( dbname: h['database'], host: h['host'], user: h['username'], password: h['password'], port: h['port'] )
  @connection.type_map_for_results = PG::BasicTypeMapForResults.new(@connection)
  query("SET enable_seqscan = OFF")
  query("SET random_page_cost = 0.01")
end

Instance Method Details

#count_indexes_by_table ⇒ Object 



75
76
77
78
79
80
 
# File 'lib/shiba/connection/postgres.rb', line 75

def count_indexes_by_table
  sql = "    select tablename as table_name, count(*) as index_count from pg_indexes where schemaname='public' group by 1 order by 2\n  EOL\n  @connection.query(sql).to_a\nend\n"

#each_column_size ⇒ Object 



87
88
 
# File 'lib/shiba/connection/postgres.rb', line 87

def each_column_size
end

#explain(sql) ⇒ Object 



82
83
84
85
 
# File 'lib/shiba/connection/postgres.rb', line 82

def explain(sql)
  rows = query("EXPLAIN (FORMAT JSON) #{sql}").to_a
  [rows.first["QUERY PLAN"], {}]
end

#fetch_indexes ⇒ Object 



17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
 
# File 'lib/shiba/connection/postgres.rb', line 17

def fetch_indexes
  result = query("    select\n        t.relname as table_name,\n        i.relname as index_name,\n        a.attname as column_name,\n        i.reltuples as numrows,\n        ix.indisunique as is_unique,\n        ix.indisprimary as is_primary,\n        s.n_distinct as numdistinct\n    from pg_namespace p\n    join pg_class t on t.relnamespace = p.oid\n    join pg_index ix on ix.indrelid = t.oid\n    join pg_class i on i.oid = ix.indexrelid\n    join pg_attribute a on a.attrelid = t.oid\n    left join pg_stats s on s.tablename = t.relname\n        AND s.attname = a.attname\n    where\n        p.nspname = 'public'\n        and a.attnum = ANY(ix.indkey)\n        and t.relkind = 'r'\n    order by\n        t.relname,\n        ix.indisprimary desc,\n        i.relname,\n        array_position(ix.indkey, a.attnum)\n    EOL\n  )\n  rows = result.to_a.map do |row|\n    # TBD: do better than this, have them return something objecty\n    if row['is_primary'] == \"t\"\n      row['index_name'] = \"PRIMARY\"\n      row['non_unique'] = 0\n    elsif row['is_unique']\n      row['non_unique'] = 0\n    end\n\n    if row['numdistinct'].nil?\n      # meaning the table's empty.\n      row['cardinality'] = 0\n    elsif row['numdistinct'] == 0\n      # numdistinct is 0 if there's rows in the table but all values are null\n      row['cardinality'] = 1\n    elsif row['numdistinct'] < 0\n      # postgres talks about either cardinality or selectivity (depending.  what's their heuristic?)\n      # in the same way we do in the yaml file!\n      # if less than zero, it's negative selectivity.\n      row['cardinality'] = -(row['numrows'] * row['numdistinct'])\n    else\n      row['cardinality'] = row['numdistinct']\n    end\n    row\n  end\n\n  #TODO: estimate multi-index column cardinality\n  rows\nend\n"

#mysql? ⇒ Boolean 



90
91
92
 
# File 'lib/shiba/connection/postgres.rb', line 90

def mysql?
  false
end

#query(sql) ⇒ Object 



13
14
15
 
# File 'lib/shiba/connection/postgres.rb', line 13

def query(sql)
  @connection.query(sql)
end