PostgreSQL-frågeoptimering: Från långsamt till under en millisekund

Förra månaden fick jag ett Slack-meddelande klockan 2 på natten: "Dashboarden timear ut." Jag SSH:ade in till produktionsservern, öppnade pg_stat_activity och hittade en enda fråga som skannade 14 miljoner rader för att returnera 12 resultat. Lösningen var ett partiellt index som tog 30 sekunder att skapa. Frågan gick från 4,2 sekunder till 0,3 millisekunder.

Det är grejen med PostgreSQL-prestanda. Problemen är nästan aldrig exotiska. Det handlar om missade index, dålig statistik, frågor som fungerade bra när tabellen hade 10 000 rader men nu har den 10 miljoner. Lösningarna är oftast enkla — när man väl vet var man ska leta.

Det här inlägget är allt jag lärt mig om PostgreSQL-frågeoptimering från att driva produktionsdatabaser. Ingen teori utan praktik. Varje teknik här har sparat mig verklig tid i verkliga system.

Felsökningstänket: Gissa inte, mät#

Det absolut största misstaget jag ser utvecklare göra med långsamma frågor är att gissa. "Kanske behöver vi ett index på den kolumnen." "Kanske är JOIN:en långsam." "Kanske borde vi lägga till mer RAM."

Sluta gissa. PostgreSQL har en av de bästa frågeanalysatorerna bland alla databaser. Använd den.

EXPLAIN — Ritningen#

Vanligt EXPLAIN visar dig vad PostgreSQL planerar att göra, utan att faktiskt köra frågan:

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

                                    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)

Detta visar planen — en indexskanning — och den uppskattade kostnaden. Men det är en förutsägelse. PostgreSQL kan ha helt fel om antalet rader. Jag har sett uppskattningar på "1 rad" när det faktiska resultatet var 50 000 rader. Den typen av feluppskattning kaskaderar till fruktansvärda planval.

Använd vanligt EXPLAIN när du vill ha en snabb titt på planen utan att faktiskt köra frågan. Det spelar roll när du analyserar en DELETE som skulle ändra data, eller en fråga som tar 30 minuter att köra.

EXPLAIN ANALYZE — Sanningen#

EXPLAIN ANALYZE SELECT * FROM orders WHERE 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

Nu ser du den faktiska exekveringstiden och det faktiska radantalet. Det är här den riktiga felsökningen sker. Jämför rows=1 (uppskattat) med rows=1 (faktiskt) — de matchar, så planeraren fattade ett bra beslut. När de inte matchar är det din första ledtråd.

Varning: EXPLAIN ANALYZE kör faktiskt frågan. Om du analyserar en UPDATE eller DELETE, linda den i en transaktion:

BEGIN;
EXPLAIN ANALYZE DELETE FROM sessions WHERE expires_at < now();
ROLLBACK;

EXPLAIN (ANALYZE, BUFFERS) — Hela bilden#

Det här är vad jag faktiskt använder 90% av tiden:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at > '2026-01-01'
  AND o.status = 'completed';

                                    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

BUFFERS-utdatan är kritisk. shared hit=312 innebär att 312 sidor kom från buffertcachen (RAM). read=45 innebär att 45 sidor behövde läsas från disk. Om du ser mycket read i förhållande till hit kan dina shared_buffers vara för små, eller så är tabellen för stor för att stanna i cachen.

Jag använder också FORMAT JSON när jag behöver klistra in planen på explain.dalibo.com för visualisering. Trädvyn gör komplexa planer mycket lättare att läsa:

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT ...;

EXPLAIN-alternativ jag faktiskt använder#

Här är den fullständiga diagnostikfrågan jag kör först vid varje undersökning av långsamma frågor:

EXPLAIN (ANALYZE, BUFFERS, TIMING, VERBOSE)
SELECT ...;

• TIMING — tidtagning per nod (på som standard med ANALYZE, men jag är explicit)
• VERBOSE — visar utdatakolumnlistor och schemanamnskvalificerade tabellnamn

Läsa frågeplaner som en detektiv#

Varje frågeplan är ett träd. PostgreSQL läser det nedifrån och upp: de djupast indenterade noderna körs först och matar resultat uppåt. Att förstå skanningstyper är grunden för allt annat.

Seq Scan — Full tabellskanning#

Seq Scan on orders  (cost=0.00..25000.00 rows=1000000 width=128)

Sekventiell skanning läser varenda rad i tabellen. För en tabell med en miljon rader innebär det varje sida på disk.

Men här är nyansen: Seq Scan är inte alltid dåligt. Om du väljer 30% eller mer av tabellen är en sekventiell skanning faktiskt snabbare än en indexskanning eftersom sekventiell I/O är mycket snabbare än slumpmässig I/O. PostgreSQL vet detta. Om den väljer en Seq Scan när du har ett index, kontrollera hur selektiv din WHERE-klausul faktiskt är.

När det är ett problem: när du väljer en liten bråkdel av rader från en stor tabell och det inte finns något index.

Index Scan — Den riktade uppslagningen#

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

Går till B-trädindexet, hittar matchande poster och hämtar sedan de faktiska raderna från tabellens heap. Varje radhämtning är en slumpmässig I/O-operation mot heapen. Det är utmärkt för mycket selektiva frågor men varje heap-hämtning har en kostnad.

Index Only Scan — Den heliga graalen#

Index Only Scan using idx_orders_customer_total on orders  (cost=0.43..4.15 rows=5 width=12)
  Index Cond: (customer_id = 42)
  Heap Fetches: 0

Det här är den snabbaste skanningstypen. PostgreSQL hämtar allt den behöver enbart från indexet, utan att röra tabellens heap. Du ser detta när alla kolumner du SELECT:ar och WHERE:ar på finns i indexet.

Haken: Heap Fetches: 0 innebär att synlighetskartan (visibility map) är uppdaterad. Om din tabell har många döda tupler (inte vacuumkörts nyligen) behöver PostgreSQL fortfarande kontrollera heapen för att verifiera radsynlighet. Det är en anledning till att autovacuum spelar roll för prestanda, inte bara diskutrymme.

Bitmap Scan — Mellantinget#

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')

Bitmapskanningar är PostgreSQL:s svar på problemet "för många rader för en indexskanning, för få för en sekventiell skanning". Den bygger en bitmap av vilka sidor som innehåller matchande rader, sorterar dem efter fysisk placering och hämtar dem sedan i ordning. Detta omvandlar slumpmässig I/O till sekventiell I/O.

Du ser ofta bitmapskanningar när två eller fler index kombineras:

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')

Denna BitmapAnd kombinerar två separata index. Det är PostgreSQL:s sätt att säga "jag har inget sammansatt index, men jag kan kombinera dessa två enkelkolumnsindex." Det fungerar, men ett ordentligt sammansatt index skulle vara snabbare.

Att upptäcka dåliga uppskattningar#

Det viktigaste jag letar efter i en frågeplan är felaktiga uppskattningar:

Nested Loop  (cost=0.43..50.12 rows=1 width=128)
             (actual time=0.025..4521.337 rows=48753 loops=1)

Planeraren uppskattade 1 rad. Det faktiska resultatet var 48 753 rader. Det är en feluppskattning på fem storleksordningar. Planeraren valde en Nested Loop eftersom den trodde att den joinade mot 1 rad. Med 48 753 rader hade en Hash Join varit storleksordningar snabbare.

Vanliga orsaker till dåliga uppskattningar:

• Föråldrad statistik: Kör ANALYZE på tabellen
• Korrelerade kolumner: Planeraren antar att kolumnvärden är oberoende. Om status = 'shipped' och created_at > '2026-01-01' är korrelerade (de senaste beställningarna är skickade), underskattar planeraren den kombinerade selektiviteten
• Egna funktioner i WHERE: Planeraren använder en standarduppskattning av selektivitet (vanligtvis 0,5% för likhet, 33% för intervall) när den inte kan analysera en funktion
• Parametriserade frågor med generiska planer: Efter 5 körningar kan PostgreSQL byta till en generisk plan som inte beaktar det faktiska parametervärdet

När du ser dåliga uppskattningar är lösningen vanligtvis en av: kör ANALYZE, skapa utökad statistik, skriv om frågan eller använd en CTE som ett optimeringsstängsel.

-- Create extended statistics for correlated columns
CREATE STATISTICS orders_status_date (dependencies)
ON status, created_at FROM orders;
ANALYZE orders;

Indexstrategi: Rätt index för rätt jobb#

Index är inte gratis. Varje index saktar ner skrivningar, förbrukar diskutrymme och behöver underhållas. Målet är inte "indexera allt" — det är "indexera exakt det du behöver."

B-tree — Den pålitliga arbetshästen#

B-tree är standard och hanterar den stora majoriteten av fall. Det stöder likhets- och intervalloperatorer (=, <, >, <=, >=, BETWEEN, IN, IS NULL).

CREATE INDEX idx_orders_created_at ON orders (created_at);

B-trädindexets kolumnordning spelar enormt stor roll för sammansatta index. Den vänstra kolumnen är den primära sorteringen, sedan nästa och så vidare. Ett index på (a, b, c) kan effektivt svara på:

• WHERE a = 1
• WHERE a = 1 AND b = 2
• WHERE a = 1 AND b = 2 AND c = 3
• WHERE a = 1 AND b > 5
• WHERE a = 1 ORDER BY b

Det kan inte effektivt svara på:

• WHERE b = 2 (hoppar över första kolumnen)
• WHERE c = 3 (hoppar över de två första kolumnerna)
• WHERE a = 1 AND c = 3 (lucka i mitten — a-villkoret används, c-villkoret kräver filter)

Tänk på det som en telefonkatalog sorterad efter efternamn, sedan förnamn. Du kan snabbt hitta alla "Svensson"-poster, eller specifikt "Svensson, Johan". Men du kan inte snabbt hitta alla "Johan"-poster utan att skanna hela katalogen.

Regeln: sätt likhetsvillkor först, sedan intervallvillkor, sedan sorteringskolumner.

-- 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);

Täckande index med INCLUDE#

PostgreSQL 11+ låter dig lägga till icke-nyckelkolumner till ett index med INCLUDE. Dessa kolumner lagras i lövsidorna men är inte en del av B-trädstrukturen. De möjliggör index-only-skanningar utan att blåsa upp trädet:

-- We frequently run:
-- SELECT id, total, status FROM orders WHERE customer_id = 42;
 
CREATE INDEX idx_orders_customer_covering
ON orders (customer_id) INCLUDE (total, status);

Nu kan PostgreSQL tillgodose hela frågan enbart från indexet. Utan INCLUDE skulle den behöva hämta total och status från heapen. Skillnaden på en kall cache kan vara dramatisk — jag har sett frågor gå från 50ms till 0,2ms bara genom att lägga till INCLUDE-kolumner.

Partiella index — Indexera bara det som spelar roll#

Det här är min absoluta favoritfunktion i PostgreSQL och den som flest utvecklare inte känner till.

-- Only 2% of orders are 'pending', but we query them constantly
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';

Det här indexet är litet jämfört med ett fullständigt index på created_at. Det innehåller bara rader där status = 'pending'. Frågor som matchar WHERE-klausulen använder detta lilla, snabba index. Frågor som inte matchar ignorerar det.

Verkligt exempel från produktion: Jag hade en sessions-tabell med 50 miljoner rader. Bara ungefär 200 000 var aktiva (inte utgångna). Ett fullständigt index på user_id var 1,2 GB. Ett partiellt index WHERE expires_at > now() var 8 MB. Frågan gick från 12ms till 0,1ms eftersom hela indexet fick plats i cachen.

-- 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;

Det sista exemplet är otroligt användbart. Det låter dig ha en unik e-postbegränsning på aktiva användare samtidigt som samma e-post kan förekomma i borttagna poster.

GIN-index — Fulltextsökning och JSONB#

GIN (Generalized Inverted Index) är svaret när du behöver söka inuti värden — arrayer, JSONB-dokument eller fulltextsökvektorer.

-- 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));

GIN-index är stora och långsamma att bygga, men blixtsnabba att fråga. De är värda det för läsintensiva arbetsbelastningar med komplexa inneslutningsfrågor.

För JSONB finns det även jsonb_path_ops som är mindre och snabbare för @>-inneslutningsfrågor, men som inte stöder existensoperatorer (?, ?|, ?&):

CREATE INDEX idx_products_metadata_pathops
ON products USING GIN (metadata jsonb_path_ops);

GiST-index — Geometriska typer och intervalltyper#

GiST (Generalized Search Tree) hanterar överlappande datatyper: geometriska former, intervall, fulltextsökning (alternativ till GIN, mindre men långsammare).

-- 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');

Hash-index — Nischade men användbara#

Hash-index är bara användbara för likhetsjämförelser. Sedan PostgreSQL 10 är de WAL-loggade och kraschsäkra. De är mindre än B-tree för breda kolumner och något snabbare för ren likhet:

-- 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%';

I praktiken använder jag sällan hash-index. B-tree hanterar likhet utmärkt, och oförmågan att göra intervallfrågor eller sortering gör hash-index för oflexibla för de flesta verkliga användningsfall.

När index skadar#

Varje index har en kostnad:

• Skrivförstärkning: Varje INSERT uppdaterar varje index på tabellen. En tabell med 8 index innebär 8 extra skrivningar per INSERT
• HOT-uppdateringar blockeras: Heap-Only Tuple (HOT)-uppdateringar är en viktig optimering där PostgreSQL kan uppdatera en rad utan att uppdatera index, men bara om ingen indexerad kolumn ändrades. Fler index = större chans att HOT-uppdateringar blockeras
• Vacuum-overhead: Fler index innebär att vacuum tar längre tid
• Planerarens overhead: Fler index innebär att planeraren har fler alternativ att utvärdera

Jag granskar regelbundet index på produktionstabeller:

-- 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;

Jag har hittat index på flera gigabyte som skapades under en engångsmigration och aldrig använts igen. Att ta bort dem snabbade märkbart upp skrivningar.

-- 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;

N+1-frågeproblemet#

Varje ORM-utvecklare stöter på detta till slut. Det är det vanligaste prestandaproblemet jag felsöker.

Hur N+1 ser ut#

# 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}")

Detta genererar:

-- 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;

501 frågor istället för 1 eller 2. Varje fråga är snabb individuellt, kanske 0,5ms. Men 501 av dem summerar till 250ms bara i databastid, plus nätverkslatens för tur och retur för varje enskild fråga.

Detektera N+1 i loggar#

Det snabbaste sättet att fånga N+1-frågor är att tillfälligt aktivera satshändelseloggning:

-- 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();

Titta sedan i loggarna. N+1 är omisskännligt — du ser hundratals identiska frågor med olika parametervärden i snabb följd.

En mer riktad metod för utveckling:

-- Enable auto_explain for slow queries
LOAD 'auto_explain';
SET auto_explain.log_min_duration = 100;  -- 100ms
SET auto_explain.log_nested_statements = ON;
SET auto_explain.log_analyze = ON;

Detta loggar den fullständiga EXPLAIN ANALYZE-utdatan för varje fråga över 100ms, inklusive frågor inuti funktioner.

Lösningen: Ivrig laddning eller JOIN:ar#

ORM-metoden — tala om för ORM:en att ladda relaterad data i förväg:

# 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()
)

Rå SQL-metoden — använd bara en JOIN:

SELECT o.*, c.name AS customer_name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending';

En fråga. Klart.

JOIN:ar vs flera frågor#

Det finns en giltig debatt här. Ibland är två frågor bättre än en JOIN:

-- 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);

JOIN-metoden duplicerar orderdata för varje artikel. Om varje order har 20 artiklar och orderraden är bred innebär det 20x dataöverföring. Tvåfrågemetoden skickar varje order exakt en gång.

Min regel: använd JOIN:ar för en-till-en-relationer, överväg separata frågor för en-till-många när "en"-sidan är bred. Men benchmarka alltid — nätverkslatensen för en andra fråga kostar ofta mer än den duplicerade datan.

Vanliga frågeomskrivningar#

Vissa frågor är långsamma inte på grund av saknade index utan på grund av hur de är skrivna. PostgreSQL:s planerare är bra, men den är inte magi.

Underfråga vs JOIN vs CTE#

Dessa tre angreppssätt kan producera väldigt olika planer:

-- 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;

I PostgreSQL 12+ inlineas CTE:er vanligtvis (planeraren behandlar dem som underfrågor), så prestandan är identisk. Men i PostgreSQL 11 och äldre är CTE:er optimeringsstängsel — planeraren materialiserar dem och kan inte skjuta igenom predikat. Om du fortfarande kör PG 11, undvik CTE:er för prestandakritiska frågor.

EXISTS vs IN vs JOIN#

Det här kommer upp konstant:

-- 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;

För stora tabeller vinner EXISTS ofta eftersom den kortsluter. IN-versionen måste bygga hela listan med retur-order_ids innan filtrering. JOIN-versionen kan producera dubletter, vilket kräver DISTINCT som lägger till ett sorterings- eller hashsteg.

Min standard: använd EXISTS när du kontrollerar existensen av relaterade rader. Det är det semantiskt tydligaste och vanligtvis det snabbaste.

Men det finns ett motexempel. Om underfrågans resultatmängd är liten och du behöver den för flera villkor:

-- If the subquery returns few rows, IN is perfectly fine
SELECT * FROM orders
WHERE customer_id IN (1, 2, 3, 4, 5);

Undvik SELECT *#

Det här är inte bara en kodstilspreferens. Det har verkliga prestandakonsekvenser:

-- 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';

Med SELECT *:

• Kan inte använda index-only-skanningar (alla kolumner behöver vara i indexet)
• Överför mer data över nätverket
• Använder mer minne för sortering och hashning
• Om någon lägger till en 10MB BYTEA-kolumn senare blir dina befintliga frågor tyst långsammare

Fönsterfunktioner vs underfrågor#

Fönsterfunktioner är en av PostgreSQL:s mest kraftfulla funktioner och överträffar nästan alltid korrelerade underfrågor:

-- 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';

Ytterligare ett vanligt mönster — att hämta den senaste raden per grupp:

-- 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;

DISTINCT ON är mitt förstahandsval för det här mönstret. Det är koncist, läsbart och PostgreSQL optimerar det väl med rätt index:

CREATE INDEX idx_notifications_user_latest
ON notifications (user_id, created_at DESC);

Paginering rätt gjort#

OFFSET är en fälla för stora datamängder:

-- 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;

Lösningen är nyckeluppsättningspaginering (även kallad markörbaserad paginering):

-- First page
SELECT * FROM products ORDER BY id LIMIT 20;
 
-- Next page: use the last id from the previous page
SELECT * FROM products WHERE id > 12345 ORDER BY id LIMIT 20;

Det här är alltid snabbt eftersom det använder indexet för att hoppa direkt till rätt position, oavsett vilken "sida" du befinner dig på. Kompromissen är att du inte kan hoppa till ett godtyckligt sidnummer, men för oändlig scrollning eller "nästa sida"-gränssnitt är nyckeluppsättningspaginering strikt överlägsen.

För komplexa sorteringsordningar:

-- Keyset pagination with multiple sort columns
SELECT * FROM products
WHERE (price, id) > (29.99, 12345)
ORDER BY price, id
LIMIT 20;

Det här använder radvärdesjämförelse, som PostgreSQL hanterar effektivt med ett sammansatt index på (price, id).

Tabellstatistik och vacuuming#

PostgreSQL:s frågeplanerare fattar beslut baserat på statistik om dina data. Dålig statistik leder till dåliga planer. Så enkelt är det.

ANALYZE: Uppdatera statistiken#

-- 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';

correlation-värdet är särskilt intressant. Det sträcker sig från -1 till 1 och mäter hur väl den fysiska ordningen av rader matchar den logiska ordningen av kolumnen. En korrelation nära 1 eller -1 innebär att datan är fysiskt sorterad på den kolumnen, vilket gör intervallskanningar mycket effektiva (sekventiell I/O). En korrelation nära 0 innebär slumpmässig I/O för intervallfrågor.

-- Increase statistics target for columns with skewed distributions
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;

Standardmålet för statistik är 100, vilket innebär att PostgreSQL samplar 300 * 100 = 30 000 rader. För kolumner med många distinkta värden eller skeva fördelningar ger en ökning till 500 eller 1000 planeraren bättre data till priset av något längre ANALYZE-tider.

Autovacuum-justering#

Autovacuum gör två saker: återvinner utrymme från döda tupler (borttagna eller uppdaterade rader) och uppdaterar statistik. På upptagna tabeller är standardinställningarna för autovacuum ofta för konservativa.

-- 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;

Om du ser tabeller med miljontals döda tupler och senaste vacuum var för timmar sedan, ligger din autovacuum efter.

För tabeller med hög omsättning (som sessioner, jobbköer eller metriker) sätter jag autovacuum-inställningar per tabell:

-- 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)
);

Tabelluppsvällning#

När PostgreSQL uppdaterar en rad ändrar den inte raden på plats — den skapar en ny version och markerar den gamla som död. Vacuum återvinner de döda raderna, men utrymmet återanvänds bara av den tabellen. Tabellfilen på disk krymper inte.

Med tiden kan en kraftigt uppdaterad tabell ha betydande uppsvällning — tabellen är mycket större på disk än vad levande data kräver. Det innebär fler sidor att skanna, mer I/O, mer cachepress.

-- 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;

För allvarligt uppsvällda tabeller är det nukleära alternativet VACUUM FULL — det skriver om hela tabellen. Men det tar ett exklusivt lås, så du kan inte göra det på en aktiv produktionstabell utan driftstopp. Den bättre metoden är pg_repack, som gör samma sak utan låsning:

pg_repack --table orders --no-kill-backend -d mydb

Anslutningspoolning#

Här är något som överraskar många utvecklare: PostgreSQL-anslutningar är dyra. Varje anslutning startar en ny process (inte en tråd), förbrukar ungefär 5-10 MB minne och har en icke-trivial fork-overhead.

Standard max_connections är 100. Om du har en applikationsserver med 20 workers som var och en öppnar 5 anslutningar har du redan nått gränsen. Lägg till en bakgrundsjobbehanterare, ett övervakningsverktyg och en migration som körs någonstans, och du har problem.

Varför du behöver en anslutningspooler#

Utan en pooler, om din applikation behöver hantera 500 samtidiga förfrågningar, behöver du 500 PostgreSQL-anslutningar. Det är 5 GB minne bara för anslutningsoverhead, och PostgreSQL:s prestanda försämras markant bortom ett par hundra anslutningar på grund av processhanteringsoverhead.

Med PgBouncer framför PostgreSQL mappas dessa 500 applikationsanslutningar till kanske 20 faktiska PostgreSQL-anslutningar. Poolern köar förfrågningar när alla databasanslutningar är upptagna.

PgBouncer-konfiguration#

[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

Transaktionsläge vs sessionsläge#

• Transaktionsläge (pool_mode = transaction): Anslutningar returneras till poolen efter varje transaktion. Det här är vad du vill ha 95% av tiden. Maximal anslutningsåteranvändning.
• Sessionsläge (pool_mode = session): Anslutningar hålls under hela klientsessionen. Använd detta om du behöver förberedda satser, SET-kommandon, LISTEN/NOTIFY eller andra sessionsnivåfunktioner.
• Satsläge (pool_mode = statement): Anslutningar returneras efter varje sats. För restriktivt för de flesta applikationer — du kan inte ens använda explicita transaktioner.

Haken med transaktionsläge: du kan inte använda förberedda satser (de är sessionsnivåtillstånd), du kan inte använda SET för sessionsvariabler, och LISTEN/NOTIFY fungerar inte som förväntat. De flesta ORM:er har ett sätt att inaktivera förberedda satser.

För Node.js med pg-drivrutinen:

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,
});

Formel för poolstorlek#

Det finns en vanlig formel för optimal PostgreSQL-anslutningspoolstorlek:

optimal_connections = (core_count * 2) + effective_spindle_count

För en modern server med 4 kärnor och en SSD (1 effektiv spindel):

optimal = (4 * 2) + 1 = 9

Det verkar kontraintuitivt lågt. Men PostgreSQL är CPU-bunden på de flesta modern hårdvara (SSD:er är tillräckligt snabba för att I/O sällan blir en flaskhals). Fler anslutningar än så leder till kontextväxlingsoverhead som faktiskt saktar ner saker.

I praktiken sätter jag vanligtvis default_pool_size till 2-3x detta antal för att hantera trafiktoppar, med förståelsen att vid maximal belastning kommer vissa frågor att vänta i PgBouncer-kön snarare än att alla träffar PostgreSQL samtidigt.

Praktisk checklista: De exakta stegen för varje långsam fråga#

Här är min faktiska process när jag får en rapport om att "den här frågan är långsam". Jag följer dessa steg i denna ordning, varje gång.

Steg 1: Hämta den faktiska frågan#

Inte "endpointen är långsam" — den faktiska SQL:en. Om du använder en ORM, aktivera frågeloggning:

-- Temporarily log all queries over 100ms
ALTER SYSTEM SET log_min_duration_statement = 100;
SELECT pg_reload_conf();

Eller kontrollera pg_stat_statements för toppfrågorna efter total tid:

-- 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;

pg_stat_statements är den enskilt mest värdefulla tillägget för PostgreSQL-prestanda. Om du inte kör det, installera det nu:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Steg 2: Kör EXPLAIN (ANALYZE, BUFFERS)#

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) <the slow query>;

Leta efter:

1. Dåliga raduppskattningar — faktiska rader skiljer sig mycket från uppskattade rader
2. Seq Scans på stora tabeller — potentiellt saknat index
3. Nested Loops med många rader — borde vara en Hash Join eller Merge Join
4. Höga buffertläsningar — kall cache eller för stor tabell
5. Sorteringsoperationer som spills till disk — öka work_mem eller lägg till index för sortering

Steg 3: Kontrollera tabellstatistik#

-- When were stats last updated?
SELECT
  relname,
  last_analyze,
  last_autoanalyze,
  n_live_tup,
  n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'orders';

Om last_analyze är gammalt eller n_dead_tup är högt i förhållande till n_live_tup, kör:

ANALYZE orders;

Kör sedan om EXPLAIN ANALYZE. Om planen ändras var föråldrad statistik problemet.

Steg 4: Kontrollera befintliga index#

-- 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;

Kanske finns indexet men det används inte. Kanske är kolumnordningen fel för din fråga.

Steg 5: Skapa eller ändra index#

Baserat på frågeplanen, skapa rätt index. Testa med EXPLAIN ANALYZE före och efter.

-- 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>;

Använd alltid CONCURRENTLY på produktionstabeller. Ett vanligt CREATE INDEX tar ett fullständigt tabelllås som blockerar alla skrivningar.

Steg 6: Överväg frågeomskrivningar#

Om indexet finns och statistiken är färsk men frågan fortfarande är långsam, titta på själva frågan:

• Kan en underfråga skrivas om som en JOIN?
• Orsakar OFFSET problem? Byt till nyckeluppsättningspaginering
• Väljer du fler kolumner än nödvändigt?
• Kan en korrelerad underfråga bli en fönsterfunktion?
• Hindrar en CTE planeraren från att optimera?

Steg 7: Kontrollera serverinställningar#

För specifika frågemönster spelar serverinställningar roll:

-- 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;

Om ändring av work_mem löser ett spill-till-disk-problem, överväg att öka det globalt. Men var försiktig — det är per operation, inte per anslutning. En komplex fråga med 10 sorteringsoperationer och work_mem = 256MB kan använda 2,5 GB för en enda fråga.

Steg 8: Övervaka efter åtgärden#

Fixa inte bara och glöm. Verifiera att åtgärden håller:

-- 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;

Bonus: Snabba vinster jag tillämpar på varje ny databas#

Det här är inställningar och rutiner jag tillämpar på varje PostgreSQL-databas jag sätter upp, innan några prestandaproblem uppstår:

-- 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

Och en övervakningsfråga jag kör veckovis:

-- 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;

Slutliga tankar#

PostgreSQL-frågeoptimering är ingen svart konst. Det är en systematisk process:

1. Mät — gissa inte. EXPLAIN (ANALYZE, BUFFERS) är din bästa vän.
2. Förstå planen — lär dig läsa skanningstyper, jointyper och raduppskattningar.
3. Indexera strategiskt — rätt index på rätt kolumner, i rätt ordning. Partiella index och täckande index är underutnyttjade superkrafter.
4. Skriv bättre frågor — EXISTS över IN för existenskontroller, nyckeluppsättningspaginering över OFFSET, fönsterfunktioner över korrelerade underfrågor.
5. Underhåll databasen — autovacuum-justering, statistikuppdateringar, anslutningspoolning.
6. Övervaka kontinuerligt — pg_stat_statements berättar var din databas spenderar sin tid. Kontrollera det regelbundet.

Skillnaden mellan en fråga som tar 4 sekunder och en som tar 0,3 millisekunder handlar sällan om hårdvara. Det handlar nästan alltid om kunskap — att veta var man ska leta och vad man ska ändra. Och nu vet du var du ska leta.