1 # Pre-defined queries of collectd's postgresql plugin.
2 #
3 # Do not edit this file. If you want to change any of the query definitions,
4 # overwrite them in collectd.conf instead.
5 #
6 # This file is distributed under the same terms as collectd itself.
8 <Query backends>
9 Statement "SELECT count(*) AS count \
10 FROM pg_stat_activity \
11 WHERE datname = $1;"
13 Param database
15 <Result>
16 Type "pg_numbackends"
17 ValuesFrom "count"
18 </Result>
19 </Query>
21 <Query transactions>
22 Statement "SELECT xact_commit, xact_rollback \
23 FROM pg_stat_database \
24 WHERE datname = $1;"
26 Param database
28 <Result>
29 Type "pg_xact"
30 InstancePrefix "commit"
31 ValuesFrom "xact_commit"
32 </Result>
33 <Result>
34 Type "pg_xact"
35 InstancePrefix "rollback"
36 ValuesFrom "xact_rollback"
37 </Result>
38 </Query>
40 <Query queries>
41 Statement "SELECT coalesce(sum(n_tup_ins), 0) AS ins, \
42 coalesce(sum(n_tup_upd), 0) AS upd, \
43 coalesce(sum(n_tup_del), 0) AS del \
44 FROM pg_stat_user_tables;"
46 <Result>
47 Type "pg_n_tup_c"
48 InstancePrefix "ins"
49 ValuesFrom "ins"
50 </Result>
51 <Result>
52 Type "pg_n_tup_c"
53 InstancePrefix "upd"
54 ValuesFrom "upd"
55 </Result>
56 <Result>
57 Type "pg_n_tup_c"
58 InstancePrefix "del"
59 ValuesFrom "del"
60 </Result>
62 MaxVersion 80299
63 </Query>
65 <Query queries>
66 Statement "SELECT coalesce(sum(n_tup_ins), 0) AS ins, \
67 coalesce(sum(n_tup_upd), 0) AS upd, \
68 coalesce(sum(n_tup_del), 0) AS del, \
69 coalesce(sum(n_tup_hot_upd), 0) AS hot_upd \
70 FROM pg_stat_user_tables;"
72 <Result>
73 Type "pg_n_tup_c"
74 InstancePrefix "ins"
75 ValuesFrom "ins"
76 </Result>
77 <Result>
78 Type "pg_n_tup_c"
79 InstancePrefix "upd"
80 ValuesFrom "upd"
81 </Result>
82 <Result>
83 Type "pg_n_tup_c"
84 InstancePrefix "del"
85 ValuesFrom "del"
86 </Result>
87 <Result>
88 Type "pg_n_tup_c"
89 InstancePrefix "hot_upd"
90 ValuesFrom "hot_upd"
91 </Result>
93 MinVersion 80300
94 </Query>
96 <Query queries_by_table>
97 Statement "SELECT schemaname, relname, \
98 n_tup_ins AS ins, \
99 n_tup_upd AS upd, \
100 n_tup_del AS del \
101 FROM pg_stat_user_tables;"
103 <Result>
104 Type "pg_n_tup_c"
105 InstancePrefix "ins"
106 InstancesFrom "schemaname" "relname"
107 ValuesFrom "ins"
108 </Result>
109 <Result>
110 Type "pg_n_tup_c"
111 InstancePrefix "upd"
112 InstancesFrom "schemaname" "relname"
113 ValuesFrom "upd"
114 </Result>
115 <Result>
116 Type "pg_n_tup_c"
117 InstancePrefix "del"
118 InstancesFrom "schemaname" "relname"
119 ValuesFrom "del"
120 </Result>
122 MaxVersion 80299
123 </Query>
125 <Query queries_by_table>
126 Statement "SELECT schemaname, relname, \
127 n_tup_ins AS ins, \
128 n_tup_upd AS upd, \
129 n_tup_del AS del, \
130 n_tup_hot_upd AS hot_upd \
131 FROM pg_stat_user_tables;"
133 <Result>
134 Type "pg_n_tup_c"
135 InstancePrefix "ins"
136 InstancesFrom "schemaname" "relname"
137 ValuesFrom "ins"
138 </Result>
139 <Result>
140 Type "pg_n_tup_c"
141 InstancePrefix "upd"
142 InstancesFrom "schemaname" "relname"
143 ValuesFrom "upd"
144 </Result>
145 <Result>
146 Type "pg_n_tup_c"
147 InstancePrefix "del"
148 InstancesFrom "schemaname" "relname"
149 ValuesFrom "del"
150 </Result>
151 <Result>
152 Type "pg_n_tup_c"
153 InstancePrefix "hot_upd"
154 InstancesFrom "schemaname" "relname"
155 ValuesFrom "hot_upd"
156 </Result>
158 MinVersion 80300
159 </Query>
161 <Query query_plans>
162 Statement "SELECT coalesce(sum(seq_scan), 0) AS seq, \
163 coalesce(sum(seq_tup_read), 0) AS seq_tup_read, \
164 coalesce(sum(idx_scan), 0) AS idx, \
165 coalesce(sum(idx_tup_fetch), 0) AS idx_tup_fetch \
166 FROM pg_stat_user_tables;"
168 <Result>
169 Type "pg_scan"
170 InstancePrefix "seq"
171 ValuesFrom "seq"
172 </Result>
173 <Result>
174 Type "pg_scan"
175 InstancePrefix "seq_tup_read"
176 ValuesFrom "seq_tup_read"
177 </Result>
178 <Result>
179 Type "pg_scan"
180 InstancePrefix "idx"
181 ValuesFrom "idx"
182 </Result>
183 <Result>
184 Type "pg_scan"
185 InstancePrefix "idx_tup_fetch"
186 ValuesFrom "idx_tup_fetch"
187 </Result>
188 </Query>
190 <Query table_states>
191 Statement "SELECT coalesce(sum(n_live_tup), 0) AS live, \
192 coalesce(sum(n_dead_tup), 0) AS dead \
193 FROM pg_stat_user_tables;"
195 <Result>
196 Type "pg_n_tup_g"
197 InstancePrefix "live"
198 ValuesFrom "live"
199 </Result>
200 <Result>
201 Type "pg_n_tup_g"
202 InstancePrefix "dead"
203 ValuesFrom "dead"
204 </Result>
206 MinVersion 80300
207 </Query>
209 <Query query_plans_by_table>
210 Statement "SELECT schemaname, relname, \
211 coalesce(seq_scan, 0) AS seq, \
212 coalesce(seq_tup_read, 0) AS seq_tup_read, \
213 coalesce(idx_scan, 0) AS idx, \
214 coalesce(idx_tup_fetch, 0) AS idx_tup_fetch \
215 FROM pg_stat_user_tables;"
217 <Result>
218 Type "pg_scan"
219 InstancePrefix "seq"
220 InstancesFrom "schemaname" "relname"
221 ValuesFrom "seq"
222 </Result>
223 <Result>
224 Type "pg_scan"
225 InstancePrefix "seq_tup_read"
226 InstancesFrom "schemaname" "relname"
227 ValuesFrom "seq_tup_read"
228 </Result>
229 <Result>
230 Type "pg_scan"
231 InstancePrefix "idx"
232 InstancesFrom "schemaname" "relname"
233 ValuesFrom "idx"
234 </Result>
235 <Result>
236 Type "pg_scan"
237 InstancePrefix "idx_tup_fetch"
238 InstancesFrom "schemaname" "relname"
239 ValuesFrom "idx_tup_fetch"
240 </Result>
241 </Query>
243 <Query table_states_by_table>
244 Statement "SELECT schemaname, relname, \
245 n_live_tup AS live, n_dead_tup AS dead \
246 FROM pg_stat_user_tables;"
248 <Result>
249 Type "pg_n_tup_g"
250 InstancePrefix "live"
251 InstancesFrom "schemaname" "relname"
252 ValuesFrom "live"
253 </Result>
254 <Result>
255 Type "pg_n_tup_g"
256 InstancePrefix "dead"
257 InstancesFrom "schemaname" "relname"
258 ValuesFrom "dead"
259 </Result>
261 MinVersion 80300
262 </Query>
264 <Query disk_io>
265 Statement "SELECT coalesce(sum(heap_blks_read), 0) AS heap_read, \
266 coalesce(sum(heap_blks_hit), 0) AS heap_hit, \
267 coalesce(sum(idx_blks_read), 0) AS idx_read, \
268 coalesce(sum(idx_blks_hit), 0) AS idx_hit, \
269 coalesce(sum(toast_blks_read), 0) AS toast_read, \
270 coalesce(sum(toast_blks_hit), 0) AS toast_hit, \
271 coalesce(sum(tidx_blks_read), 0) AS tidx_read, \
272 coalesce(sum(tidx_blks_hit), 0) AS tidx_hit \
273 FROM pg_statio_user_tables;"
275 <Result>
276 Type "pg_blks"
277 InstancePrefix "heap_read"
278 ValuesFrom "heap_read"
279 </Result>
280 <Result>
281 Type "pg_blks"
282 InstancePrefix "heap_hit"
283 ValuesFrom "heap_hit"
284 </Result>
285 <Result>
286 Type "pg_blks"
287 InstancePrefix "idx_read"
288 ValuesFrom "idx_read"
289 </Result>
290 <Result>
291 Type "pg_blks"
292 InstancePrefix "idx_hit"
293 ValuesFrom "idx_hit"
294 </Result>
295 <Result>
296 Type "pg_blks"
297 InstancePrefix "toast_read"
298 ValuesFrom "toast_read"
299 </Result>
300 <Result>
301 Type "pg_blks"
302 InstancePrefix "toast_hit"
303 ValuesFrom "toast_hit"
304 </Result>
305 <Result>
306 Type "pg_blks"
307 InstancePrefix "tidx_read"
308 ValuesFrom "tidx_read"
309 </Result>
310 <Result>
311 Type "pg_blks"
312 InstancePrefix "tidx_hit"
313 ValuesFrom "tidx_hit"
314 </Result>
315 </Query>
317 <Query disk_io_by_table>
318 Statement "SELECT schemaname, relname, \
319 coalesce(heap_blks_read, 0) AS heap_read, \
320 coalesce(heap_blks_hit, 0) AS heap_hit, \
321 coalesce(idx_blks_read, 0) AS idx_read, \
322 coalesce(idx_blks_hit, 0) AS idx_hit, \
323 coalesce(toast_blks_read, 0) AS toast_read, \
324 coalesce(toast_blks_hit, 0) AS toast_hit, \
325 coalesce(tidx_blks_read, 0) AS tidx_read, \
326 coalesce(tidx_blks_hit, 0) AS tidx_hit \
327 FROM pg_statio_user_tables;"
329 <Result>
330 Type "pg_blks"
331 InstancePrefix "heap_read"
332 InstancesFrom "schemaname" "relname"
333 ValuesFrom "heap_read"
334 </Result>
335 <Result>
336 Type "pg_blks"
337 InstancePrefix "heap_hit"
338 InstancesFrom "schemaname" "relname"
339 ValuesFrom "heap_hit"
340 </Result>
341 <Result>
342 Type "pg_blks"
343 InstancePrefix "idx_read"
344 InstancesFrom "schemaname" "relname"
345 ValuesFrom "idx_read"
346 </Result>
347 <Result>
348 Type "pg_blks"
349 InstancePrefix "idx_hit"
350 InstancesFrom "schemaname" "relname"
351 ValuesFrom "idx_hit"
352 </Result>
353 <Result>
354 Type "pg_blks"
355 InstancePrefix "toast_read"
356 InstancesFrom "schemaname" "relname"
357 ValuesFrom "toast_read"
358 </Result>
359 <Result>
360 Type "pg_blks"
361 InstancePrefix "toast_hit"
362 InstancesFrom "schemaname" "relname"
363 ValuesFrom "toast_hit"
364 </Result>
365 <Result>
366 Type "pg_blks"
367 InstancePrefix "tidx_read"
368 InstancesFrom "schemaname" "relname"
369 ValuesFrom "tidx_read"
370 </Result>
371 <Result>
372 Type "pg_blks"
373 InstancePrefix "tidx_hit"
374 InstancesFrom "schemaname" "relname"
375 ValuesFrom "tidx_hit"
376 </Result>
377 </Query>
379 <Query disk_usage>
380 Statement "SELECT pg_database_size($1) AS size;"
382 Param database
384 <Result>
385 Type pg_db_size
386 ValuesFrom "size"
387 </Result>
388 </Query>
390 # vim: set ft=config :