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

Returns a new instance of 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 = <<-EOL
    select tablename as table_name, count(*) as index_count from pg_indexes where schemaname='public' group by 1 order by 2
  EOL
  @connection.query(sql).to_a
end

#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(<<-EOL
    select
        t.relname as table_name,
        i.relname as index_name,
        a.attname as column_name,
        i.reltuples as numrows,
        ix.indisunique as is_unique,
        ix.indisprimary as is_primary,
        s.n_distinct as numdistinct
    from pg_namespace p
    join pg_class t on t.relnamespace = p.oid
    join pg_index ix on ix.indrelid = t.oid
    join pg_class i on i.oid = ix.indexrelid
    join pg_attribute a on a.attrelid = t.oid
    left join pg_stats s on s.tablename = t.relname
        AND s.attname = a.attname
    where
        p.nspname = 'public'
        and a.attnum = ANY(ix.indkey)
        and t.relkind = 'r'
    order by
        t.relname,
        ix.indisprimary desc,
        i.relname,
        array_position(ix.indkey, a.attnum)
    EOL
  )
  rows = result.to_a.map do |row|
    # TBD: do better than this, have them return something objecty
    if row['is_primary'] == "t"
      row['index_name'] = "PRIMARY"
      row['non_unique'] = 0
    elsif row['is_unique']
      row['non_unique'] = 0
    end

    if row['numdistinct'].nil?
      # meaning the table's empty.
      row['cardinality'] = 0
    elsif row['numdistinct'] == 0
      # numdistinct is 0 if there's rows in the table but all values are null
      row['cardinality'] = 1
    elsif row['numdistinct'] < 0
      # postgres talks about either cardinality or selectivity (depending.  what's their heuristic?)
      # in the same way we do in the yaml file!
      # if less than zero, it's negative selectivity.
      row['cardinality'] = -(row['numrows'] * row['numdistinct'])
    else
      row['cardinality'] = row['numdistinct']
    end
    row
  end

  #TODO: estimate multi-index column cardinality
  rows
end

#mysql? ⇒ Boolean

Returns:

  • (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