# Chapter 7: The Waiting Game (Diagnostic Guide) Every time the elephant stops moving, he leaves a trail of breadcrumbs explaining *why*. To understand your workload, you must learn to read these signs. ## The Diagnostic Flowchart When your query is slow, look at the "Wait State" in \`pg_stat_activity\`: ### 1. Is the Elephant Still "Working"? (CPU/No Wait) If there is **no wait event** (or it says "CPU"), the elephant is in "The Sweat" mode. He is currently hammering away at a task in the CPU. - **Top Suspects:** [[Resources/CPU|CPU Frequency]], [[Resources/Memory|Memory sorting]], or [[Extension/_Extension|Extension code]] (complex PL/pgSQL or JIT). - **Pro Tip:** Look for [[Operations/Sort]] or [[Operations/HashJoin]] operations in your \`EXPLAIN ANALYZE\`. If \`Actual Rows\` is huge but \`Buffers\` is small, you're likely CPU-bound. ### 2. Is the Elephant Waiting for a Book? (IO Wait) If you see **IO** events, the elephant is in "The Sigh" mode, standing by the elevator to the Frozen Pantry. - **Top Suspects:** [[IO/_IO|Disk Throughput]] (The SSD/HDD is saturated) or **Memory Pressure** (The brain is too small, forcing evicted pages to disk). - **Pro Tip:** Check \`shared_buffers\` and \`effective_cache_size\`. You might need more [[Resources/Memory|Index Cache]] to avoid the elevator altogether. ### 3. Is the Elephant Bumping into Others? (Lock/LWLock Wait) If you see **Locks**, the elephant is trapped in a crowd at the Narrow Bridge. - **Top Suspects:** [[Lock/_Lock|Heavyweight Locks]] (uncommitted transactions holding \`RowExclusiveLock\`) or [[LWLock/_LWLock|LWLocks]] (contention on the \`ProcArray\` or \`WALInsertLock\`). - **Pro Tip:** Use \`pg_blocking_pids()\` to find the elephant who forgot to drop their padlock. ### 4. Is the Elephant Waiting for a Message? (IPC Wait) If you see **IPC**, the elephant is waiting for his teammates to yell back across the room. - **Top Suspects:** [[IPC/_IPC|Parallel Worker Contention\]] or **Message Queue saturation**. - **Pro Tip:** This is common during [[Operations/Gather|Parallel Scans]]. Check if \`max_parallel_workers_per_gather\` is too high for your CPU count. ### 5. Is the Elephant Just Bored? (Client/Activity Wait) If you see **Client**, the elephant is just sitting there waiting for *you* to tell him what to do next. - **Top Suspect:** [[Client/_Client|Network Latency]] or **Application Connection Pooling** (The app is doing heavy logic *between* queries). --- ## The Wait Event Library - [[Activity/_Activity|Activity]] - Sleeping, idling, or background housekeeping. - [[BufferPin/_BufferPin|BufferPin]] - Waiting for a page to be unpinned (common during Vacuum). - [[Client/_Client|Client]] - Waiting for the network or the application. - [[Extension/_Extension|Extension]] - Custom code from external modules. - [[CPU/_CPU|CPU]] - Internal processing and algorithmic waits (Sweating). - [[IO/_IO|IO]] - Fetching data from the Frozen Pantry (Disk). - [[IPC/_IPC|IPC]] - Parallel worker synchronization. - [[Lock/_Lock|Lock]] - Transactional blockades on tables or rows. - [[LWLock/_LWLock|LWLock]] - Microscopic friction in shared memory data structures. - [[Timeout/_Timeout|Timeout]] - Staff members giving up or sleeping (e.g., \`pg_sleep\`).