Class: FastCount::Adapters::PostgresqlAdapter
Instance Method Summary
collapse
Methods inherited from BaseAdapter
#initialize
Instance Method Details
#estimated_count(sql) ⇒ Object
73
74
75
76
|
# File 'lib/fast_count/adapters/postgresql_adapter.rb', line 73
def estimated_count(sql)
query_plan = @connection.select_value("EXPLAIN #{sql}")
query_plan.match(/rows=(\d+)/)[1].to_i
end
|
#fast_count(table_name, threshold) ⇒ Object
67
68
69
70
71
|
# File 'lib/fast_count/adapters/postgresql_adapter.rb', line 67
def fast_count(table_name, threshold)
@connection.select_value(
"SELECT fast_count(#{@connection.quote(table_name)}, #{@connection.quote(threshold)})"
).to_i
end
|
#fast_distinct_count(table_name, column_name) ⇒ Object
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
|
# File 'lib/fast_count/adapters/postgresql_adapter.rb', line 78
def fast_distinct_count(table_name, column_name)
unless index_exists?(table_name, column_name)
raise "Index starting with '#{column_name}' must exist on '#{table_name}' table"
end
table = @connection.quote_table_name(table_name)
column = @connection.quote_column_name(column_name)
@connection.select_value(" WITH RECURSIVE t AS (\n (SELECT \#{column} FROM \#{table} ORDER BY \#{column} LIMIT 1)\n UNION\n SELECT (SELECT \#{column} FROM \#{table} WHERE \#{column} > t.\#{column} ORDER BY \#{column} LIMIT 1)\n FROM t\n WHERE t.\#{column} IS NOT NULL\n ),\n\n distinct_values AS (\n SELECT \#{column} FROM t WHERE \#{column} IS NOT NULL\n UNION\n SELECT NULL WHERE EXISTS (SELECT 1 FROM \#{table} WHERE \#{column} IS NULL)\n )\n\n SELECT COUNT(*) FROM distinct_values\n SQL\nend\n")
|
#install ⇒ Object
7
8
9
10
11
12
13
14
15
16
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
|
# File 'lib/fast_count/adapters/postgresql_adapter.rb', line 7
def install
@connection.execute(" CREATE FUNCTION fast_count(identifier text, threshold bigint) RETURNS bigint AS $$\n DECLARE\n count bigint;\n table_parts text[];\n schema_name text;\n table_name text;\n BEGIN\n SELECT PARSE_IDENT(identifier) INTO table_parts;\n\n IF ARRAY_LENGTH(table_parts, 1) = 2 THEN\n schema_name := ''''|| table_parts[1] ||'''';\n table_name := ''''|| table_parts[2] ||'''';\n ELSE\n schema_name := 'ANY (current_schemas(false))';\n table_name := ''''|| table_parts[1] ||'''';\n END IF;\n\n EXECUTE '\n WITH tables_counts AS (\n -- inherited and partitioned tables counts\n SELECT\n ((SUM(child.reltuples::float) / greatest(SUM(child.relpages), 1))) *\n (SUM(pg_relation_size(child.oid))::float / (current_setting(''block_size'')::float))::integer AS estimate\n FROM pg_inherits\n INNER JOIN pg_class parent ON pg_inherits.inhparent = parent.oid\n LEFT JOIN pg_namespace n ON n.oid = parent.relnamespace\n INNER JOIN pg_class child ON pg_inherits.inhrelid = child.oid\n WHERE n.nspname = '|| schema_name ||' AND\n parent.relname = '|| table_name ||'\n\n UNION ALL\n\n -- table count\n SELECT\n (reltuples::float / greatest(relpages, 1)) *\n (pg_relation_size(c.oid)::float / (current_setting(''block_size'')::float))::integer AS estimate\n FROM pg_class c\n LEFT JOIN pg_namespace n ON n.oid = c.relnamespace\n WHERE n.nspname = '|| schema_name ||' AND\n c.relname = '|| table_name ||'\n )\n\n SELECT\n CASE\n WHEN SUM(estimate) < '|| threshold ||' THEN (SELECT COUNT(*) FROM '|| identifier ||')\n ELSE SUM(estimate)\n END AS count\n FROM tables_counts' INTO count;\n RETURN count;\n END\n $$ LANGUAGE plpgsql;\n SQL\nend\n")
|
#uninstall ⇒ Object
63
64
65
|
# File 'lib/fast_count/adapters/postgresql_adapter.rb', line 63
def uninstall
@connection.execute("DROP FUNCTION IF EXISTS fast_count(text, bigint)")
end
|