Skip to content

Instantly share code, notes, and snippets.

@lfittl

lfittl/example_queries.sql

Last active June 21, 2016 21:31
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save lfittl/3b5c77b4b376604293d978063a19d520 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save lfittl/3b5c77b4b376604293d978063a19d520 to your computer and use it in GitHub Desktop.
Download ZIP
Example structure and queries from pganalyze timeseries data for tables/indices
Raw
example_queries.sql
SELECT
size_bytes AS table_size,
(SELECT SUM(size_bytes) FROM schema_index_stats sis JOIN schema_indices si ON (si.id = sis.schema_index_id) WHERE si.table_id = st.schema_table_id AND sis.snapshot_id = st.snapshot_id) AS index_size,
COALESCE((SELECT SUM(size_bytes) FROM schema_indices si JOIN schema_index_stats sis ON (si.id = sis.schema_index_id) WHERE si.table_id = st.schema_table_id AND sis.snapshot_id = st.snapshot_id), ?) + size_bytes AS total_size,
date_part(?, s.collected_at) AS collected_at
FROM
snapshots s JOIN schema_table_stats st
ON
(snapshot_id = s.id)
WHERE
s.server_id = (SELECT server_id FROM databases WHERE id = $1) AND collected_at BETWEEN $2 AND $3 AND schema_table_id = $4
ORDER
BY collected_at
---
SELECT
size_bytes AS index_size,
date_part(?, s.collected_at) AS collected_at
FROM
snapshots s JOIN schema_index_stats
ON
(snapshot_id = s.id)
WHERE
s.server_id = (SELECT server_id FROM databases WHERE id = $1) AND s.collected_at BETWEEN $2 AND $3 AND schema_index_stats.schema_index_id = $4
ORDER
BY s.collected_at
Raw
structure_and_stats.sql
CREATE TABLE snapshots (
id integer NOT NULL,
collected_at timestamp without time zone NOT NULL,
server_id uuid NOT NULL
...
);
CREATE TABLE schema_index_stats (
id integer NOT NULL,
snapshot_id integer,
schema_index_id integer,
size_bytes bigint,
idx_blks_hit bigint,
wasted_bytes bigint,
idx_blks_read bigint,
idx_tup_fetch bigint,
idx_tup_read bigint,
idx_scan bigint
);
CREATE TABLE schema_table_stats (
id integer NOT NULL,
schema_table_id integer NOT NULL,
n_dead_tup bigint,
n_live_tup bigint,
n_tup_del bigint,
n_tup_hot_upd bigint,
n_tup_ins bigint,
n_tup_upd bigint,
wasted_bytes bigint,
size_bytes bigint,
snapshot_id integer
);
--
SELECT pg_class.relname, relpages, reltuples, pg_stat_user_tables.* FROM pg_class JOIN pg_stat_user_tables ON (oid = relid) WHERE pg_class.relname = 'schema_index_stats' OR pg_class.relname = 'schema_table_stats' OR pg_class.relname = 'snapshots';
-[ RECORD 1 ]-------+------------------------------
relname | schema_index_stats
relpages | 5423380
reltuples | 3.11496e+08
relid | 17012049
schemaname | public
relname | schema_index_stats
seq_scan | 3
seq_tup_read | 0
idx_scan | 8713404
idx_tup_fetch | 169649291
n_tup_ins | 193217938
n_tup_upd | 0
n_tup_del | 139530637
n_tup_hot_upd | 0
n_live_tup | 315119913
n_dead_tup | 28240805
n_mod_since_analyze | 3623679
last_vacuum |
last_autovacuum | 2016-06-18 04:33:06.384602+00
last_analyze | 2016-06-04 07:21:27.81162+00
last_autoanalyze | 2016-06-21 13:38:37.401849+00
vacuum_count | 0
autovacuum_count | 3
analyze_count | 1
autoanalyze_count | 17
-[ RECORD 2 ]-------+------------------------------
relname | schema_table_stats
relpages | 2756224
reltuples | 1.6766e+08
relid | 17012065
schemaname | public
relname | schema_table_stats
seq_scan | 4
seq_tup_read | 0
idx_scan | 1032293
idx_tup_fetch | 53767059
n_tup_ins | 68926509
n_tup_upd | 0
n_tup_del | 41639365
n_tup_hot_upd | 0
n_live_tup | 168463343
n_dead_tup | 6634070
n_mod_since_analyze | 5127857
last_vacuum |
last_autovacuum | 2016-06-18 23:04:33.81689+00
last_analyze | 2016-06-04 07:20:39.772836+00
last_autoanalyze | 2016-06-21 03:14:51.339085+00
vacuum_count | 0
autovacuum_count | 2
analyze_count | 1
autoanalyze_count | 11
-[ RECORD 3 ]-------+------------------------------
relname | snapshots
relpages | 21327
reltuples | 927797
relid | 17012079
schemaname | public
relname | snapshots
seq_scan | 2975
seq_tup_read | 1224552544
idx_scan | 4160600
idx_tup_fetch | 1682261064
n_tup_ins | 696341
n_tup_upd | 691974
n_tup_del | 598035
n_tup_hot_upd | 667517
n_live_tup | 944387
n_dead_tup | 8074
n_mod_since_analyze | 33125
last_vacuum |
last_autovacuum | 2016-06-21 11:30:11.35586+00
last_analyze | 2016-06-04 07:21:44.246397+00
last_autoanalyze | 2016-06-21 11:31:50.57273+00
vacuum_count | 0
autovacuum_count | 6
analyze_count | 1
autoanalyze_count | 34
--
SELECT schemaname, tablename, attname, inherited, null_frac, avg_width, n_distinct, correlation FROM pg_stats WHERE tablename IN ('schema_table_stats', 'schema_index_stats', 'snapshots');
schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | correlation
------------+--------------------+--------------------+-----------+-----------+-----------+------------+-------------
public | schema_index_stats | snapshot_id | f | 0 | 4 | 30147 | 0.758906
public | schema_index_stats | schema_index_id | f | 0 | 8 | 36669 | 0.205394
public | schema_index_stats | size_bytes | f | 0 | 8 | 12192 | 0.0971045
public | schema_index_stats | idx_blks_hit | f | 0 | 8 | 52018 | 0.0921108
public | schema_index_stats | wasted_bytes | f | 0.2881 | 8 | 6166 | 0.142847
public | schema_index_stats | idx_blks_read | f | 0 | 8 | 21590 | 0.0942811
public | schema_index_stats | idx_tup_fetch | f | 0 | 8 | 12805 | 0.419839
public | schema_index_stats | idx_scan | f | 0 | 8 | 14979 | 0.298984
public | schema_index_stats | idx_tup_read | f | 0 | 8 | 16525 | 0.374586
public | schema_index_stats | id | f | 0 | 8 | -1 | 0.758906
public | schema_table_stats | n_tup_upd | f | 0.1376 | 8 | 4124 | 0.671162
public | schema_table_stats | wasted_bytes | f | 0.3653 | 8 | 1862 | 0.429343
public | schema_table_stats | id | f | 0 | 8 | -1 | 0.789506
public | schema_table_stats | snapshot_id | f | 0 | 4 | 34356 | 0.789506
public | schema_table_stats | schema_table_id | f | 0 | 8 | 17042 | 0.113008
public | schema_table_stats | n_live_tup | f | 0.1376 | 8 | 13744 | 0.0833856
public | schema_table_stats | n_dead_tup | f | 0.1376 | 8 | 2824 | 0.643816
public | schema_table_stats | n_tup_del | f | 0.1376 | 8 | 2163 | 0.745284
public | schema_table_stats | n_tup_ins | f | 0.1376 | 8 | 11936 | 0.188945
public | schema_table_stats | size_bytes | f | 0 | 8 | 7410 | 0.0801634
public | schema_table_stats | n_tup_hot_upd | f | 0.1376 | 8 | 3580 | 0.689933
public | snapshots | collected_at | f | 0 | 8 | 76721 | -0.510791
public | snapshots | id | f | 0 | 8 | -1 | -0.510748
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment