Technologies/PostgreSQL/postgresql.index.scans
PostgreSQLPostgreSQLMetric

postgresql.index.scans

Index scans initiated
Dimensions:None
Available on:DatadogDatadog (2)Native (1)OpenTelemetryOpenTelemetry (1)PrometheusPrometheus (1)
Interface Metrics (5)
DatadogDatadog
Enabled with `relations`. The overall number of index scans initiated on this table. This metric is tagged with db, schema, table.
Dimensions:None
DatadogDatadog
Enabled with `relations`. The number of index scans initiated on this table. This metric is tagged with db, schema, table, index
Dimensions:None
Native
last_idx_scan statistic from pg_stat_all_indexes
Dimensions:None
OpenTelemetryOpenTelemetry
The number of index scans on a table.
Dimensions:None
PrometheusPrometheus
Number of index scans initiated on this table
Dimensions:None
Sources
postgresql.index_rel_scansgithub.com
postgresql.index_scansgithub.com
last_idx_scangithub.com
postgresql.index.scansgithub.com
stat_user_tables_idx_scangithub.com

Technical Annotations (20)

Configuration Parameters (1)
default_statistics_targetrecommended: 1000
Increased from default 100 to improve planner estimates for skewed data distributions
CLI Commands (8)
CLUSTER identities_identity USING index_name;remediation
ALTER TABLE identities_identity ALTER COLUMN email_domain SET STATISTICS 10000;diagnostic
VACUUM ANALYZE identities_identity;remediation
ALTER TABLE your_table ALTER COLUMN status SET STATISTICS 500;remediation
SELECT indexrelname AS index_name, relname AS table_name, idx_scan AS times_used, pg_size_pretty(pg_relation_size(indexrelid)) AS index_size FROM pg_stat_user_indexes WHERE idx_scan = 0 AND indexrelid NOT IN (SELECT indexrelid FROM pg_index WHERE indisprimary) ORDER BY pg_relation_size(indexrelid) DESC;diagnostic
DROP INDEX index_name;remediation
EXPLAIN ANALYZE SELECT * FROM table_name WHERE column_name = 'value'diagnostic
SELECT schemaname, relname AS table_name, seq_scan, idx_scan, n_tup_ins AS inserts, n_tup_upd AS updates, n_tup_del AS deletes, n_dead_tup AS dead_tuples, last_vacuum, last_autovacuum FROM pg_stat_user_tables ORDER BY n_dead_tup DESC;diagnostic
Technical References (11)
correlationconceptLIMIT clauseconceptvarchar_pattern_opscomponentpg_stat_user_indexescomponenthistogramconceptselectivityconceptcardinality estimatesconceptWHERE predicateconceptpg_stat_user_tablescomponentseq_scancomponentidx_scancomponent
Related Insights (6)
Query planner chooses sequential scan over index scan when data distribution is skewedwarning
▸
Index bloat wastes disk space and degrades query performancewarning
▸
Insufficient column statistics cause poor selectivity estimateswarning
▸
Unused indexes waste write performance and storageinfo
▸
Missing indexes force sequential scans on small result setswarning
▸
High sequential scan to index scan ratio indicates missing indexeswarning
▸