Vincent Chéry VincentAntoine

2 followers · 0 following

France

View GitHub Profile

Recently created

Least recently created

Recently updated

Least recently updated

VincentAntoine / seq_scan_index_scan_per_table.sql

Created November 26, 2024 13:05

Postgresql - Volume de sequential scan / index scan par table

	WITH stats_reset AS (
	SELECT EXTRACT(
	EPOCH FROM (
	CURRENT_TIMESTAMP -
	(
	SELECT stats_reset FROM pg_stat_statements_info
	)
	)
	) / 3600 AS hours_since_stats_reset
	),

VincentAntoine / pgstattuple.sql

Last active November 26, 2024 13:04

Postgresql - Analyse du bloat des tables du schéma `public` avec `pgstattuple`

	WITH t AS (
	SELECT
	s.relid,
	s.relname,
	last_autovacuum,
	last_vacuum
	FROM pg_stat_user_tables s
	WHERE s.schemaname = 'public'
	)

VincentAntoine / pg_stat_statements_query.sql

Last active November 26, 2024 13:06

Postgresql - Statistiques d'exécution de requêtes avec `pg_stat_statements`

	WITH stats_reset AS (
	SELECT EXTRACT(
	EPOCH FROM (
	CURRENT_TIMESTAMP -
	(
	SELECT stats_reset FROM pg_stat_statements_info
	)
	)
	) / 3600 AS hours_since_stats_reset
	),

VincentAntoine / hypertable_missing_indexes.sql

Last active July 24, 2024 16:08

Sums the number of index scans and sequential scans performed on TimescaleDB hypertables to identify potentially missing indexes (tables with a significant number of sequential scans).

	SELECT
	ht.schema_name,
	ht.table_name,
	COUNT(DISTINCT ck.table_name) AS nb_of_chunks,
	SUM(stat.seq_scan) AS seq_scan,
	SUM(stat.idx_scan) AS idx_scan,
	SUM(stat.idx_scan) - SUM(stat.seq_scan) AS difference,
	CASE WHEN SUM(stat.idx_scan) < SUM(stat.seq_scan) THEN 'Missing index ?' ELSE 'OK' END AS missing_index
	FROM _timescaledb_catalog.hypertable ht
	JOIN _timescaledb_catalog.chunk ck ON ht.id = ck.hypertable_id

VincentAntoine / timescaledb_tables_and_hypertables_sizes.sql

Last active February 18, 2025 18:47

Query to list all tables and hypertables in a Postgresql / TimescaleDB database with their total size

	WITH chunks AS (
	SELECT
	ht.schema_name AS hypertable_schema,
	ht.table_name AS hypertable_name,
	ck.table_name AS chunk_name
	FROM _timescaledb_catalog.hypertable ht
	JOIN _timescaledb_catalog.chunk ck
	ON ht.id = ck.hypertable_id
	),