Participant Report Optimization

PostgresSports

Optimize slow-running participant performance query by creating indexes and populating performance report table.

Created by Lingxiao Du

2025-08-18

Performance OptimizationSchema Design

Model Ranking

Click on the dots to view the trajectory of each task run

Model	Run Results	Pass@4	Pass^4	Avg Time	Avg Turns	Input Tokens	Output Tokens	Total Tokens

claude-4-sonnet	4 /4			127.0s	18.8	168,045	4,171	172,216
deepseek-chat	4 /4			186.3s	16.0	116,699	2,428	119,127
gemini-2-5-pro	4 /4			81.4s	7.0	17,064	6,777	23,841
gpt-5	4 /4			226.3s	14.0	76,428	12,950	89,378
grok-4	4 /4			130.9s	17.5	-	-	-
k2	4 /4			160.3s	15.8	108,361	2,199	110,560
o3	4 /4			61.0s	12.5	55,658	3,521	59,179
qwen-3-coder	2 /4			92.9s	18.5	137,658	2,016	139,674
claude-4-1-opus	1 /1	-	-	277.9s	22.0	123,864	4,546	128,410

Task State

Table "addresses" { "id" int4 [not null, increment] "location_id" int4 [not null] "language" varchar(100) "suite" varchar(100) "floor" varchar(100) "building" varchar(100) "street_number" varchar(100) "street_prefix" varchar(100) "street" varchar(100) "street_suffix" varchar(100) "neighborhood" varchar(100) "district" varchar(100) "locality" varchar(100) "county" varchar(100) "region" varchar(100) "postal_code" varchar(100) "country" varchar(100) } Table "affiliation_phases" { "id" int4 [not null, increment] "affiliation_id" int4 [not null] "ancestor_affiliation_id" int4 "start_season_id" int4 "start_date_time" timestamp "end_season_id" int4 "end_date_time" timestamp } Table "affiliations" { "id" int4 [not null, increment] "affiliation_key" varchar(100) [not null] "affiliation_type" varchar(100) "publisher_id" int4 [not null] } Table "affiliations_documents" { "affiliation_id" int4 [not null] "document_id" int4 [not null] } Table "affiliations_events" { "affiliation_id" int4 [not null] "event_id" int4 [not null] } Table "affiliations_media" { "affiliation_id" int4 [not null] "media_id" int4 [not null] } Table "american_football_action_participants" { "id" int4 [not null, increment] "american_football_action_play_id" int4 [not null] "person_id" int4 [not null] "participant_role" varchar(100) [not null] "score_type" varchar(100) "field_line" int4 "yardage" int4 "score_credit" int4 "yards_gained" int4 } Table "american_football_action_plays" { "id" int4 [not null, increment] "american_football_event_state_id" int4 [not null] "play_type" varchar(100) "score_attempt_type" varchar(100) "drive_result" varchar(100) "points" int4 "comment" varchar(255) } Table "american_football_defensive_stats" { "id" int4 [not null, increment] "tackles_total" varchar(100) "tackles_solo" varchar(100) "tackles_assists" varchar(100) "interceptions_total" varchar(100) "interceptions_yards" varchar(100) "interceptions_average" varchar(100) "interceptions_longest" varchar(100) "interceptions_touchdown" varchar(100) "quarterback_hurries" varchar(100) "sacks_total" varchar(100) "sacks_yards" varchar(100) "passes_defensed" varchar(100) } Table "american_football_down_progress_stats" { "id" int4 [not null, increment] "first_downs_total" varchar(100) "first_downs_pass" varchar(100) "first_downs_run" varchar(100) "first_downs_penalty" varchar(100) "conversions_third_down" varchar(100) "conversions_third_down_attempts" varchar(100) "conversions_third_down_percentage" varchar(100) "conversions_fourth_down" varchar(100) "conversions_fourth_down_attempts" varchar(100) "conversions_fourth_down_percentage" varchar(100) } Table "american_football_event_states" { "id" int4 [not null, increment] "event_id" int4 [not null] "current_state" int2 "sequence_number" int4 "period_value" int4 "period_time_elapsed" varchar(100) "period_time_remaining" varchar(100) "clock_state" varchar(100) "down" int4 "team_in_possession_id" int4 "distance_for_1st_down" int4 "field_side" varchar(100) "field_line" int4 "context" varchar(40) } Table "american_football_fumbles_stats" { "id" int4 [not null, increment] "fumbles_committed" varchar(100) "fumbles_forced" varchar(100) "fumbles_recovered" varchar(100) "fumbles_lost" varchar(100) "fumbles_yards_gained" varchar(100) "fumbles_own_committed" varchar(100) "fumbles_own_recovered" varchar(100) "fumbles_own_lost" varchar(100) "fumbles_own_yards_gained" varchar(100) "fumbles_opposing_committed" varchar(100) "fumbles_opposing_recovered" varchar(100) "fumbles_opposing_lost" varchar(100) "fumbles_opposing_yards_gained" varchar(100) } Table "american_football_offensive_stats" { "id" int4 [not null, increment] "offensive_plays_yards" varchar(100) "offensive_plays_number" varchar(100) "offensive_plays_average_yards_per" varchar(100) "possession_duration" varchar(100) "turnovers_giveaway" varchar(100) } Table "american_football_passing_stats" { "id" int4 [not null, increment] "passes_attempts" varchar(100) "passes_completions" varchar(100) "passes_percentage" varchar(100) "passes_yards_gross" varchar(100) "passes_yards_net" varchar(100) "passes_yards_lost" varchar(100) "passes_touchdowns" varchar(100) "passes_touchdowns_percentage" varchar(100) "passes_interceptions" varchar(100) "passes_interceptions_percentage" varchar(100) "passes_longest" varchar(100) "passes_average_yards_per" varchar(100) "passer_rating" varchar(100) "receptions_total" varchar(100) "receptions_yards" varchar(100) "receptions_touchdowns" varchar(100) "receptions_first_down" varchar(100) "receptions_longest" varchar(100) "receptions_average_yards_per" varchar(100) } Table "american_football_penalties_stats" { "id" int4 [not null, increment] "penalties_total" varchar(100) "penalty_yards" varchar(100) "penalty_first_downs" varchar(100) } Table "american_football_rushing_stats" { "id" int4 [not null, increment] "rushes_attempts" varchar(100) "rushes_yards" varchar(100) "rushes_touchdowns" varchar(100) "rushing_average_yards_per" varchar(100) "rushes_first_down" varchar(100) "rushes_longest" varchar(100) } Table "american_football_sacks_against_stats" { "id" int4 [not null, increment] "sacks_against_yards" varchar(100) "sacks_against_total" varchar(100) } Table "american_football_scoring_stats" { "id" int4 [not null, increment] "touchdowns_total" varchar(100) "touchdowns_passing" varchar(100) "touchdowns_rushing" varchar(100) "touchdowns_special_teams" varchar(100) "touchdowns_defensive" varchar(100) "extra_points_attempts" varchar(100) "extra_points_made" varchar(100) "extra_points_missed" varchar(100) "extra_points_blocked" varchar(100) "field_goal_attempts" varchar(100) "field_goals_made" varchar(100) "field_goals_missed" varchar(100) "field_goals_blocked" varchar(100) "safeties_against" varchar(100) "two_point_conversions_attempts" varchar(100) "two_point_conversions_made" varchar(100) "touchbacks_total" varchar(100) } Table "american_football_special_teams_stats" { "id" int4 [not null, increment] "returns_punt_total" varchar(100) "returns_punt_yards" varchar(100) "returns_punt_average" varchar(100) "returns_punt_longest" varchar(100) "returns_punt_touchdown" varchar(100) "returns_kickoff_total" varchar(100) "returns_kickoff_yards" varchar(100) "returns_kickoff_average" varchar(100) "returns_kickoff_longest" varchar(100) "returns_kickoff_touchdown" varchar(100) "returns_total" varchar(100) "returns_yards" varchar(100) "punts_total" varchar(100) "punts_yards_gross" varchar(100) "punts_yards_net" varchar(100) "punts_longest" varchar(100) "punts_inside_20" varchar(100) "punts_inside_20_percentage" varchar(100) "punts_average" varchar(100) "punts_blocked" varchar(100) "touchbacks_total" varchar(100) "touchbacks_total_percentage" varchar(100) "touchbacks_kickoffs" varchar(100) "touchbacks_kickoffs_percentage" varchar(100) "touchbacks_punts" varchar(100) "touchbacks_punts_percentage" varchar(100) "touchbacks_interceptions" varchar(100) "touchbacks_interceptions_percentage" varchar(100) "fair_catches" varchar(100) } Table "baseball_action_contact_details" { "id" int4 [not null, increment] "baseball_action_pitch_id" int4 [not null] "location" varchar(100) "strength" varchar(100) "velocity" int4 "comment" text "trajectory_coordinates" varchar(100) "trajectory_formula" varchar(100) } Table "baseball_action_pitches" { "id" int4 [not null, increment] "baseball_action_play_id" int4 [not null] "sequence_number" int4 "baseball_defensive_group_id" int4 "umpire_call" varchar(100) "pitch_location" varchar(100) "pitch_type" varchar(100) "pitch_velocity" int4 "comment" text "trajectory_coordinates" varchar(100) "trajectory_formula" varchar(100) "ball_type" varchar(40) "strike_type" varchar(40) } Table "baseball_action_plays" { "id" int4 [not null, increment] "baseball_event_state_id" int4 [not null] "play_type" varchar(100) "notation" varchar(100) "notation_yaml" text "baseball_defensive_group_id" int4 "comment" varchar(255) "runner_on_first_advance" int4 "runner_on_second_advance" int4 "runner_on_third_advance" int4 "outs_recorded" int4 "rbi" int4 "runs_scored" int4 "earned_runs_scored" varchar(100) } Table "baseball_action_substitutions" { "id" int4 [not null, increment] "baseball_event_state_id" int4 [not null] "sequence_number" int4 "person_type" varchar(100) "person_original_id" int4 "person_original_position_id" int4 "person_original_lineup_slot" int4 "person_replacing_id" int4 "person_replacing_position_id" int4 "person_replacing_lineup_slot" int4 "substitution_reason" varchar(100) "comment" varchar(100) } Table "baseball_defensive_group" { "id" int4 [not null, increment] } Table "baseball_defensive_players" { "id" int4 [not null, increment] "baseball_defensive_group_id" int4 [not null] "player_id" int4 [not null] "position_id" int4 [not null] } Table "baseball_defensive_stats" { "id" int4 [not null, increment] "double_plays" int4 "triple_plays" int4 "putouts" int4 "assists" int4 "errors" int4 "fielding_percentage" numeric "defensive_average" numeric "errors_passed_ball" int4 "errors_catchers_interference" int4 } Table "baseball_event_states" { "id" int4 [not null, increment] "event_id" int4 [not null] "current_state" int2 "sequence_number" int4 "at_bat_number" int4 "inning_value" int4 "inning_half" varchar(100) "outs" int4 "balls" int4 "strikes" int4 "runner_on_first_id" int4 "runner_on_second_id" int4 "runner_on_third_id" int4 "runner_on_first" int2 "runner_on_second" int2 "runner_on_third" int2 "runs_this_inning_half" int4 "pitcher_id" int4 "batter_id" int4 "batter_side" varchar(100) "context" varchar(40) } Table "baseball_offensive_stats" { "id" int4 [not null, increment] "average" numeric "runs_scored" int4 "at_bats" int4 "hits" int4 "rbi" int4 "total_bases" int4 "slugging_percentage" numeric "bases_on_balls" int4 "strikeouts" int4 "left_on_base" int4 "left_in_scoring_position" int4 "singles" int4 "doubles" int4 "triples" int4 "home_runs" int4 "grand_slams" int4 "at_bats_per_rbi" numeric "plate_appearances_per_rbi" numeric "at_bats_per_home_run" numeric "plate_appearances_per_home_run" numeric "sac_flies" int4 "sac_bunts" int4 "grounded_into_double_play" int4 "moved_up" int4 "on_base_percentage" numeric "stolen_bases" int4 "stolen_bases_caught" int4 "stolen_bases_average" numeric "hit_by_pitch" int4 "defensive_interferance_reaches" int4 "on_base_plus_slugging" numeric "plate_appearances" int4 "hits_extra_base" int4 } Table "baseball_pitching_stats" { "id" int4 [not null, increment] "runs_allowed" int4 "singles_allowed" int4 "doubles_allowed" int4 "triples_allowed" int4 "home_runs_allowed" int4 "innings_pitched" varchar(20) "hits" int4 "earned_runs" int4 "unearned_runs" int4 "bases_on_balls" int4 "bases_on_balls_intentional" int4 "strikeouts" int4 "strikeout_to_bb_ratio" numeric "number_of_pitches" int4 "era" numeric "inherited_runners_scored" int4 "pick_offs" int4 "errors_hit_with_pitch" int4 "errors_wild_pitch" int4 "balks" int4 "wins" int4 "losses" int4 "saves" int4 "shutouts" int4 "games_complete" int4 "games_finished" int4 "winning_percentage" numeric "event_credit" varchar(40) "save_credit" varchar(40) } Table "basketball_defensive_stats" { "id" int4 [not null, increment] "steals_total" varchar(100) "steals_per_game" varchar(100) "blocks_total" varchar(100) "blocks_per_game" varchar(100) } Table "basketball_event_states" { "id" int4 [not null, increment] "event_id" int4 [not null] "current_state" int4 "sequence_number" int4 "period_value" varchar(100) "period_time_elapsed" varchar(100) "period_time_remaining" varchar(100) "context" varchar(40) } Table "basketball_offensive_stats" { "id" int4 [not null, increment] "field_goals_made" int4 "field_goals_attempted" int4 "field_goals_percentage" varchar(100) "field_goals_per_game" varchar(100) "field_goals_attempted_per_game" varchar(100) "field_goals_percentage_adjusted" varchar(100) "three_pointers_made" int4 "three_pointers_attempted" int4 "three_pointers_percentage" varchar(100) "three_pointers_per_game" varchar(100) "three_pointers_attempted_per_game" varchar(100) "free_throws_made" varchar(100) "free_throws_attempted" varchar(100) "free_throws_percentage" varchar(100) "free_throws_per_game" varchar(100) "free_throws_attempted_per_game" varchar(100) "points_scored_total" varchar(100) "points_scored_per_game" varchar(100) "assists_total" varchar(100) "assists_per_game" varchar(100) "turnovers_total" varchar(100) "turnovers_per_game" varchar(100) "points_scored_off_turnovers" varchar(100) "points_scored_in_paint" varchar(100) "points_scored_on_second_chance" varchar(100) "points_scored_on_fast_break" varchar(100) } Table "basketball_rebounding_stats" { "id" int4 [not null, increment] "rebounds_total" varchar(100) "rebounds_per_game" varchar(100) "rebounds_defensive" varchar(100) "rebounds_offensive" varchar(100) "team_rebounds_total" varchar(100) "team_rebounds_per_game" varchar(100) "team_rebounds_defensive" varchar(100) "team_rebounds_offensive" varchar(100) } Table "basketball_team_stats" { "id" int4 [not null, increment] "timeouts_left" varchar(100) "largest_lead" varchar(100) "fouls_total" varchar(100) "turnover_margin" varchar(100) } Table "bookmakers" { "id" int4 [not null, increment] "bookmaker_key" varchar(100) "publisher_id" int4 [not null] "location_id" int4 } Table "core_person_stats" { "id" int4 [not null, increment] "time_played_event" varchar(40) "time_played_total" varchar(40) "time_played_event_average" varchar(40) "events_played" int4 "events_started" int4 "position_id" int4 } Table "core_stats" { "id" int4 [not null, increment] "score" varchar(100) "score_opposing" varchar(100) "score_attempts" varchar(100) "score_attempts_opposing" varchar(100) "score_percentage" varchar(100) "score_percentage_opposing" varchar(100) } Table "db_info" { "version" varchar(100) [not null, default: 16] } Table "display_names" { "id" int4 [not null, increment] "language" varchar(100) [not null] "entity_type" varchar(100) [not null] "entity_id" int4 [not null] "full_name" varchar(100) "first_name" varchar(100) "middle_name" varchar(100) "last_name" varchar(100) "alias" varchar(100) "abbreviation" varchar(100) "short_name" varchar(100) "prefix" varchar(20) "suffix" varchar(20) } Table "document_classes" { "id" int4 [not null, increment] "name" varchar(100) } Table "document_contents" { "id" int4 [not null, increment] "document_id" int4 [not null] "sportsml" varchar(200) "abstract" text } Table "document_fixtures" { "id" int4 [not null, increment] "fixture_key" varchar(100) "publisher_id" int4 [not null] "name" varchar(100) "document_class_id" int4 [not null] } Table "document_fixtures_events" { "id" int4 [not null, increment] "document_fixture_id" int4 [not null] "event_id" int4 [not null] "latest_document_id" int4 [not null] "last_update" timestamp } Table "document_package_entry" { "id" int4 [not null, increment] "document_package_id" int4 [not null] "rank" varchar(100) "document_id" int4 [not null] "headline" varchar(100) "short_headline" varchar(100) } Table "document_packages" { "id" int4 [not null, increment] "package_key" varchar(100) "package_name" varchar(100) "date_time" date } Table "documents" { "id" int4 [not null, increment] "doc_id" varchar(75) [not null] "publisher_id" int4 [not null] "date_time" timestamp "title" varchar(255) "language" varchar(100) "priority" varchar(100) "revision_id" varchar(75) "stats_coverage" varchar(100) "document_fixture_id" int4 [not null] "source_id" int4 "db_loading_date_time" timestamp } Table "documents_media" { "id" int4 [not null, increment] "document_id" int4 [not null] "media_id" int4 [not null] "media_caption_id" int4 [not null] } Table "events" { "id" int4 [not null, increment] "event_key" varchar(100) [not null] "publisher_id" int4 [not null] "start_date_time" timestamp "site_id" int4 "site_alignment" varchar(100) "event_status" varchar(100) "duration" varchar(100) "attendance" varchar(100) "last_update" timestamp } Table "events_documents" { "event_id" int4 [not null] "document_id" int4 [not null] } Table "events_media" { "event_id" int4 [not null] "media_id" int4 [not null] } Table "events_sub_seasons" { "event_id" int4 [not null] "sub_season_id" int4 [not null] } Table "ice_hockey_action_participants" { "id" int4 [not null, increment] "ice_hockey_action_play_id" int4 [not null] "person_id" int4 [not null] "participant_role" varchar(100) [not null] "point_credit" int4 } Table "ice_hockey_action_plays" { "id" int4 [not null, increment] "ice_hockey_event_state_id" int4 [not null] "play_type" varchar(100) "score_attempt_type" varchar(100) "play_result" varchar(100) "comment" varchar(255) } Table "ice_hockey_defensive_stats" { "id" int4 [not null, increment] "shots_power_play_allowed" varchar(100) "shots_penalty_shot_allowed" varchar(100) "goals_power_play_allowed" varchar(100) "goals_penalty_shot_allowed" varchar(100) "goals_against_average" varchar(100) "saves" varchar(100) "save_percentage" varchar(100) "penalty_killing_amount" varchar(100) "penalty_killing_percentage" varchar(100) "shots_blocked" varchar(100) "takeaways" varchar(100) "shutouts" varchar(100) "minutes_penalty_killing" varchar(100) "hits" varchar(100) "goals_empty_net_allowed" varchar(100) "goals_short_handed_allowed" varchar(100) "goals_shootout_allowed" varchar(100) "shots_shootout_allowed" varchar(100) } Table "ice_hockey_event_states" { "id" int4 [not null, increment] "event_id" int4 [not null] "current_state" int4 "sequence_number" int4 "period_value" varchar(100) "period_time_elapsed" varchar(100) "period_time_remaining" varchar(100) "context" varchar(40) } Table "ice_hockey_offensive_stats" { "id" int4 [not null, increment] "goals_game_winning" varchar(100) "goals_game_tying" varchar(100) "goals_power_play" varchar(100) "goals_short_handed" varchar(100) "goals_even_strength" varchar(100) "goals_empty_net" varchar(100) "goals_overtime" varchar(100) "goals_shootout" varchar(100) "goals_penalty_shot" varchar(100) "assists" varchar(100) "points" varchar(100) "power_play_amount" varchar(100) "power_play_percentage" varchar(100) "shots_penalty_shot_taken" varchar(100) "shots_penalty_shot_missed" varchar(100) "shots_penalty_shot_percentage" varchar(100) "giveaways" varchar(100) "minutes_power_play" varchar(100) "faceoff_wins" varchar(100) "faceoff_losses" varchar(100) "faceoff_win_percentage" varchar(100) "scoring_chances" varchar(100) } Table "ice_hockey_player_stats" { "id" int4 [not null, increment] "plus_minus" varchar(100) } Table "injury_phases" { "id" int4 [not null, increment] "person_id" int4 [not null] "injury_status" varchar(100) "injury_type" varchar(100) "injury_comment" varchar(100) "disabled_list" varchar(100) "start_date_time" timestamp "end_date_time" timestamp "season_id" int4 "phase_type" varchar(100) "injury_side" varchar(100) } Table "key_aliases" { "id" int4 [not null, increment] "key_id" int4 [not null] "key_root_id" int4 [not null] } Table "key_roots" { "id" int4 [not null, increment] "key_type" varchar(100) } Table "latest_revisions" { "id" int4 [not null, increment] "revision_id" varchar(75) [not null] "latest_document_id" int4 [not null] } Table "locations" { "id" int4 [not null, increment] "timezone" varchar(100) "latitude" varchar(100) "longitude" varchar(100) "country_code" varchar(100) } Table "media" { "id" int4 [not null, increment] "object_id" int4 "source_id" int4 "revision_id" int4 "media_type" varchar(100) "publisher_id" int4 [not null] "date_time" varchar(100) "credit_id" int4 [not null] "db_loading_date_time" timestamp "creation_location_id" int4 [not null] } Table "media_captions" { "id" int4 [not null, increment] "media_id" int4 [not null] "caption_type" varchar(100) "caption" varchar(100) "caption_author_id" int4 [not null] "language" varchar(100) "caption_size" varchar(100) } Table "media_contents" { "id" int4 [not null, increment] "media_id" int4 [not null] "object" varchar(100) "format" varchar(100) "mime_type" varchar(100) "height" varchar(100) "width" varchar(100) "duration" varchar(100) "file_size" varchar(100) "resolution" varchar(100) } Table "media_keywords" { "id" int4 [not null, increment] "keyword" varchar(100) "media_id" int4 [not null] } Table "motor_racing_event_states" { "id" int4 [not null, increment] "event_id" int4 [not null] "current_state" int4 "sequence_number" int4 "lap" varchar(100) "laps_remaining" varchar(100) "time_elapsed" varchar(100) "flag_state" varchar(100) "context" varchar(40) } Table "motor_racing_qualifying_stats" { "id" int4 [not null, increment] "grid" varchar(100) "pole_position" varchar(100) "pole_wins" varchar(100) "qualifying_speed" varchar(100) "qualifying_speed_units" varchar(100) "qualifying_time" varchar(100) "qualifying_position" varchar(100) } Table "motor_racing_race_stats" { "id" int4 [not null, increment] "time_behind_leader" varchar(100) "laps_behind_leader" varchar(100) "time_ahead_follower" varchar(100) "laps_ahead_follower" varchar(100) "time" varchar(100) "points" varchar(100) "points_rookie" varchar(100) "bonus" varchar(100) "laps_completed" varchar(100) "laps_leading_total" varchar(100) "distance_leading" varchar(100) "distance_completed" varchar(100) "distance_units" varchar(40) "speed_average" varchar(40) "speed_units" varchar(40) "status" varchar(40) "finishes_top_5" varchar(40) "finishes_top_10" varchar(40) "starts" varchar(40) "finishes" varchar(40) "non_finishes" varchar(40) "wins" varchar(40) "races_leading" varchar(40) "money" varchar(40) "money_units" varchar(40) "leads_total" varchar(40) } Table "outcome_totals" { "id" int4 [not null, increment] "standing_subgroup_id" int4 [not null] "outcome_holder_type" varchar(100) "outcome_holder_id" int4 "rank" varchar(100) "wins" varchar(100) "losses" varchar(100) "ties" varchar(100) "undecideds" varchar(100) "winning_percentage" varchar(100) "points_scored_for" varchar(100) "points_scored_against" varchar(100) "points_difference" varchar(100) "standing_points" varchar(100) "streak_type" varchar(100) "streak_duration" varchar(100) "streak_total" varchar(100) "streak_start" date "streak_end" date } Table "participants_events" { "id" int4 [not null, increment] "participant_type" varchar(100) [not null] "participant_id" int4 [not null] "event_id" int4 [not null] "alignment" varchar(100) "score" varchar(100) "event_outcome" varchar(100) "rank" int4 } Table "periods" { "id" int4 [not null, increment] "participant_event_id" int4 [not null] "period_value" varchar(100) "score" varchar(100) } Table "person_event_metadata" { "id" int4 [not null, increment] "person_id" int4 [not null] "event_id" int4 [not null] "status" varchar(100) "health" varchar(100) "weight" varchar(100) "role_id" int4 "position_id" int4 "team_id" int4 "lineup_slot" int4 "lineup_slot_sequence" int4 } Table "person_phases" { "id" int4 [not null, increment] "person_id" int4 [not null] "membership_type" varchar(40) [not null] "membership_id" int4 [not null] "role_id" int4 "role_status" varchar(40) "phase_status" varchar(40) "uniform_number" varchar(20) "regular_position_id" int4 "regular_position_depth" varchar(40) "height" varchar(100) "weight" varchar(100) "start_date_time" timestamp "start_season_id" int4 "end_date_time" timestamp "end_season_id" int4 "entry_reason" varchar(40) "exit_reason" varchar(40) "selection_level" int4 "selection_sublevel" int4 "selection_overall" int4 } Table "persons" { "id" int4 [not null, increment] "person_key" varchar(100) [not null] "publisher_id" int4 [not null] "gender" varchar(20) "birth_date" varchar(30) "death_date" varchar(30) "birth_location_id" int4 "hometown_location_id" int4 "residence_location_id" int4 "death_location_id" int4 } Table "persons_documents" { "person_id" int4 [not null] "document_id" int4 [not null] } Table "persons_media" { "person_id" int4 [not null] "media_id" int4 [not null] } Table "positions" { "id" int4 [not null, increment] "affiliation_id" int4 [not null] "abbreviation" varchar(100) [not null] } Table "publishers" { "id" int4 [not null, increment] "publisher_key" varchar(100) [not null] "publisher_name" varchar(100) } Table "roles" { "id" int4 [not null, increment] "role_key" varchar(100) [not null] "role_name" varchar(100) "comment" varchar(100) } Table "seasons" { "id" int4 [not null, increment] "season_key" int4 [not null] "publisher_id" int4 [not null] "league_id" int4 [not null] "start_date_time" timestamp "end_date_time" timestamp } Table "sites" { "id" int4 [not null, increment] "site_key" int4 [not null] "publisher_id" int4 [not null] "location_id" int4 } Table "soccer_defensive_stats" { "id" int4 [not null, increment] "shots_penalty_shot_allowed" varchar(100) "goals_penalty_shot_allowed" varchar(100) "goals_against_average" varchar(100) "goals_against_total" varchar(100) "saves" varchar(100) "save_percentage" varchar(100) "catches_punches" varchar(100) "shots_on_goal_total" varchar(100) "shots_shootout_total" varchar(100) "shots_shootout_allowed" varchar(100) "shots_blocked" varchar(100) "shutouts" varchar(100) } Table "soccer_event_states" { "id" int4 [not null, increment] "event_id" int4 [not null] "current_state" int4 "sequence_number" int4 "period_value" varchar(100) "period_time_elapsed" varchar(100) "period_time_remaining" varchar(100) "minutes_elapsed" varchar(100) "period_minute_elapsed" varchar(100) "context" varchar(40) } Table "soccer_foul_stats" { "id" int4 [not null, increment] "fouls_suffered" varchar(100) "fouls_commited" varchar(100) "cautions_total" varchar(100) "cautions_pending" varchar(100) "caution_points_total" varchar(100) "caution_points_pending" varchar(100) "ejections_total" varchar(100) } Table "soccer_offensive_stats" { "id" int4 [not null, increment] "goals_game_winning" varchar(100) "goals_game_tying" varchar(100) "goals_overtime" varchar(100) "goals_shootout" varchar(100) "goals_total" varchar(100) "assists_game_winning" varchar(100) "assists_game_tying" varchar(100) "assists_overtime" varchar(100) "assists_total" varchar(100) "points" varchar(100) "shots_total" varchar(100) "shots_on_goal_total" varchar(100) "shots_hit_frame" varchar(100) "shots_penalty_shot_taken" varchar(100) "shots_penalty_shot_scored" varchar(100) "shots_penalty_shot_missed" varchar(40) "shots_penalty_shot_percentage" varchar(40) "shots_shootout_taken" varchar(40) "shots_shootout_scored" varchar(40) "shots_shootout_missed" varchar(40) "shots_shootout_percentage" varchar(40) "giveaways" varchar(40) "offsides" varchar(40) "corner_kicks" varchar(40) "hat_tricks" varchar(40) } Table "standing_subgroups" { "id" int4 [not null, increment] "standing_id" int4 [not null] "affiliation_id" int4 [not null] } Table "standings" { "id" int4 [not null, increment] "affiliation_id" int4 [not null] "standing_type" varchar(100) "sub_season_id" int4 [not null] "last_updated" varchar(100) "duration_scope" varchar(100) "competition_scope" varchar(100) "competition_scope_id" varchar(100) "alignment_scope" varchar(100) "site_scope" varchar(100) "scoping_label" varchar(100) "publisher_id" int4 [not null] "source" varchar(100) } Table "stats" { "id" int4 [not null, increment] "stat_repository_type" varchar(100) "stat_repository_id" int4 [not null] "stat_holder_type" varchar(100) "stat_holder_id" int4 "stat_coverage_type" varchar(100) "stat_coverage_id" int4 "context" varchar(40) [not null] } Table "sub_periods" { "id" int4 [not null, increment] "period_id" int4 [not null] "sub_period_value" varchar(100) "score" varchar(100) } Table "sub_seasons" { "id" int4 [not null, increment] "sub_season_key" varchar(100) [not null] "season_id" int4 [not null] "sub_season_type" varchar(100) [not null] "start_date_time" timestamp "end_date_time" timestamp } Table "team_american_football_stats" { "id" int4 [not null, increment] "yards_per_attempt" varchar(100) "average_starting_position" varchar(100) "timeouts" varchar(100) "time_of_possession" varchar(100) "turnover_ratio" varchar(100) } Table "team_phases" { "id" int4 [not null, increment] "team_id" int4 [not null] "start_season_id" int4 "end_season_id" int4 "affiliation_id" int4 [not null] "start_date_time" varchar(100) "end_date_time" varchar(100) "phase_status" varchar(40) "role_id" int4 } Table "teams" { "id" int4 [not null, increment] "team_key" varchar(100) [not null] "publisher_id" int4 [not null] "home_site_id" int4 } Table "teams_documents" { "team_id" int4 [not null] "document_id" int4 [not null] } Table "teams_media" { "team_id" int4 [not null] "media_id" int4 [not null] } Table "tennis_action_points" { "id" int4 [not null, increment] "sub_period_id" varchar(100) "sequence_number" varchar(100) "win_type" varchar(100) } Table "tennis_action_volleys" { "id" int4 [not null, increment] "sequence_number" varchar(100) "tennis_action_points_id" int4 "landing_location" varchar(100) "swing_type" varchar(100) "result" varchar(100) "spin_type" varchar(100) "trajectory_details" varchar(100) } Table "tennis_event_states" { "id" int4 [not null, increment] "event_id" int4 [not null] "current_state" int4 "sequence_number" int4 "tennis_set" varchar(100) "game" varchar(100) "server_person_id" int4 "server_score" varchar(100) "receiver_person_id" int4 "receiver_score" varchar(100) "service_number" varchar(100) "context" varchar(40) } Table "tennis_return_stats" { "id" int4 [not null, increment] "returns_played" varchar(100) "matches_played" varchar(100) "first_service_return_points_won" varchar(100) "first_service_return_points_won_pct" varchar(100) "second_service_return_points_won" varchar(100) "second_service_return_points_won_pct" varchar(100) "return_games_played" varchar(100) "return_games_won" varchar(100) "return_games_won_pct" varchar(100) "break_points_played" varchar(100) "break_points_converted" varchar(100) "break_points_converted_pct" varchar(100) } Table "tennis_service_stats" { "id" int4 [not null, increment] "services_played" varchar(100) "matches_played" varchar(100) "aces" varchar(100) "first_services_good" varchar(100) "first_services_good_pct" varchar(100) "first_service_points_won" varchar(100) "first_service_points_won_pct" varchar(100) "second_service_points_won" varchar(100) "second_service_points_won_pct" varchar(100) "service_games_played" varchar(100) "service_games_won" varchar(100) "service_games_won_pct" varchar(100) "break_points_played" varchar(100) "break_points_saved" varchar(100) "break_points_saved_pct" varchar(100) } Table "wagering_moneylines" { "id" int4 [not null, increment] "bookmaker_id" int4 [not null] "event_id" int4 [not null] "date_time" timestamp "team_id" int4 [not null] "person_id" int4 "rotation_key" varchar(100) "comment" varchar(100) "vigorish" varchar(100) "line" varchar(100) "line_opening" varchar(100) "prediction" varchar(100) } Table "wagering_odds_lines" { "id" int4 [not null, increment] "bookmaker_id" int4 [not null] "event_id" int4 [not null] "date_time" timestamp "team_id" int4 [not null] "person_id" int4 "rotation_key" varchar(100) "comment" varchar(100) "numerator" varchar(100) "denominator" varchar(100) "prediction" varchar(100) "payout_calculation" varchar(100) "payout_amount" varchar(100) } Table "wagering_runlines" { "id" int4 [not null, increment] "bookmaker_id" int4 [not null] "event_id" int4 [not null] "date_time" timestamp "team_id" int4 [not null] "person_id" int4 "rotation_key" varchar(100) "comment" varchar(100) "vigorish" varchar(100) "line" varchar(100) "line_opening" varchar(100) "line_value" varchar(100) "prediction" varchar(100) } Table "wagering_straight_spread_lines" { "id" int4 [not null, increment] "bookmaker_id" int4 [not null] "event_id" int4 [not null] "date_time" timestamp "team_id" int4 [not null] "person_id" int4 "rotation_key" varchar(100) "comment" varchar(100) "vigorish" varchar(100) "line_value" varchar(100) "line_value_opening" varchar(100) "prediction" varchar(100) } Table "wagering_total_score_lines" { "id" int4 [not null, increment] "bookmaker_id" int4 [not null] "event_id" int4 [not null] "date_time" timestamp "team_id" int4 [not null] "person_id" int4 "rotation_key" varchar(100) "comment" varchar(100) "vigorish" varchar(100) "line_over" varchar(100) "line_under" varchar(100) "total" varchar(100) "total_opening" varchar(100) "prediction" varchar(100) } Table "weather_conditions" { "id" int4 [not null, increment] "event_id" int4 [not null] "temperature" varchar(100) "temperature_units" varchar(40) "humidity" varchar(100) "clouds" varchar(100) "wind_direction" varchar(100) "wind_velocity" varchar(100) "weather_code" varchar(100) }

Instruction

Verify

Python

"""
Verification script for PostgreSQL Sports Task 3: Query Performance Optimization
"""

import os
import sys
import psycopg2
from decimal import Decimal

def rows_match(actual_row, expected_row):
    """
    Compare two rows with appropriate tolerance.
    For Decimal types: allows 0.001 tolerance
    For other types: requires exact match
    """
    if len(actual_row) != len(expected_row):
        return False
    
    for actual, expected in zip(actual_row, expected_row):
        if isinstance(actual, Decimal) and isinstance(expected, Decimal):
            if abs(float(actual) - float(expected)) > 0.001:
                return False
        elif isinstance(actual, float) and isinstance(expected, float):
            if abs(actual - expected) > 0.001:
                return False
        elif actual != expected:
            return False
    
    return True

def get_connection_params() -> dict:
    """Get database connection parameters."""
    return {
        "host": os.getenv("POSTGRES_HOST", "localhost"),
        "port": int(os.getenv("POSTGRES_PORT", 5432)),
        "database": os.getenv("POSTGRES_DATABASE", "sports"),
        "user": os.getenv("POSTGRES_USERNAME", "postgres"),
        "password": os.getenv("POSTGRES_PASSWORD", "postgres")
    }

def verify_report_data(conn) -> bool:
    """Verify the report table contains the expected data."""
    with conn.cursor() as cur:
        # Get actual results from the report table
        cur.execute("""
            SELECT participant_id, event_count, stat_count, stat_type_count, last_event_date
            FROM participant_performance_report
            ORDER BY participant_id
        """)
        actual_results = cur.fetchall()
        
        if len(actual_results) == 0:
            print("❌ Report table is empty")
            return False
        
        # Execute ground truth query
        cur.execute("""
            SELECT 
                pe.participant_id,
                COUNT(pe.event_id) as event_count,
                (SELECT COUNT(*) FROM stats s WHERE s.stat_holder_id = pe.participant_id AND s.stat_holder_type = 'persons') as stat_count,
                (SELECT COUNT(DISTINCT s.stat_repository_type) FROM stats s WHERE s.stat_holder_id = pe.participant_id AND s.stat_holder_type = 'persons') as stat_type_count,
                (SELECT MAX(e.start_date_time) FROM events e JOIN participants_events pe2 ON e.id = pe2.event_id WHERE pe2.participant_id = pe.participant_id) as last_event_date
            FROM participants_events pe 
            WHERE pe.participant_id <= 50
            GROUP BY pe.participant_id
            ORDER BY pe.participant_id
        """)
        expected_results = cur.fetchall()

        if len(actual_results) != len(expected_results):
            print(f"❌ Expected {len(expected_results)} report records, got {len(actual_results)}")
            return False

        mismatches = 0
        for actual, expected in zip(actual_results, expected_results):
            if not rows_match(actual, expected):
                if mismatches < 5:
                    print(f"❌ Row mismatch: expected {expected}, got {actual}")
                mismatches += 1

        if mismatches > 0:
            print(f"❌ Total mismatches in report data: {mismatches}")
            return False

        print(f"✅ Report data is correct ({len(actual_results)} records)")
        return True

def verify_performance_optimization(conn) -> bool:
    """Verify that key performance optimization indexes have been implemented."""
    with conn.cursor() as cur:
        print("\n🔍 Checking for critical performance indexes...")
        
        # Check 1: participants_events.participant_id index (critical for subqueries)
        cur.execute("""
            SELECT indexname, indexdef 
            FROM pg_indexes 
            WHERE schemaname = 'public' 
            AND tablename = 'participants_events'
            AND indexdef LIKE '%participant_id%'
        """)
        participant_indexes = cur.fetchall()
        has_participant_index = len(participant_indexes) > 0
        
        # Check 2: stats table optimization (critical for subquery filtering)
        cur.execute("""
            SELECT indexname, indexdef 
            FROM pg_indexes 
            WHERE schemaname = 'public' 
            AND tablename = 'stats'
            AND indexdef LIKE '%stat_holder_type%'
            AND indexdef LIKE '%stat_holder_id%'
        """)
        stats_indexes = cur.fetchall()
        has_stats_index = len(stats_indexes) > 0
        
        # Report findings
        critical_indexes_found = 0
        
        if has_participant_index:
            print("✅ Found participant filtering index on participants_events.participant_id")
            critical_indexes_found += 1
        else:
            print("❌ Missing critical index on participants_events.participant_id")
            
        if has_stats_index:
            print("✅ Found subquery optimization index on stats table")
            critical_indexes_found += 1
        else:
            print("❌ Missing critical index on stats table")
        
        # Must have both critical indexes for this subquery-heavy query
        if critical_indexes_found >= 2:
            print(f"\n✅ Performance optimization: PASS ({critical_indexes_found}/2 critical indexes found)")
            return True
        else:
            print(f"\n❌ Performance optimization: FAIL ({critical_indexes_found}/2 critical indexes found)")
            print("   Create these critical indexes:")
            print("   - CREATE INDEX ON participants_events(participant_id);")
            print("   - CREATE INDEX ON stats(stat_holder_type, stat_holder_id);")
            return False

def main():
    """Main verification function."""
    print("=" * 50)
    print("Verifying Sports Task 3: Query Performance Optimization")
    print("=" * 50)

    # Get connection parameters
    conn_params = get_connection_params()

    if not conn_params["database"]:
        print("❌ No database specified")
        sys.exit(1)

    try:
        # Connect to database
        conn = psycopg2.connect(**conn_params)

        # Verify all components
        success = (
            verify_report_data(conn) and
            verify_performance_optimization(conn)
        )

        conn.close()

        if success:
            print("\n🎉 Task verification: PASS")
            sys.exit(0)
        else:
            print("\n❌ Task verification: FAIL")
            sys.exit(1)

    except psycopg2.Error as e:
        print(f"❌ Database error: {e}")
        sys.exit(1)
    except Exception as e:
        print(f"❌ Verification error: {e}")
        sys.exit(1)

if __name__ == "__main__":
    main()