pg_flame postgresql EXPLAIN ANALYZE 火焰图工具
pg_flame 是golang 编写的一个将pg的EXPLAIN ANALYZE 转换为火焰图，使用简单
以下是一个简单的demo
环境准备
- docker-compose 文件
version: "3"
services:
postgres:
image: postgres:9.6.11
ports:
- "5432:5432"
environment:
- "POSTGRES_PASSWORD:dalong"
- 源码编译
clone 代码
git clone <a href="https://github.com/mgartner/pg_flame.git">https://github.com/mgartner/pg_flame.git</a>
goreleaser release --snapshot --skip-publish --rm-dist
简单修改.goreleaser.yml支持windows
# This is an example goreleaser.yaml file with some sane defaults.
# Make sure to check the documentation at http://goreleaser.com
builds:
- env:
- CGO_ENABLED=0
goos:
- linux
- darwin
- windows
archives:
- replacements:
darwin: Darwin
linux: Linux
windows: windows
386: i386
amd64: x86_64
checksum:
name_template: 'checksums.txt'
snapshot:
name_template: "{{ .Tag }}-next"
changelog:
sort: asc
- sql 脚本
CREATE TABLE users (
id SERIAL PRIMARY KEY,
userename text,
userage integer
);
-- Indices -------------------------------------------------------
CREATE UNIQUE INDEX users_pkey ON users(id int4_ops);
CREATE TABLE userapps (
id SERIAL PRIMARY KEY,
appname text,
appversion text,
userid integer
);
-- Indices -------------------------------------------------------
CREATE UNIQUE INDEX userapps_pkey ON userapps(id int4_ops);
INSERT INTO "public"."userapps"("id","appname","appversion","userid")
VALUES
(1,E'login',E'v1',1),
(2,E'view',E'v2.0',2);
INSERT INTO "public"."users"("id","userename","userage")
VALUES
(1,E'dalong',11),
(2,E'app',22);
使用
- 启动pg
docker-compose up -d
- 生成json 格式的 EXPLAIN ANALYZE
psql -U postgres -h 127.0.0.1 postgres -qAtc 'EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) select * from userapps a join users b on a.id=b.id' > plan.json
- 生成报告结果
cat plan.json | ./dist/pg_flame_darwin_amd64/pg_flame > result.html
- 效果
说明
pg_flame 多平台构建使用了goreleaser，如果需要构建跨平台的语言包，需要安装，而且上边数据比较少，看的不是很明显
