Ingénieur SQL Senior et Expert en Optimisation de Requêtes

Mode Detected: Build Mode Database Flavour: PostgreSQL DB Version: 15 Query Goal: Retrieve all columns for rows where the `name` column contains the string "john" (case-insensitive) Data Volume Est.: Unknown (assume up to 1M rows) Performance Goal: Sub-second response Security Context: User input involved – parameterisation required --- ### STEP 2 - Schema & Requirements Analysis **Assumption:** A table named `users` exists with at least a `name` column. No schema was provided, so the following is assumed: | Table | Key Columns | Data Types | Estimated Rows | Existing Indexes | |-------|-------------|------------|----------------|-----------------| | users | id (PK), name | id: INTEGER, name: TEXT | 1,000,000 | PK on id | **Query Requirements Breakdown:** - **Data Needed:** All columns (`SELECT *`) - **Joins Required:** None - **Filter Conditions:** `name` ILIKE '%john%' (case-insensitive substring match) - **Aggregations:** None - **Sorting/Paging:** None specified - **Subqueries:** None --- ### STEP 4 - Execution Plan Simulation **Query Execution Order:** 1. **FROM:** `users` table – full table scan predicted (no index on `name`) 2. **WHERE:** `name ILIKE '%john%'` – non-SARGable due to leading wildcard; index on `name` (if exists) would be bypassed 3. **SELECT:** All columns retrieved 4. **ORDER BY:** None 5. **LIMIT/OFFSET:** None **Operation Cost Analysis:** | Operation | Type | Index Used | Cost Estimate | Risk | |-----------|------|------------|---------------|------| | Table Access | Full Table Scan | None | High (O(N)) | Critical – scans all 1M rows | **Overall Complexity:** - **Current Query Cost:** High (full table scan) - **Primary Bottleneck:** Non-SARGable `ILIKE` with leading wildcard - **Optimisation Potential:** Medium (limited by substring search requirement) --- ### STEP 5 - Index Strategy **Index Recommendations:** | # | Table | Columns | Index Type | Reason | Expected Impact | |---|-------|---------|------------|--------|----------------| | 1 | users | name | B-Tree (standard) | Supports equality and prefix searches | Minimal – leading wildcard bypasses it | | 2 | users | name | Full-Text (GIN) | Supports tokenised text search | High – enables fast substring/token matching | **Exact DDL Statements:** ```sql -- Standard B-Tree index (limited benefit for leading wildcard) CREATE INDEX idx_users_name_btree ON users(name); -- Full-text index for efficient substring search CREATE INDEX idx_users_name_gin ON users USING gin(to_tsvector('simple', name)); ``` **Index Warnings:** - The B-Tree index will **not** help with `ILIKE '%john%'` due to the leading wildcard. - The GIN full-text index requires query rewrite to use `to_tsquery` or `plainto_tsquery` – not a drop-in fix for `ILIKE`. - Alternative: Consider `pg_trgm` extension for trigram-based indexing (best for substring ILIKE). **Recommended Additional Index (PostgreSQL-specific):** ```sql -- Enable pg_trgm extension (requires superuser or appropriate privileges) CREATE EXTENSION IF NOT EXISTS pg_trgm; -- Trigram index – optimises ILIKE '%pattern%' queries CREATE INDEX idx_users_name_trgm ON users USING gin (name gin_trgm_ops); ``` --- ### STEP 6 - Final Production Query ```sql -- ============================================================ -- Query : Search users by name substring (case-insensitive) -- Author : Generated -- DB : PostgreSQL 15 -- Tables : users -- Indexes : idx_users_name_trgm (recommended) -- Params : $1 (search term, e.g., 'john') -- ============================================================ SELECT * FROM users WHERE name ILIKE '%' || $1 || '%'; ``` **Notes:** - Parameterised with `$1` to prevent SQL injection. - Relies on `pg_trgm` GIN index for performance. - If `pg_trgm` is unavailable, fallback is full table scan – acceptable for small datasets. --- ### STEP 7 - Query Summary Card **Query Overview:** - Mode : Build - Database : PostgreSQL 15 - Tables Involved : 1 - Query Complexity: Simple **Query Health Card:** | Area | Status | Notes | |-----------------------|----------|-------------------------------------------| | Index Coverage | WARN | Requires pg_trgm index for performance | | Parameterization | OK | Uses $1 placeholder | | Anti-Patterns | WARN | Leading wildcard ILIKE is non-SARGable | | Join Efficiency | OK | No joins | | SQL Injection Safe | OK | Parameterised query | | DB Flavor Optimized | OK | Uses PostgreSQL-specific pg_trgm | | Execution Plan Score | WARN | Full scan without proper index | **Indexes to Create:** 1 – `idx_users_name_trgm` (plus `CREATE EXTENSION pg_trgm`) **Indexes to Drop:** 0 **Security Fixes:** 0 (already parameterised) **Recommended Next Steps:** - Run `CREATE EXTENSION IF NOT EXISTS pg_trgm;` and create the trigram index. - Validate with: `EXPLAIN ANALYZE SELECT * FROM users WHERE name ILIKE '%john%';` - Monitor performance; consider `LIMIT` if result sets are large.