Optimisation des requêtes PostgreSQL : de lent à sub-milliseconde | Akousa

                                    QUERY PLAN
---------------------------------------------------------------------------
 Index Scan using idx_orders_customer_id on orders  (cost=0.43..8.45 rows=1 width=128)
   Index Cond: (customer_id = 42)

                                    QUERY PLAN
---------------------------------------------------------------------------
 Index Scan using idx_orders_customer_id on orders  (cost=0.43..8.45 rows=1 width=128)
                                                    (actual time=0.021..0.023 rows=1 loops=1)
   Index Cond: (customer_id = 42)
 Planning Time: 0.085 ms
 Execution Time: 0.041 ms

                                    QUERY PLAN
---------------------------------------------------------------------------
 Hash Join  (cost=425.00..1893.25 rows=2150 width=48)
            (actual time=2.341..15.892 rows=2347 loops=1)
   Hash Cond: (o.customer_id = c.id)
   Buffers: shared hit=312 read=45
   ->  Bitmap Heap Scan on orders o  (cost=12.50..1450.75 rows=2150 width=32)
                                     (actual time=0.512..12.105 rows=2347 loops=1)
         Recheck Cond: (created_at > '2026-01-01'::date)
         Filter: (status = 'completed')
         Rows Removed by Filter: 893
         Heap Blocks: exact=189
         Buffers: shared hit=195 read=45
         ->  Bitmap Index Scan on idx_orders_created_at  (cost=0.00..11.96 rows=3043 width=0)
                                                         (actual time=0.287..0.287 rows=3240 loops=1)
               Index Cond: (created_at > '2026-01-01'::date)
               Buffers: shared hit=12
   ->  Hash  (cost=287.50..287.50 rows=10000 width=24)
             (actual time=1.753..1.753 rows=10000 loops=1)
         Buffers: shared hit=117
         ->  Seq Scan on customers c  (cost=0.00..287.50 rows=10000 width=24)
                                      (actual time=0.008..0.892 rows=10000 loops=1)
               Buffers: shared hit=117
 Planning Time: 0.215 ms
 Execution Time: 16.147 ms

Bitmap Heap Scan on orders  (cost=45.00..1250.00 rows=5000 width=128)
  Recheck Cond: (created_at > '2026-01-01')
  ->  Bitmap Index Scan on idx_orders_created_at  (cost=0.00..43.75 rows=5000 width=0)
        Index Cond: (created_at > '2026-01-01')

Bitmap Heap Scan on orders
  Recheck Cond: ((status = 'completed') AND (created_at > '2026-01-01'))
  ->  BitmapAnd
        ->  Bitmap Index Scan on idx_orders_status
              Index Cond: (status = 'completed')
        ->  Bitmap Index Scan on idx_orders_created_at
              Index Cond: (created_at > '2026-01-01')

sql

-- Query: WHERE status = 'active' AND created_at > '2026-01-01' ORDER BY total DESC
 
-- Good: equality first, then range, then sort
CREATE INDEX idx_orders_optimized ON orders (status, created_at, total);
 
-- Bad: range before equality
CREATE INDEX idx_orders_bad ON orders (created_at, status, total);

sql

-- Another common pattern: soft deletes
CREATE INDEX idx_users_active_email ON users (email)
WHERE deleted_at IS NULL;
 
-- Unique constraint only on active records
CREATE UNIQUE INDEX idx_users_unique_email ON users (email)
WHERE deleted_at IS NULL;

sql

-- JSONB containment queries
CREATE INDEX idx_products_metadata ON products USING GIN (metadata);
 
-- Now this is fast:
SELECT * FROM products WHERE metadata @> '{"color": "red"}';
 
-- Full-text search
CREATE INDEX idx_articles_search ON articles USING GIN (to_tsvector('english', title || ' ' || body));

sql

-- IP range lookups
CREATE INDEX idx_ip_ranges ON ip_blocks USING GiST (ip_range);
 
SELECT * FROM ip_blocks WHERE ip_range @> '192.168.1.50'::inet;
 
-- Temporal ranges (scheduling conflicts)
CREATE INDEX idx_bookings_period ON bookings USING GiST (
  tstzrange(start_time, end_time)
);
 
-- Find overlapping bookings:
SELECT * FROM bookings
WHERE tstzrange(start_time, end_time) && tstzrange('2026-03-01 10:00', '2026-03-01 12:00');

sql

-- Good for equality-only lookups on large text columns
CREATE INDEX idx_sessions_token ON sessions USING HASH (session_token);
 
-- This is fast:
SELECT * FROM sessions WHERE session_token = 'abc123def456...';
 
-- This CANNOT use the hash index:
SELECT * FROM sessions WHERE session_token LIKE 'abc%';

sql

-- Find unused indexes
SELECT
  schemaname, tablename, indexname,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelid NOT IN (
    SELECT conindid FROM pg_constraint
    WHERE contype IN ('p', 'u')  -- exclude primary key and unique constraints
  )
ORDER BY pg_relation_size(indexrelid) DESC;

sql

-- Find duplicate indexes (same columns, same order)
SELECT
  a.indexrelid::regclass AS index1,
  b.indexrelid::regclass AS index2,
  a.indrelid::regclass AS table_name
FROM pg_index a
JOIN pg_index b ON a.indrelid = b.indrelid
  AND a.indexrelid < b.indexrelid
  AND a.indkey = b.indkey;

python

# Python / SQLAlchemy — the classic trap
orders = session.query(Order).filter_by(status='pending').all()
 
for order in orders:
    # This triggers a separate query for EACH order
    print(f"Customer: {order.customer.name}")

sql

-- Query 1: Get all pending orders
SELECT * FROM orders WHERE status = 'pending';
 
-- Query 2: Get customer for order 1
SELECT * FROM customers WHERE id = 101;
 
-- Query 3: Get customer for order 2
SELECT * FROM customers WHERE id = 102;
 
-- ...Query 502: Get customer for order 500
SELECT * FROM customers WHERE id = 600;

sql

-- Log all queries that take more than 0ms (i.e., all queries)
SET log_min_duration_statement = 0;
 
-- Better for production: log only slow queries
ALTER SYSTEM SET log_min_duration_statement = 50;  -- 50ms threshold
SELECT pg_reload_conf();

python

# SQLAlchemy — joinedload fetches customers in the same query
from sqlalchemy.orm import joinedload
 
orders = (
    session.query(Order)
    .options(joinedload(Order.customer))
    .filter_by(status='pending')
    .all()
)

sql

-- Approach 1: Single JOIN (might produce duplicate data if one-to-many)
SELECT o.*, i.product_name, i.quantity
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.status = 'pending';
 
-- Approach 2: Two queries (less data transfer if orders have many columns)
SELECT * FROM orders WHERE status = 'pending';
-- application collects order_ids = [1, 2, 3, ...]
SELECT * FROM order_items WHERE order_id = ANY($1);

sql

-- Subquery in WHERE — often fine, sometimes terrible
SELECT * FROM orders
WHERE customer_id IN (
  SELECT id FROM customers WHERE country = 'US'
);
 
-- JOIN — usually the best choice
SELECT o.* FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.country = 'US';
 
-- CTE — materialized in PG 11 and below, optimized in PG 12+
WITH us_customers AS (
  SELECT id FROM customers WHERE country = 'US'
)
SELECT o.* FROM orders o
JOIN us_customers uc ON uc.id = o.customer_id;

sql

-- EXISTS — stops at first match, great for checking existence
SELECT * FROM orders o
WHERE EXISTS (
  SELECT 1 FROM returns r WHERE r.order_id = o.id
);
 
-- IN with subquery — builds full result set first
SELECT * FROM orders
WHERE id IN (SELECT order_id FROM returns);
 
-- JOIN — can produce duplicates if returns has multiple rows per order
SELECT DISTINCT o.* FROM orders o
JOIN returns r ON r.order_id = o.id;

sql

-- Bad: fetches all 25 columns including a TEXT column with kilobytes of data
SELECT * FROM products WHERE category = 'electronics';
 
-- Good: fetches only what you need
SELECT id, name, price FROM products WHERE category = 'electronics';

sql

-- Slow: correlated subquery runs once per row
SELECT
  o.*,
  (SELECT COUNT(*) FROM order_items i WHERE i.order_id = o.id) AS item_count,
  (SELECT SUM(amount) FROM payments p WHERE p.order_id = o.id) AS total_paid
FROM orders o
WHERE o.status = 'completed';
 
-- Fast: window functions compute in a single pass
SELECT
  o.*,
  COUNT(i.id) OVER (PARTITION BY o.id) AS item_count,
  SUM(p.amount) OVER (PARTITION BY o.id) AS total_paid
FROM orders o
LEFT JOIN order_items i ON i.order_id = o.id
LEFT JOIN payments p ON p.order_id = o.id
WHERE o.status = 'completed';

sql

-- Slow: correlated subquery
SELECT * FROM notifications n
WHERE n.created_at = (
  SELECT MAX(created_at) FROM notifications
  WHERE user_id = n.user_id
);
 
-- Fast: DISTINCT ON (PostgreSQL-specific, extremely useful)
SELECT DISTINCT ON (user_id) *
FROM notifications
ORDER BY user_id, created_at DESC;
 
-- Also fast: ROW_NUMBER window function
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM notifications
) sub
WHERE rn = 1;

sql

-- Page 1: fast
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 0;
 
-- Page 500: slow — PostgreSQL must scan and discard 9,980 rows
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 9980;
 
-- Page 5000: very slow — scans 99,980 rows to return 20
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 99980;

sql

-- Analyze a single table
ANALYZE orders;
 
-- Analyze the entire database
ANALYZE;
 
-- See current statistics for a column
SELECT
  tablename, attname, n_distinct, most_common_vals, correlation
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';

sql

-- Check autovacuum status
SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  last_vacuum,
  last_autovacuum,
  last_analyze,
  last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

sql

-- More aggressive autovacuum for high-write tables
ALTER TABLE sessions SET (
  autovacuum_vacuum_scale_factor = 0.01,     -- vacuum at 1% dead tuples (default 20%)
  autovacuum_analyze_scale_factor = 0.005,   -- analyze at 0.5% changes (default 10%)
  autovacuum_vacuum_cost_delay = 2           -- faster vacuum (default 2ms in newer PG)
);

sql

-- Estimate table bloat (simplified version)
SELECT
  current_database(),
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
  pg_size_pretty(
    pg_total_relation_size(schemaname || '.' || tablename) -
    pg_relation_size(schemaname || '.' || tablename)
  ) AS index_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC
LIMIT 20;

ini

[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb
 
[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
 
; Pool mode
pool_mode = transaction
 
; Pool sizing
default_pool_size = 20
max_client_conn = 500
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3
 
; Timeouts
server_idle_timeout = 300
client_idle_timeout = 0
query_timeout = 30

javascript

const pool = new Pool({
  host: "127.0.0.1",
  port: 6432, // PgBouncer port, not PostgreSQL port
  database: "mydb",
  max: 20, // matches PgBouncer default_pool_size
  statement_timeout: 30000,
  // Disable prepared statements for PgBouncer transaction mode
  prepare: false,
});

sql

-- Top 10 queries by total execution time
SELECT
  queryid,
  calls,
  round(total_exec_time::numeric, 2) AS total_time_ms,
  round(mean_exec_time::numeric, 2) AS avg_time_ms,
  round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS pct,
  query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

sql

-- What indexes exist on this table?
SELECT
  indexname,
  indexdef,
  idx_scan,
  idx_tup_read,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
JOIN pg_indexes ON pg_stat_user_indexes.indexrelname = pg_indexes.indexname
WHERE tablename = 'orders'
ORDER BY idx_scan DESC;

sql

-- Create the index concurrently (doesn't lock the table)
CREATE INDEX CONCURRENTLY idx_orders_customer_status
ON orders (customer_id, status)
WHERE status IN ('pending', 'processing');
 
-- Verify the index is used
EXPLAIN (ANALYZE, BUFFERS) <the slow query>;

sql

-- work_mem: memory for sorts and hash joins (per operation, not per connection)
-- Default is 4MB, which is too low for complex queries
SET work_mem = '64MB';  -- try this and re-run EXPLAIN ANALYZE
 
-- effective_cache_size: tells planner how much disk cache to expect
-- Set to ~75% of total RAM
SHOW effective_cache_size;
 
-- random_page_cost: ratio of random I/O to sequential I/O
-- Default is 4.0, set to 1.1-1.5 for SSDs
SHOW random_page_cost;

sql

-- Reset pg_stat_statements to see fresh data after your changes
SELECT pg_stat_statements_reset();
 
-- Check back in an hour/day
SELECT
  queryid,
  calls,
  round(mean_exec_time::numeric, 2) AS avg_time_ms,
  round(max_exec_time::numeric, 2) AS max_time_ms,
  query
FROM pg_stat_statements
WHERE query LIKE '%orders%'
ORDER BY mean_exec_time DESC;

sql

-- 1. Install pg_stat_statements
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
 
-- 2. Enable slow query logging (50ms is my threshold)
ALTER SYSTEM SET log_min_duration_statement = 50;
 
-- 3. SSD-appropriate settings
ALTER SYSTEM SET random_page_cost = 1.1;
ALTER SYSTEM SET effective_io_concurrency = 200;
 
-- 4. Increase work_mem from the anemic default
ALTER SYSTEM SET work_mem = '32MB';
 
-- 5. Set effective_cache_size to 75% of RAM
-- For a 16GB server:
ALTER SYSTEM SET effective_cache_size = '12GB';
 
-- 6. Set shared_buffers to 25% of RAM
-- For a 16GB server:
ALTER SYSTEM SET shared_buffers = '4GB';
 
-- 7. Improve autovacuum responsiveness
ALTER SYSTEM SET autovacuum_naptime = '10s';
 
-- Apply changes
SELECT pg_reload_conf();
-- Note: shared_buffers requires a restart

sql

-- Tables that need attention
SELECT
  schemaname || '.' || relname AS table,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
  n_live_tup,
  n_dead_tup,
  CASE WHEN n_live_tup > 0
    THEN round(100.0 * n_dead_tup / n_live_tup, 1)
    ELSE 0
  END AS dead_pct,
  last_autovacuum,
  last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

L'état d'esprit du débugage : ne devine pas, mesure

EXPLAIN — Le plan de vol

EXPLAIN ANALYZE — La vérité

EXPLAIN (ANALYZE, BUFFERS) — Le tableau complet

Les options d'EXPLAIN que j'utilise vraiment

Lire les plans de requête comme un détective

Seq Scan — Le scan complet de table

Index Scan — La recherche ciblée

Index Only Scan — Le Graal

Bitmap Scan — Le juste milieu

Repérer les mauvaises estimations

Stratégie d'indexation : le bon index pour le bon usage

B-tree — Le cheval de bataille par défaut

Index couvrants avec INCLUDE

Index partiels — N'indexe que ce qui compte

Index GIN — Recherche full-text et JSONB

Index GiST — Types géométriques et de plages

Index Hash — Niche mais utile

Quand les index font mal

Le problème des requêtes N+1

À quoi ressemble le N+1

Détecter le N+1 dans les logs

La solution : chargement anticipé ou JOINs

JOINs vs requêtes multiples

Réécritures de requêtes courantes

Sous-requête vs JOIN vs CTE

EXISTS vs IN vs JOIN

Éviter le SELECT *

Fonctions de fenêtrage vs sous-requêtes

La pagination bien faite

Statistiques de table et vacuuming

ANALYZE : mettre à jour les statistiques

Tuning de l'autovacuum

Bloat de table

Connection pooling

Pourquoi tu as besoin d'un connection pooler

Configuration de PgBouncer

Mode transaction vs mode session

Formule de dimensionnement du pool

Checklist pratique : les étapes exactes pour chaque requête lente

Étape 1 : obtenir la requête réelle

Étape 2 : lancer EXPLAIN (ANALYZE, BUFFERS)

Étape 3 : vérifier les statistiques de la table

Étape 4 : vérifier les index existants

Étape 5 : créer ou modifier les index

Étape 6 : envisager des réécritures de requête

Étape 7 : vérifier les paramètres du serveur

Étape 8 : surveiller après la correction

Bonus : les quick wins que j'applique à chaque nouvelle base de données

Conclusion

Articles similaires

How to Compress Images Without Losing Quality: The Complete 2026 Guide

Authentification moderne en 2026 : JWT, sessions, OAuth et Passkeys