Back to Repositories

Testing PostgreSQL Index Suggestions in PgHero

This test suite validates PostgreSQL index suggestions functionality in PgHero, covering various index types and query patterns. It ensures the database properly identifies and recommends optimal indexes based on query analysis and data access patterns.

Test Coverage Overview

The test suite provides comprehensive coverage of PostgreSQL index suggestion scenarios:

Basic index suggestions for column queries
Existing index detection
Primary key index handling
Hash index cases including multiple values
Special index types: GIST, BRIN, GIN, LTREE
Network address (INET) indexing

Implementation Analysis

The testing approach uses Minitest framework with systematic validation of index suggestions across different query patterns. Each test case isolates specific index scenarios, validating the suggested_indexes and suggested_indexes_by_query methods against expected index recommendations.

The implementation leverages database version checks and query stats management for accurate testing.

Technical Details

Testing Framework: Minitest
Database Integration: PostgreSQL
Setup Requirements: Database connection, query stats reset capability
Test Data: User model with various column types (jsonb, inet, timestamp, etc.)

Best Practices Demonstrated

The test suite exemplifies robust testing practices through isolated test cases, clear setup procedures, and comprehensive edge case coverage. Each test focuses on a specific index type or query pattern, ensuring maintainable and reliable validation of index suggestion functionality.

Isolated test cases for each index type
Proper setup and teardown management
Comprehensive query pattern coverage
Version-specific functionality handling

ankane/pghero

test/suggested_indexes_test.rb

            
require_relative "test_helper"

class SuggestedIndexesTest < Minitest::Test
  def setup
    if database.server_version_num >= 120000
      database.reset_query_stats
    else
      database.reset_instance_query_stats
    end
  end

  def test_suggested_indexes_enabled
    assert database.suggested_indexes_enabled?
  end

  def test_basic
    User.where(email: "[email protected]").first
    assert_equal [{table: "users", columns: ["email"]}], database.suggested_indexes.map { |q| q.except(:queries, :details) }
  end

  def test_existing_index
    User.where("updated_at > ?", Time.now).to_a
    assert_equal [], database.suggested_indexes.map { |q| q.except(:queries, :details) }
  end

  def test_primary_key
    query = "SELECT * FROM users WHERE id = 1"
    result = database.suggested_indexes_by_query(queries: [query])[query]
    assert_equal ["id"], result[:covering_index]
  end

  def test_hash
    query = "SELECT * FROM users WHERE login_attempts = 1"
    result = database.suggested_indexes_by_query(queries: [query])[query]
    assert_equal ["login_attempts"], result[:covering_index]
  end

  def test_hash_multiple_values
    query = "SELECT * FROM users WHERE login_attempts IN (1, 2)"
    result = database.suggested_indexes_by_query(queries: [query])[query]
    assert_equal ["login_attempts"], result[:covering_index]
  end

  def test_hash_greater_than
    query = "SELECT * FROM users WHERE login_attempts > 1"
    result = database.suggested_indexes_by_query(queries: [query])[query]
    assert_nil result[:covering_index]
  end

  def test_gist_trgm
    query = "SELECT * FROM users WHERE country = 'Test 1'"
    result = database.suggested_indexes_by_query(queries: [query])[query]
    assert_nil result[:covering_index]
  end

  def test_ltree
    query = "SELECT * FROM users WHERE tree_path = 'path1'"
    result = database.suggested_indexes_by_query(queries: [query])[query]
    assert_equal ["tree_path"], result[:covering_index]
  end

  def test_range
    query = "SELECT * FROM users WHERE range = '[0, 0]'"
    result = database.suggested_indexes_by_query(queries: [query])[query]
    assert_equal ["range"], result[:covering_index]
  end

  def test_inet
    query = "SELECT * FROM users WHERE last_known_ip = '127.0.0.1'"
    result = database.suggested_indexes_by_query(queries: [query])[query]
    assert_equal ["last_known_ip inet_ops"], result[:covering_index]
  end

  def test_inet_greater_than
    query = "SELECT * FROM users WHERE last_known_ip > '127.0.0.1'"
    result = database.suggested_indexes_by_query(queries: [query])[query]
    assert_equal ["last_known_ip inet_ops"], result[:covering_index]
  end

  def test_brin
    query = "SELECT * FROM users WHERE created_at = NOW()"
    result = database.suggested_indexes_by_query(queries: [query])[query]
    assert_equal ["created_at"], result[:covering_index]
  end

  def test_brin_order
    query = "SELECT * FROM users ORDER BY created_at LIMIT 1"
    result = database.suggested_indexes_by_query(queries: [query])[query]
    assert_nil result[:covering_index]
  end

  def test_gin
    query = "SELECT * FROM users WHERE metadata = '{}'::jsonb"
    result = database.suggested_indexes_by_query(queries: [query])[query]
    assert_nil result[:covering_index]
  end
end