· 6 years ago · Jun 26, 2019, 05:42 AM
1/**
2 Title: SQL snippets for "PostgreSQL - A maintenance and performance primer"
3 Author: PM - 2019
4 */
5
6
7----------------------------------------------------
8-- Query to find unused indexes
9
10SELECT s.schemaname,
11 s.relname AS tablename,
12 s.indexrelname AS indexname,
13 pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size
14FROM pg_catalog.pg_stat_user_indexes s
15 JOIN pg_catalog.pg_index i ON s.indexrelid = i.indexrelid
16WHERE s.idx_scan = 0 -- has never been scanned
17 AND 0 <> ALL (i.indkey) -- no index column is an expression
18 AND NOT i.indisunique -- is not a UNIQUE index
19 AND NOT EXISTS -- does not enforce a constraint
20 (SELECT 1
21 FROM pg_catalog.pg_constraint c
22 WHERE c.conindid = s.indexrelid)
23ORDER BY pg_relation_size(s.indexrelid) DESC;
24
25
26----------------------------------------------------
27-- Find duplicate indexes
28
29SELECT pg_size_pretty(SUM(pg_relation_size(idx))::BIGINT) AS SIZE,
30 (array_agg(idx))[1] AS idx1,
31 (array_agg(idx))[2] AS idx2,
32 (array_agg(idx))[3] AS idx3,
33 (array_agg(idx))[4] AS idx4
34FROM (
35 SELECT indexrelid::regclass AS idx,
36 (indrelid::text || E'\n' || indclass::text || E'\n' || indkey::text || E'\n' ||
37 COALESCE(indexprs::text, '') || E'\n' || COALESCE(indpred::text, '')) AS KEY
38 FROM pg_index) sub
39GROUP BY KEY
40HAVING COUNT(*) > 1
41ORDER BY SUM(pg_relation_size(idx)) DESC;
42
43----------------------------------------------------
44-- Show running queries
45
46SELECT pid, age(clock_timestamp(), query_start), usename, query
47FROM pg_stat_activity
48WHERE query != '<IDLE>'
49 AND query NOT ILIKE '%pg_stat_activity%'
50ORDER BY query_start desc;
51
52
53----------------------------------------------------
54-- Show bocked statements
55select pid,
56 usename,
57 pg_blocking_pids(pid) as blocked_by,
58 query as blocked_query
59from pg_stat_activity
60where cardinality(pg_blocking_pids(pid)) > 0;
61
62----------------------------------------------------
63-- kill stuff
64SELECT pg_cancel_backend(procpid);
65SELECT pg_terminate_backend(procpid);
66
67----------------------------------------------------
68-- cache hit rates (should not be less than 0.99)
69SELECT sum(heap_blks_read) as heap_read,
70 sum(heap_blks_hit) as heap_hit,
71 (sum(heap_blks_hit) - sum(heap_blks_read)) / sum(heap_blks_hit) as ratio
72FROM pg_statio_user_tables;
73
74----------------------------------------------------
75-- Enable statiscs module and show data
76CREATE EXTENSION pg_stat_statements;
77
78SELECT pg_stat_statements_reset();
79
80select *
81from pg_stat_statements;
82
83----------------------------------------------------
84-- Show database size
85select datname, pg_size_pretty(pg_database_size(datname))
86from pg_database
87order by pg_database_size(datname) desc;
88
89----------------------------------------------------
90-- Get table and index sizes
91SELECT current_database(),
92 table_schema as Schema,
93 TABLE_NAME as Table,
94 row_estimate as Row_estimate,
95 pg_size_pretty(total_bytes) AS Total_size_pretty,
96 pg_size_pretty(index_bytes) AS Index_size_pretty,
97 pg_size_pretty(toast_bytes) AS Toast_size_pretty,
98 pg_size_pretty(table_bytes) AS Table_size_pretty,
99 table_bytes AS Total_size_bytes,
100 index_bytes AS Index_size_bytes,
101 toast_bytes AS Toast_size_bytes,
102 table_bytes AS Table_size_bytes
103FROM (
104 SELECT table_schema,
105 table_name,
106 row_estimate,
107 index_bytes,
108 toast_bytes,
109 total_bytes,
110 total_bytes - index_bytes - COALESCE(toast_bytes, 0) AS table_bytes
111 FROM (
112 SELECT c.oid
113 , nspname AS table_schema
114 , relname AS table_name
115 , c.reltuples AS row_estimate
116 , pg_total_relation_size(c.oid) AS total_bytes
117 , pg_indexes_size(c.oid) AS index_bytes
118 , pg_total_relation_size(reltoastrelid) AS toast_bytes
119 FROM pg_class c
120 LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
121
122 WHERE relkind = 'r'
123 ) a
124 ) as tableandindexsizes
125
126order by total_bytes desc;
127
128
129
130----------------------------------------------------
131-- Index size, usage anc last analyze and last vacuum dates
132SELECT pg_stat_user_indexes.schemaname,
133 pg_stat_user_indexes.relname AS tablename,
134 pg_stat_user_indexes.indexrelname AS indexname,
135 pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
136 pg_stat_user_indexes.idx_tup_read as index_entries_returned_by_scans,
137 pg_stat_user_indexes.idx_tup_fetch as table_rows_fetched_by_simple_index_scans,
138 pg_stat_user_indexes.idx_scan as index_scans_initiated_on_this_table,
139 last_analyze,
140 last_autoanalyze,
141 last_autovacuum,
142 last_vacuum
143FROM pg_stat_user_indexes
144 JOIN pg_index USING (indexrelid)
145 join pg_stat_user_tables on pg_stat_user_indexes.relid = pg_stat_user_tables.relid
146WHERE
147ORDER BY pg_relation_size(indexrelid) DESC;
148
149----------------------------------------------------
150-- Index usage rates (should not be less than 0.99)
151SELECT s.schemaname,
152 s.relname,
153 i.indexrelname,
154 CASE
155 WHEN (s.seq_scan + s.idx_scan) != 0
156 THEN ROUND(100.0 * s.idx_scan / (s.seq_scan + s.idx_scan), 2)
157 ELSE 0
158 END AS percent_of_times_index_used,
159 n_live_tup AS rows_in_table
160FROM pg_stat_user_tables s
161 JOIN pg_stat_user_indexes i ON s.relid = i.relid
162--where n_live_tup > 10000
163ORDER BY n_live_tup DESC;
164
165----------------------------------------------------
166-- Unused indexes
167SELECT s.schemaname,
168 s.relname AS tablename,
169 s.indexrelname AS indexname,
170 pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size,
171 idx_tup_read as index_entries_returned_by_scans,
172 idx_tup_fetch as table_rows_fetched_by_simple_index_scans,
173 idx_scan as index_scans_initiated_on_this_table
174FROM pg_catalog.pg_stat_user_indexes s
175 JOIN pg_catalog.pg_index i ON s.indexrelid = i.indexrelid
176WHERE s.idx_scan = 0 -- has never been scanned
177 AND 0 <> ALL (i.indkey) -- no index column is an expression
178 AND NOT i.indisunique -- is not a UNIQUE index
179 AND NOT EXISTS -- does not enforce a constraint
180 (SELECT 1
181 FROM pg_catalog.pg_constraint c
182 WHERE c.conindid = s.indexrelid)
183ORDER BY pg_relation_size(s.indexrelid) DESC;
184
185--- Index hit ratios
186with table_stats as (
187 select psut.schemaname,
188 psut.relname,
189 psut.n_live_tup,
190 1.0 * psut.idx_scan / greatest(1, psut.seq_scan + psut.idx_scan) as index_use_ratio
191 from pg_stat_user_tables psut
192 order by psut.n_live_tup desc
193),
194 table_io as (
195 select psiut.relname,
196 sum(psiut.heap_blks_read) as table_page_read,
197 sum(psiut.heap_blks_hit) as table_page_hit,
198 sum(psiut.heap_blks_hit) /
199 greatest(1, sum(psiut.heap_blks_hit) + sum(psiut.heap_blks_read)) as table_hit_ratio
200 from pg_statio_user_tables psiut
201 group by psiut.relname
202 order by table_page_read desc
203 ),
204 index_io as (
205 select psiui.relname,
206 psiui.indexrelname,
207 sum(psiui.idx_blks_read) as idx_page_read,
208 sum(psiui.idx_blks_hit) as idx_page_hit,
209 1.0 * sum(psiui.idx_blks_hit) /
210 greatest(1.0, sum(psiui.idx_blks_hit) + sum(psiui.idx_blks_read)) as idx_hit_ratio
211 from pg_statio_user_indexes psiui
212 group by psiui.relname, psiui.indexrelname
213 order by sum(psiui.idx_blks_read) desc
214 )
215select ts.schemaname,
216 ts.relname,
217 ii.indexrelname,
218 ts.n_live_tup,
219 trunc(ts.index_use_ratio, 4) as index_use_ratio,
220 ti.table_page_read,
221 ti.table_page_hit,
222 trunc(ti.table_hit_ratio, 4) as table_hit_ratio,
223 ii.idx_page_read,
224 ii.idx_page_hit,
225 trunc(ii.idx_hit_ratio, 4) as idx_hit_ratio
226from table_stats ts
227 left outer join table_io ti
228 on ti.relname = ts.relname
229 left outer join index_io ii
230 on ii.relname = ts.relname
231order by ti.table_page_read desc, ii.idx_page_read desc;
232
233
234
235----------------------------------------------------
236-- Table bloat
237select current_database(),
238 schemaname as Schema,
239 tblname as Table,
240 pg_size_pretty(real_size::numeric) as real_size_pretty,
241 pg_size_pretty(extra_size::numeric) as extra_size_pretty,
242 ROUND((extra_ratio)::numeric, 2) as extra_ratio,
243 pg_size_pretty(bloat_size::numeric) as bloat_size_pretty,
244 ROUND((bloat_ratio)::numeric, 2) as bloat_ratio,
245 real_size as bloat_size_bytes,
246 extra_size as extra_size_bytes,
247 bloat_size as bloat_size_bytes,
248 fillfactor as Fillfactor
249
250from (
251 SELECT current_database(),
252 schemaname,
253 tblname,
254 bs * tblpages AS real_size,
255 (tblpages - est_tblpages) * bs AS extra_size,
256 CASE
257 WHEN tblpages - est_tblpages > 0
258 THEN 100 * (tblpages - est_tblpages) / tblpages::float
259 ELSE 0
260 END AS extra_ratio,
261 fillfactor,
262 CASE
263 WHEN tblpages - est_tblpages_ff > 0
264 THEN (tblpages - est_tblpages_ff) * bs
265 ELSE 0
266 END AS bloat_size,
267 CASE
268 WHEN tblpages - est_tblpages_ff > 0
269 THEN 100 * (tblpages - est_tblpages_ff) / tblpages::float
270 ELSE 0
271 END AS bloat_ratio,
272 is_na
273 -- , (pst).free_percent + (pst).dead_tuple_percent AS real_frag
274 FROM (
275 SELECT ceil(reltuples / ((bs - page_hdr) / tpl_size)) + ceil(toasttuples / 4) AS est_tblpages,
276 ceil(reltuples / ((bs - page_hdr) * fillfactor / (tpl_size * 100))) +
277 ceil(toasttuples / 4) AS est_tblpages_ff,
278 tblpages,
279 fillfactor,
280 bs,
281 tblid,
282 schemaname,
283 tblname,
284 heappages,
285 toastpages,
286 is_na
287 -- , stattuple.pgstattuple(tblid) AS pst
288 FROM (
289 SELECT (4 + tpl_hdr_size + tpl_data_size + (2 * ma)
290 - CASE WHEN tpl_hdr_size % ma = 0 THEN ma ELSE tpl_hdr_size % ma END
291 - CASE
292 WHEN ceil(tpl_data_size)::int % ma = 0 THEN ma
293 ELSE ceil(tpl_data_size)::int % ma END
294 ) AS tpl_size,
295 bs - page_hdr AS size_per_block,
296 (heappages + toastpages) AS tblpages,
297 heappages,
298 toastpages,
299 reltuples,
300 toasttuples,
301 bs,
302 page_hdr,
303 tblid,
304 schemaname,
305 tblname,
306 fillfactor,
307 is_na
308 FROM (
309 SELECT tbl.oid AS tblid,
310 ns.nspname AS schemaname,
311 tbl.relname AS tblname,
312 tbl.reltuples,
313 tbl.relpages AS heappages,
314 coalesce(toast.relpages, 0) AS toastpages,
315 coalesce(toast.reltuples, 0) AS toasttuples,
316 coalesce(substring(
317 array_to_string(tbl.reloptions, ' ')
318 FROM 'fillfactor=([0-9]+)')::smallint,
319 100) AS fillfactor,
320 current_setting('block_size')::numeric AS bs,
321 CASE
322 WHEN version() ~ 'mingw32' OR version() ~ '64-bit|x86_64|ppc64|ia64|amd64'
323 THEN 8
324 ELSE 4 END AS ma,
325 24 AS page_hdr,
326 23 + CASE
327 WHEN MAX(coalesce(null_frac, 0)) > 0 THEN (7 + count(*)) / 8
328 ELSE 0::int END
329 +
330 CASE WHEN tbl.relhasoids THEN 4 ELSE 0 END AS tpl_hdr_size,
331 sum((1 - coalesce(s.null_frac, 0)) * coalesce(s.avg_width, 1024)) AS tpl_data_size,
332 bool_or(att.atttypid = 'pg_catalog.name'::regtype)
333 OR
334 count(att.attname) <> count(s.attname) AS is_na
335 FROM pg_attribute AS att
336 JOIN pg_class AS tbl ON att.attrelid = tbl.oid
337 JOIN pg_namespace AS ns ON ns.oid = tbl.relnamespace
338 LEFT JOIN pg_stats AS s ON s.schemaname = ns.nspname
339 AND s.tablename = tbl.relname AND s.inherited = false AND
340 s.attname = att.attname
341 LEFT JOIN pg_class AS toast ON tbl.reltoastrelid = toast.oid
342 WHERE att.attnum > 0
343 AND NOT att.attisdropped
344 AND tbl.relkind = 'r'
345 GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, tbl.relhasoids
346 ORDER BY 2, 3
347 ) AS s
348 ) AS s2
349 ) AS s3
350 WHERE NOT is_na
351 ) as tablestats
352order by real_size desc;
353
354----------------------------------------------------
355-- Index bloat
356select current_database(),
357 schemaname as Schema,
358 tblname as Table,
359 idxname as Index,
360 pg_size_pretty(real_size::numeric) as real_size_pretty,
361 pg_size_pretty(extra_size::numeric) as extra_size_pretty,
362 ROUND((extra_ratio)::numeric, 2) as extra_ratio,
363 pg_size_pretty(bloat_size::numeric) as bloat_size_pretty,
364 ROUND((bloat_ratio)::numeric, 2) as bloat_ratio,
365 real_size as bloat_size_bytes,
366 extra_size as extra_size_bytes,
367 bloat_size as bloat_size_bytes,
368 fillfactor as Fillfactor
369
370from (
371 SELECT current_database(),
372 nspname AS schemaname,
373 tblname,
374 idxname,
375 bs * (relpages)::bigint AS real_size,
376 bs * (relpages - est_pages)::bigint AS extra_size,
377 100 * (relpages - est_pages)::float / relpages AS extra_ratio,
378 fillfactor,
379 CASE
380 WHEN relpages > est_pages_ff
381 THEN bs * (relpages - est_pages_ff)
382 ELSE 0
383 END AS bloat_size,
384 100 * (relpages - est_pages_ff)::float / relpages AS bloat_ratio,
385 is_na
386 -- , 100-(sub.pst).avg_leaf_density, est_pages, index_tuple_hdr_bm, maxalign, pagehdr, nulldatawidth, nulldatahdrwidth, sub.reltuples, sub.relpages -- (DEBUG INFO)
387 FROM (
388 SELECT coalesce(1 +
389 ceil(reltuples / floor((bs - pageopqdata - pagehdr) / (4 + nulldatahdrwidth)::float)),
390 0 -- ItemIdData size + computed avg size of a tuple (nulldatahdrwidth)
391 ) AS est_pages,
392 coalesce(1 +
393 ceil(reltuples /
394 floor((bs - pageopqdata - pagehdr) * fillfactor /
395 (100 * (4 + nulldatahdrwidth)::float))),
396 0
397 ) AS est_pages_ff,
398 bs,
399 nspname,
400 table_oid,
401 tblname,
402 idxname,
403 relpages,
404 fillfactor,
405 is_na
406 -- , stattuple.pgstatindex(quote_ident(nspname)||'.'||quote_ident(idxname)) AS pst, index_tuple_hdr_bm, maxalign, pagehdr, nulldatawidth, nulldatahdrwidth, reltuples -- (DEBUG INFO)
407 FROM (
408 SELECT maxalign,
409 bs,
410 nspname,
411 tblname,
412 idxname,
413 reltuples,
414 relpages,
415 relam,
416 table_oid,
417 fillfactor,
418 (index_tuple_hdr_bm +
419 maxalign - CASE -- Add padding to the index tuple header to align on MAXALIGN
420 WHEN index_tuple_hdr_bm % maxalign = 0 THEN maxalign
421 ELSE index_tuple_hdr_bm % maxalign
422 END
423 + nulldatawidth + maxalign - CASE -- Add padding to the data to align on MAXALIGN
424 WHEN nulldatawidth = 0 THEN 0
425 WHEN nulldatawidth::integer % maxalign = 0
426 THEN maxalign
427 ELSE nulldatawidth::integer % maxalign
428 END
429 )::numeric AS nulldatahdrwidth,
430 pagehdr,
431 pageopqdata,
432 is_na
433 -- , index_tuple_hdr_bm, nulldatawidth -- (DEBUG INFO)
434 FROM (
435 SELECT i.nspname,
436 i.tblname,
437 i.idxname,
438 i.reltuples,
439 i.relpages,
440 i.relam,
441 a.attrelid AS table_oid,
442 current_setting('block_size')::numeric AS bs,
443 fillfactor,
444 CASE -- MAXALIGN: 4 on 32bits, 8 on 64bits (and mingw32 ?)
445 WHEN version() ~ 'mingw32' OR version() ~ '64-bit|x86_64|ppc64|ia64|amd64'
446 THEN 8
447 ELSE 4
448 END AS maxalign,
449 /* per page header, fixed size: 20 for 7.X, 24 for others */
450 24 AS pagehdr,
451 /* per page btree opaque data */
452 16 AS pageopqdata,
453 /* per tuple header: add IndexAttributeBitMapData if some cols are null-able */
454 CASE
455 WHEN max(coalesce(s.null_frac, 0)) = 0
456 THEN 2 -- IndexTupleData size
457 ELSE 2 + ((32 + 8 - 1) / 8) -- IndexTupleData size + IndexAttributeBitMapData size ( max num filed per index + 8 - 1 /8)
458 END AS index_tuple_hdr_bm,
459 /* data len: we remove null values save space using it fractionnal part from stats */
460 sum((1 - coalesce(s.null_frac, 0)) * coalesce(s.avg_width, 1024)) AS nulldatawidth,
461 max(CASE WHEN a.atttypid = 'pg_catalog.name'::regtype THEN 1 ELSE 0 END) >
462 0 AS is_na
463 FROM pg_attribute AS a
464 JOIN (
465 SELECT nspname,
466 tbl.relname AS tblname,
467 idx.relname AS idxname,
468 idx.reltuples,
469 idx.relpages,
470 idx.relam,
471 indrelid,
472 indexrelid,
473 indkey::smallint[] AS attnum,
474 coalesce(substring(
475 array_to_string(idx.reloptions, ' ')
476 from 'fillfactor=([0-9]+)')::smallint, 90) AS fillfactor
477 FROM pg_index
478 JOIN pg_class idx ON idx.oid = pg_index.indexrelid
479 JOIN pg_class tbl ON tbl.oid = pg_index.indrelid
480 JOIN pg_namespace ON pg_namespace.oid = idx.relnamespace
481 WHERE pg_index.indisvalid
482 AND tbl.relkind = 'r'
483 AND idx.relpages > 0
484 ) AS i ON a.attrelid = i.indexrelid
485 JOIN pg_stats AS s ON s.schemaname = i.nspname
486 AND ((s.tablename = i.tblname AND
487 s.attname = pg_catalog.pg_get_indexdef(a.attrelid, a.attnum, TRUE)) -- stats from tbl
488 OR (s.tablename = i.idxname AND s.attname = a.attname))-- stats from functionnal cols
489 JOIN pg_type AS t ON a.atttypid = t.oid
490 WHERE a.attnum > 0
491 GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9
492 ) AS s1
493 ) AS s2
494 JOIN pg_am am ON s2.relam = am.oid
495 WHERE am.amname = 'btree'
496 ) AS sub
497 WHERE NOT is_na
498 --ORDER BY 2, 3, 4;
499
500 ) as indexstats
501order by real_size desc;