feat(dashboard): add query texts to TopN charts and table with smart truncation

Nikolay Samokhvalovrequested to merge
claude/add-query-texts-Gqxkk into main

Summary

Add human-readable SQL query texts to Dashboard 2 (Query Performance Analysis) - both in the detailed table view and in all TopN chart legends. Includes configurable truncation options and expanded top_n values.

Key Features

1. Query Texts in TopN Chart Legends

  • All 23 TopN panels now show query text instead of just queryid numbers
  • Uses Prometheus "info metrics" pattern with group_left join
  • New Flask endpoint /query_info_metrics generates pgwatch_query_info metrics
  • VictoriaMetrics scrapes this endpoint every 60s

2. Legend Format Options (toggleable via dropdown)

Option Description
Smart (short) Extracts table names: SELECT ... FROM users, orders [queryid]
Smart (medium) Same extraction, 60 chars
Smart (long) Same extraction, 100 chars
Raw (short) Simple truncation: select * from users whe... [queryid]
Raw (medium) Simple truncation, 60 chars
Raw (long) Simple truncation, 100 chars
Full text Complete query up to 500 chars
QueryID only Just the numeric ID

3. Full QueryID in Legends

  • Legends include complete queryid with minus sign: SELECT ... FROM users [-1002918973244469490]
  • Enables easy identification and searching

4. Expanded top_n Values

  • Now supports: 5, 10, 15, 20, 50, 100, 500

5. Query Texts in Detailed Table

  • Added "Query Text" column to pg_stat_statements table view
  • Fetched from PostgreSQL sink database (pgss_queryid_queries table)

Technical Implementation

  • Flask Backend (monitoring_flask_backend/app.py):

    • smart_truncate_query() - extracts table names, CTEs from SQL
    • /query_info_metrics endpoint - Prometheus exposition format
    • /query_texts endpoint - JSON for table view

  • Prometheus Config (config/prometheus/prometheus.yml):

    • Added query-info scrape job targeting Flask backend

  • Dashboard (Dashboard_2_Aggregated_query_analysis.json):

    • All TopN panels use * on(queryid) group_left(...) pgwatch_query_info
    • legend_label variable controls truncation format
    • top_n variable expanded with more options

Test Plan

  • Verify TopN charts show query text legends
  • Verify all legend format options work
  • Verify full queryid (with minus sign) displays correctly
  • Verify top_n dropdown shows all options (5-500)
  • Verify query texts in detailed table view

Closes #87 (closed)

Edited by Nikolay Samokhvalov

Merge request reports