Customer Analytics Optimization

PostgresDVD Rental

Optimize slow customer analytics query with correlated subqueries causing timeout issues in reporting dashboard.

Created by Lingxiao Du

2025-08-20

Performance Optimization

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			157.1s	19.5	226,481	5,220	231,700
deepseek-chat	4 /4			280.8s	20.3	293,968	3,473	297,441
k2	4 /4			181.3s	16.3	96,654	3,695	100,349
gpt-5	3 /4			275.3s	10.0	41,760	15,116	56,875
grok-4	3 /4			126.8s	11.8	-	-	-
qwen-3-coder	3 /4			100.7s	18.8	136,422	3,207	139,629
claude-4-1-opus	1 /1	-	-	263.7s	14.0	362,694	4,122	366,816
o3	1 /4			53.0s	6.3	16,688	3,329	20,017
gemini-2-5-pro	0 /4			60.5s	3.8	11,830	5,112	16,942

Task State

Enum "mpaa_rating" { "G" "PG" "PG-13" "R" "NC-17" } Table "customer" { "customer_id" int4 [pk, not null, increment] "store_id" int2 [not null] "first_name" varchar(45) [not null] "last_name" varchar(45) [not null] "email" varchar(50) "address_id" int2 [not null] "activebool" bool [not null, default: true] "create_date" date [not null, default: `('now'::text)::date`] "last_update" timestamp [default: `now()`] "active" int4 Indexes { address_id [type: btree, name: "idx_fk_address_id"] store_id [type: btree, name: "idx_fk_store_id"] last_name [type: btree, name: "idx_last_name"] } } Table "actor" { "actor_id" int4 [pk, not null, increment] "first_name" varchar(45) [not null] "last_name" varchar(45) [not null] "last_update" timestamp [not null, default: `now()`] Indexes { last_name [type: btree, name: "idx_actor_last_name"] } } Table "category" { "category_id" int4 [pk, not null, increment] "name" varchar(25) [not null] "last_update" timestamp [not null, default: `now()`] } Table "film" { "film_id" int4 [pk, not null, increment] "title" varchar(255) [not null] "description" text "release_year" int4 "language_id" int2 [not null] "rental_duration" int2 [not null, default: 3] "rental_rate" numeric(4,2) [not null, default: 4.99] "length" int2 "replacement_cost" numeric(5,2) [not null, default: 19.99] "rating" mpaa_rating [default: 'G'] "last_update" timestamp [not null, default: `now()`] "special_features" "text[]" "fulltext" tsvector [not null] Indexes { fulltext [type: gist, name: "film_fulltext_idx"] language_id [type: btree, name: "idx_fk_language_id"] title [type: btree, name: "idx_title"] } } Table "film_actor" { "actor_id" int2 [not null] "film_id" int2 [not null] "last_update" timestamp [not null, default: `now()`] Indexes { (actor_id, film_id) [type: btree, name: "film_actor_pkey"] film_id [type: btree, name: "idx_fk_film_id"] } } Table "film_category" { "film_id" int2 [not null] "category_id" int2 [not null] "last_update" timestamp [not null, default: `now()`] Indexes { (film_id, category_id) [type: btree, name: "film_category_pkey"] } } Table "address" { "address_id" int4 [pk, not null, increment] "address" varchar(50) [not null] "address2" varchar(50) "district" varchar(20) [not null] "city_id" int2 [not null] "postal_code" varchar(10) "phone" varchar(20) [not null] "last_update" timestamp [not null, default: `now()`] Indexes { city_id [type: btree, name: "idx_fk_city_id"] } } Table "city" { "city_id" int4 [pk, not null, increment] "city" varchar(50) [not null] "country_id" int2 [not null] "last_update" timestamp [not null, default: `now()`] Indexes { country_id [type: btree, name: "idx_fk_country_id"] } } Table "country" { "country_id" int4 [pk, not null, increment] "country" varchar(50) [not null] "last_update" timestamp [not null, default: `now()`] } Table "inventory" { "inventory_id" int4 [pk, not null, increment] "film_id" int2 [not null] "store_id" int2 [not null] "last_update" timestamp [not null, default: `now()`] Indexes { (store_id, film_id) [type: btree, name: "idx_store_id_film_id"] } } Table "language" { "language_id" int4 [pk, not null, increment] "name" bpchar(20) [not null] "last_update" timestamp [not null, default: `now()`] } Table "payment" { "payment_id" int4 [pk, not null, increment] "customer_id" int2 [not null] "staff_id" int2 [not null] "rental_id" int4 [not null] "amount" numeric(5,2) [not null] "payment_date" timestamp [not null] Indexes { rental_id [type: btree, name: "idx_fk_rental_id"] staff_id [type: btree, name: "idx_fk_staff_id"] } } Table "rental" { "rental_id" int4 [pk, not null, increment] "rental_date" timestamp [not null] "inventory_id" int4 [not null] "customer_id" int2 [not null] "return_date" timestamp "staff_id" int2 [not null] "last_update" timestamp [not null, default: `now()`] Indexes { (rental_date, inventory_id, customer_id) [type: btree, name: "idx_unq_rental_rental_date_inventory_id_customer_id"] inventory_id [type: btree, name: "idx_fk_inventory_id"] } } Table "staff" { "staff_id" int4 [pk, not null, increment] "first_name" varchar(45) [not null] "last_name" varchar(45) [not null] "address_id" int2 [not null] "email" varchar(50) "store_id" int2 [not null] "active" bool [not null, default: true] "username" varchar(16) [not null] "password" varchar(40) "last_update" timestamp [not null, default: `now()`] "picture" bytea } Table "store" { "store_id" int4 [pk, not null, increment] "manager_staff_id" int2 [unique, not null] "address_id" int2 [not null] "last_update" timestamp [not null, default: `now()`] } Ref "fk_address_city":"city"."city_id" < "address"."city_id" Ref "fk_city":"country"."country_id" < "city"."country_id" Ref "customer_address_id_fkey":"address"."address_id" < "customer"."address_id" [update: cascade, delete: restrict] Ref "film_language_id_fkey":"language"."language_id" < "film"."language_id" [update: cascade, delete: restrict] Ref "film_actor_actor_id_fkey":"actor"."actor_id" < "film_actor"."actor_id" [update: cascade, delete: restrict] Ref "film_actor_film_id_fkey":"film"."film_id" < "film_actor"."film_id" [update: cascade, delete: restrict] Ref "film_category_category_id_fkey":"category"."category_id" < "film_category"."category_id" [update: cascade, delete: restrict] Ref "film_category_film_id_fkey":"film"."film_id" < "film_category"."film_id" [update: cascade, delete: restrict] Ref "inventory_film_id_fkey":"film"."film_id" < "inventory"."film_id" [update: cascade, delete: restrict] Ref "payment_customer_id_fkey":"customer"."customer_id" < "payment"."customer_id" [update: cascade, delete: restrict] Ref "payment_rental_id_fkey":"rental"."rental_id" < "payment"."rental_id" [update: cascade, delete: set null] Ref "payment_staff_id_fkey":"staff"."staff_id" < "payment"."staff_id" [update: cascade, delete: restrict] Ref "rental_customer_id_fkey":"customer"."customer_id" < "rental"."customer_id" [update: cascade, delete: restrict] Ref "rental_inventory_id_fkey":"inventory"."inventory_id" < "rental"."inventory_id" [update: cascade, delete: restrict] Ref "rental_staff_id_key":"staff"."staff_id" < "rental"."staff_id" Ref "staff_address_id_fkey":"address"."address_id" < "staff"."address_id" [update: cascade, delete: restrict] Ref "store_address_id_fkey":"address"."address_id" < "store"."address_id" [update: cascade, delete: restrict] Ref "store_manager_staff_id_fkey":"staff"."staff_id" < "store"."manager_staff_id" [update: cascade, delete: restrict]

Instruction

Verify

Python

"""
Verification script for PostgreSQL Task 1: Customer Payment Query Optimization
"""

import os
import sys
import psycopg2

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"),
        "user": os.getenv("POSTGRES_USERNAME"),
        "password": os.getenv("POSTGRES_PASSWORD")
    }

def check_payment_customer_id_index(conn) -> bool:
    """Check if there's any index on payment.customer_id column."""
    with conn.cursor() as cur:
        cur.execute("""
            SELECT indexname, indexdef 
            FROM pg_indexes 
            WHERE schemaname = 'public' 
            AND tablename = 'payment'
            AND indexdef LIKE '%customer_id%'
        """)
        indexes = cur.fetchall()
        print(indexes)
        return len(indexes) > 0, indexes

def main():
    """Main verification function."""
    print("=" * 60)
    print("PostgreSQL Task 1 Verification: Customer Payment Query Optimization")
    print("=" * 60)
    
    # 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)
        
        print("\n🔍 Checking for customer_id index on payment table...")
        
        # Check if any index exists on payment.customer_id
        has_index, indexes = check_payment_customer_id_index(conn)
        
        if has_index:
            print("✅ Found index(es) on payment.customer_id:")
            for index_name, index_def in indexes:
                print(f"   - {index_name}: {index_def}")
        else:
            print("❌ No index found on payment.customer_id column")
        
        conn.close()
        
        if has_index:
            print(f"\n🎉 Task verification: PASS")
            print(f"   - Index on payment.customer_id exists")
            sys.exit(0)
        else:
            print(f"\n❌ Task verification: FAIL")
            print(f"   - No index found on payment.customer_id")
            print(f"   - Create an index on payment(customer_id) to optimize the queries")
            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()