Back to Repositories

Testing PostgreSQL Index Optimization Workflows in pghero

This comprehensive test suite validates database indexing functionality in the pghero library, focusing on optimal index selection for various SQL queries and database operations. The tests cover a wide range of SQL scenarios including WHERE clauses, ORDER BY statements, and different comparison operators.

Test Coverage Overview

The test suite provides extensive coverage of database indexing scenarios including:

Single and multiple column indexing
WHERE clause conditions with various operators (=, IN, LIKE, BETWEEN)
ORDER BY clauses with different sorting directions
NULL value handling
Prepared statements
Edge cases like system tables and small tables

Implementation Analysis

The testing approach utilizes Minitest framework with custom assertions for index validation. The implementation follows a structured pattern of testing index recommendations against specific SQL queries, using a combination of positive test cases for valid indexing scenarios and negative test cases for situations where indexes should not be recommended.

Technical Details

Testing Framework: Minitest
Custom Assertions: assert_best_index and assert_no_index
Database Integration: PostgreSQL specific features
Test Helper Integration
Mock Database Scenarios

Best Practices Demonstrated

The test suite exemplifies several testing best practices:

Comprehensive edge case coverage
Clear test naming conventions
Isolated test cases
Structured test organization
Proper error case handling
Consistent assertion patterns

ankane/pghero

test/best_index_test.rb

            
require_relative "test_helper"

class BestIndexTest < Minitest::Test
  def test_where
    assert_best_index ({table: "users", columns: ["city_id"]}), "SELECT * FROM users WHERE city_id = 1"
  end

  def test_all_values
    index = database.best_index("SELECT * FROM users WHERE login_attempts = 1 ORDER BY created_at")
    expected = {
      found: true,
      structure: {table: "users", where: [{column: "login_attempts", op: "="}], sort: [{column: "created_at", direction: "asc"}]},
      index: {table: "users", columns: ["login_attempts", "created_at"]},
      rows: 5000,
      row_estimates: {"login_attempts (=)" => 167, "created_at (sort)" => 1},
      row_progression: [5000, 167, 0]
    }
    assert_equal expected, index
  end

  def test_where_multiple_columns
    assert_best_index ({table: "users", columns: ["city_id", "login_attempts"]}), "SELECT * FROM users WHERE city_id = 1 and login_attempts = 2"
  end

  def test_where_unique
    assert_best_index ({table: "users", columns: ["email"]}), "SELECT * FROM users WHERE city_id = 1 AND email = '[email protected]'"
  end

  def test_order
    assert_best_index ({table: "users", columns: ["created_at"]}), "SELECT * FROM users ORDER BY created_at"
  end

  def test_order_multiple
    assert_best_index ({table: "users", columns: ["login_attempts", "created_at"]}), "SELECT * FROM users ORDER BY login_attempts, created_at"
  end

  def test_order_multiple_direction
    assert_best_index ({table: "users", columns: ["login_attempts"]}), "SELECT * FROM users ORDER BY login_attempts DESC, created_at"
  end

  def test_order_multiple_unique
    assert_best_index ({table: "users", columns: ["id"]}), "SELECT * FROM users ORDER BY id, created_at"
  end

  def test_where_unique_order
    assert_best_index ({table: "users", columns: ["email"]}), "SELECT * FROM users WHERE email = '[email protected]' ORDER BY created_at"
  end

  def test_where_order
    assert_best_index ({table: "users", columns: ["login_attempts", "created_at"]}), "SELECT * FROM users WHERE login_attempts = 1 ORDER BY created_at"
  end

  def test_where_order_unknown
    assert_best_index ({table: "users", columns: ["login_attempts"]}), "SELECT * FROM users WHERE login_attempts = 1 ORDER BY NOW()"
  end

  def test_where_in
    assert_best_index ({table: "users", columns: ["city_id"]}), "SELECT * FROM users WHERE city_id IN (1, 2)"
  end

  def test_like
    assert_best_index ({table: "users", columns: ["email gist_trgm_ops"], using: "gist"}), "SELECT * FROM users WHERE email LIKE $1"
  end

  def test_like_where
    assert_best_index ({table: "users", columns: ["city_id"]}), "SELECT * FROM users WHERE city_id = $1 AND email LIKE $2"
  end

  def test_like_where2
    assert_best_index ({table: "users", columns: ["email gist_trgm_ops"], using: "gist"}), "SELECT * FROM users WHERE email LIKE $1 AND active = $2"
  end

  def test_ilike
    assert_best_index ({table: "users", columns: ["email gist_trgm_ops"], using: "gist"}), "SELECT * FROM users WHERE email ILIKE $1"
  end

  def test_not_equals
    assert_best_index ({table: "users", columns: ["login_attempts"]}), "SELECT * FROM users WHERE city_id != $1 and login_attempts = 2"
  end

  def test_not_in
    assert_best_index ({table: "users", columns: ["login_attempts"]}), "SELECT * FROM users WHERE city_id NOT IN ($1) and login_attempts = 2"
  end

  def test_between
    assert_best_index ({table: "users", columns: ["city_id"]}), "SELECT * FROM users WHERE city_id BETWEEN 1 AND 2"
  end

  def test_multiple_range
    assert_best_index ({table: "users", columns: ["city_id"]}), "SELECT * FROM users WHERE city_id > $1 and login_attempts > $2"
  end

  def test_where_prepared
    assert_best_index ({table: "users", columns: ["city_id"]}), "SELECT * FROM users WHERE city_id = $1"
  end

  def test_where_normalized
    assert_best_index ({table: "users", columns: ["city_id"]}), "SELECT * FROM users WHERE city_id = $1"
  end

  def test_is_null
    assert_best_index ({table: "users", columns: ["zip_code"]}), "SELECT * FROM users WHERE zip_code IS NULL"
  end

  def test_is_null_equal
    assert_best_index ({table: "users", columns: ["zip_code", "login_attempts"]}), "SELECT * FROM users WHERE zip_code IS NULL AND login_attempts = $1"
  end

  def test_is_not_null
    assert_best_index ({table: "users", columns: ["login_attempts"]}), "SELECT * FROM users WHERE zip_code IS NOT NULL AND login_attempts = $1"
  end

  def test_update
    assert_best_index ({table: "users", columns: ["city_id"]}), "UPDATE users SET email = 'test' WHERE city_id = 1"
  end

  def test_delete
    assert_best_index ({table: "users", columns: ["city_id"]}), "DELETE FROM users WHERE city_id = 1"
  end

  def test_parse_error
    assert_no_index "Parse error", "SELECT *123'"
  end

  def test_stats_not_found
    assert_no_index "Stats not found", "SELECT * FROM non_existent_table WHERE id = 1"
  end

  def test_unknown_structure
    assert_no_index "Unknown structure", "SELECT NOW()"
  end

  def test_where_or
    assert_no_index "Unknown structure", "SELECT FROM users WHERE login_attempts = 0 OR login_attempts = 1"
  end

  def test_where_nested_or
    assert_no_index "Unknown structure", "SELECT FROM users WHERE city_id = 1 AND (login_attempts = 0 OR login_attempts = 1)"
  end

  def test_multiple_tables
    assert_no_index "JOIN not supported yet", "SELECT * FROM users INNER JOIN cities ON cities.id = users.city_id"
  end

  def test_no_columns
    assert_no_index "No columns to index", "SELECT * FROM users"
  end

  def test_small_table
    assert_no_index "No index needed if less than 500 rows", "SELECT * FROM states WHERE name = 'State 1'"
  end

  def test_system_table
    assert_no_index "System table", "SELECT COUNT(*) AS count FROM pg_extension WHERE extname = $1"
  end

  def test_insert
    assert_no_index "INSERT statement", "INSERT INTO users (login_attempts) VALUES (1)"
  end

  def test_set
    assert_no_index "SET statement", "set client_encoding to 'UTF8'"
  end

  def test_empty_statement
    assert_no_index "Empty statement", nil
  end

  protected

  def assert_best_index(expected, statement)
    index = database.best_index(statement)
    assert_nil index[:explanation]
    assert index[:found]
    assert_equal expected, index[:index]
  end

  def assert_no_index(explanation, statement)
    index = database.best_index(statement)
    assert !index[:found]
    assert_equal explanation, index[:explanation]
  end
end