Matt N. matthew-n

Processing JSON using jq

jq is useful to slice, filter, map and transform structured json data.

Installing jq

On Mac OS

brew install jq

Visualize PostGIS index using Gevel in Postgres 9.6:

git clone git://sigaev.ru/gevel
cd gevel
git checkout bd8b8b031a8049a6e7c18c00946bfbd99d75d27f
USE_PGXS=1 make
sudo USE_PGXS=1 make install
psql -f /usr/share/postgresql/9.6/contrib/gevel.sql

MIT License

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

	WITH btree_index_atts AS (
	SELECT nspname, relname, reltuples, relpages, indrelid, relam,
	regexp_split_to_table(indkey::text, ' ')::smallint AS attnum,
	indexrelid as index_oid
	FROM pg_index
	JOIN pg_class ON pg_class.oid=pg_index.indexrelid
	JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
	JOIN pg_am ON pg_class.relam = pg_am.oid
	WHERE pg_am.amname = 'btree'
	),

	WITH btree_index_atts AS (
	SELECT nspname, relname, reltuples, relpages, indrelid, relam,
	regexp_split_to_table(indkey::text, ' ')::smallint AS attnum,
	indexrelid as index_oid
	FROM pg_index
	JOIN pg_class ON pg_class.oid=pg_index.indexrelid
	JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
	JOIN pg_am ON pg_class.relam = pg_am.oid
	WHERE pg_am.amname = 'btree'
	),

	WITH table_scans as (
	SELECT relid,
	tables.idx_scan + tables.seq_scan as all_scans,
	( tables.n_tup_ins + tables.n_tup_upd + tables.n_tup_del ) as writes,
	pg_relation_size(relid) as table_size
	FROM pg_stat_user_tables as tables
	),
	all_writes as (
	SELECT sum(writes) as total_writes
	FROM table_scans

	-- check for exact matches
	WITH index_cols_ord as (
	SELECT attrelid, attnum, attname
	FROM pg_attribute
	JOIN pg_index ON indexrelid = attrelid
	WHERE indkey[0] > 0
	ORDER BY attrelid, attnum
	),
	index_col_list AS (
	SELECT attrelid,

	-- check for containment
	-- i.e. index A contains index B
	-- and both share the same first column
	-- but they are NOT identical

	WITH index_cols_ord as (
	SELECT attrelid, attnum, attname
	FROM pg_attribute
	JOIN pg_index ON indexrelid = attrelid
	WHERE indkey[0] > 0

	WITH
	write_adjust AS (
	-- change the below to 1.0 if pg_stats goes back to
	-- the creation of the database
	SELECT 0.0 AS adjustment
	),
	index_usage AS (
	SELECT sut.relid,
	current_database() AS database,
	sut.schemaname::text as schema_name,

	select
	test,
	query::char(48),
	instance,
	--scale,
	--clients,
	round(mean_time::numeric, 4) mean_time,
	round(stddev_time::numeric, 4) stddev_time,
	round(((mean_time - mean_time_lag)::numeric / mean_time::numeric )*100, 1) as mean_time_diff,
	round(((stddev_time - stddev_time_lag)::numeric / stddev_time::numeric )*100, 1) as stddev_time_diff