表膨胀的查询方法

文档用途

本文旨在介绍表膨胀的查询方法。

详细信息

可以通过如下sql语句定位膨胀的表。

WITH constants AS(
SELECT

current_setting('block_size')::NUMERIC AS bs,

23 AS hdr,

4 AS ma

),

bloat_info AS(

SELECT

ma,

bs,

schemaname,

tablename,

(

datawidth +(

hdr + ma -(

CASE

WHEN hdr % ma = 0 THEN ma

ELSE hdr % ma

END

)

)

)::NUMERIC AS datahdr,

(

maxfracsum*(

nullhdr + ma -(

CASE

WHEN nullhdr % ma = 0 THEN ma

ELSE nullhdr % ma

END

)

)

) AS nullhdr2

FROM

(

SELECT

schemaname,

tablename,

hdr,

ma,

bs,

SUM(( 1 - null_frac )* avg_width ) AS datawidth,

MAX( null_frac ) AS maxfracsum,

hdr +(

SELECT

1 + COUNT(*)/ 8

FROM

pg_stats s2

WHERE

null_frac <> 0

AND s2.schemaname = s.schemaname

AND s2.tablename = s.tablename

) AS nullhdr

FROM

pg_stats s,

constants

GROUP BY

1,

2,

3,

4,

5

) AS foo

),

table_bloat AS(

SELECT

schemaname,

tablename,

cc.relpages,

bs,

CEIL(

(

cc.reltuples*(

(

datahdr + ma -(

CASE

WHEN datahdr % ma = 0 THEN ma

ELSE datahdr % ma

END

)

)+ nullhdr2 + 4

)

)/(

bs - 20::FLOAT

)

) AS otta

FROM

bloat_info

JOIN pg_class cc ON

cc.relname = bloat_info.tablename

JOIN pg_namespace nn ON

cc.relnamespace = nn.oid

AND nn.nspname = bloat_info.schemaname

AND nn.nspname <> 'information_schema'

),

index_bloat AS(

SELECT

schemaname,

tablename,

bs,

COALESCE(

c2.relname,

'?'

) AS iname,

COALESCE(

c2.reltuples,

0

) AS ituples,

COALESCE(

c2.relpages,

0

) AS ipages,

COALESCE(

CEIL(

(

c2.reltuples*(

datahdr - 12

)

)/(

bs - 20::FLOAT

)

),

0

) AS iotta -- very rough approximation, assumes all cols

FROM

bloat_info

JOIN pg_class cc ON

cc.relname = bloat_info.tablename

JOIN pg_namespace nn ON

cc.relnamespace = nn.oid

AND nn.nspname = bloat_info.schemaname

AND nn.nspname <> 'information_schema'

JOIN pg_index i ON

indrelid = cc.oid

JOIN pg_class c2 ON

c2.oid = i.indexrelid

) SELECT

TYPE,

schemaname,

object_name,

bloat,

pg_size_pretty(raw_waste) AS waste

FROM

(

SELECT

'table' AS TYPE,

schemaname,

tablename AS object_name,

round(

CASE

WHEN otta = 0 THEN 0.0

ELSE table_bloat.relpages / otta::NUMERIC

END,

1

) AS bloat,

CASE

WHEN relpages < otta THEN '0'

ELSE(

bs*(

table_bloat.relpages - otta

)::BIGINT

)::BIGINT

END AS raw_waste

FROM

table_bloat

UNION SELECT

'index' AS TYPE,

schemaname,

tablename || '::' || iname AS object_name,

round(

CASE

WHEN iotta = 0

OR ipages = 0 THEN 0.0

ELSE ipages / iotta::NUMERIC

END,

1

) AS bloat,

CASE

WHEN ipages < iotta THEN '0'

ELSE(

bs*(

ipages - iotta

)

)::BIGINT

END AS raw_waste

FROM

index_bloat

) bloat_summary

ORDER BY

raw_waste DESC,

bloat DESC;