20 expert PostgreSQL Explain Analyze Jsonb answers researched from official documentation. Every answer cites authoritative sources you can verify.
Containment query: WHERE data @> '{"status": "active"}'. EXPLAIN shows: (1) Without GIN index: Seq Scan, Filter: (data @> '{"status": "active"}'), slow. (2) With GIN index: Bitmap Index Scan on data_gin_idx, fast. Verify: Index Cond: (data @> '{"status": "active"}'::jsonb) appears in plan.
VERBOSE shows output columns and casts. JSONB query: (data->>'price')::numeric > 100. EXPLAIN VERBOSE reveals: (1) Type cast cost (text → numeric), (2) Whether expression index used, (3) Output column types. Red flag: repeated casts in nested loops (multiply cost). Optimize: expression index on ((data->>'price')::numeric).
Array query: SELECT * FROM table, jsonb_array_elements(data->'items') item WHERE item->>'id' = '123'. EXPLAIN shows: Nested Loop with SubPlan (expensive). Optimization: (1) Use jsonb_path_query for simpler path, (2) Create GIN index on (data->'items'), (3) Consider materialized view for frequent array queries. Post-optimization: Index Scan replaces Seq Scan.
Aggregation query: SELECT data->>'category', COUNT(*) FROM table GROUP BY data->>'category'. EXPLAIN shows: (1) GroupAggregate vs HashAggregate (hash faster for large groups), (2) work_mem exceeded → 'temp written' in buffers, (3) Sort cost before GroupAggregate. Optimize: increase work_mem (2x groups size), add index on ((data->>'category')).
Containment query: WHERE data @> '{"status": "active"}'. EXPLAIN shows: (1) Without GIN index: Seq Scan, Filter: (data @> '{"status": "active"}'), slow. (2) With GIN index: Bitmap Index Scan on data_gin_idx, fast. Verify: Index Cond: (data @> '{"status": "active"}'::jsonb) appears in plan.
VERBOSE shows output columns and casts. JSONB query: (data->>'price')::numeric > 100. EXPLAIN VERBOSE reveals: (1) Type cast cost (text → numeric), (2) Whether expression index used, (3) Output column types. Red flag: repeated casts in nested loops (multiply cost). Optimize: expression index on ((data->>'price')::numeric).
Array query: SELECT * FROM table, jsonb_array_elements(data->'items') item WHERE item->>'id' = '123'. EXPLAIN shows: Nested Loop with SubPlan (expensive). Optimization: (1) Use jsonb_path_query for simpler path, (2) Create GIN index on (data->'items'), (3) Consider materialized view for frequent array queries. Post-optimization: Index Scan replaces Seq Scan.
Aggregation query: SELECT data->>'category', COUNT(*) FROM table GROUP BY data->>'category'. EXPLAIN shows: (1) GroupAggregate vs HashAggregate (hash faster for large groups), (2) work_mem exceeded → 'temp written' in buffers, (3) Sort cost before GroupAggregate. Optimize: increase work_mem (2x groups size), add index on ((data->>'category')).
EXPLAIN ANALYZE executes query and reports actual runtime statistics: execution time, rows scanned, index usage, JSONB extraction cost. Syntax: EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM table WHERE data->>'key' = 'value'. Shows: Seq Scan vs Index Scan, actual rows vs estimated, buffer hits/misses, JSONB operator costs.
Slow JSONB indicators: (1) High Planning Time (>50ms) for complex JSONB paths, (2) 'SubPlan' nodes for JSONB array operations (jsonb_array_elements), (3) Rows Removed by Filter >90%, (4) Buffers: temp written (disk spill for large JSONB aggregations). Optimize: simplify JSONB paths, add expression indexes, increase work_mem for aggregations.
Planning vs Execution: (1) High planning (>100ms): complex query, many joins, consider prepared statements, (2) High execution (>1s): missing index, poor selectivity, increase work_mem. JSONB typical: planning 5-50ms, execution 10-500ms. Red flag: planning >50% total time for simple JSONB query → outdated statistics, run ANALYZE table.
EXPLAIN ANALYZE executes query and reports actual runtime statistics: execution time, rows scanned, index usage, JSONB extraction cost. Syntax: EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM table WHERE data->>'key' = 'value'. Shows: Seq Scan vs Index Scan, actual rows vs estimated, buffer hits/misses, JSONB operator costs.
Slow JSONB indicators: (1) High Planning Time (>50ms) for complex JSONB paths, (2) 'SubPlan' nodes for JSONB array operations (jsonb_array_elements), (3) Rows Removed by Filter >90%, (4) Buffers: temp written (disk spill for large JSONB aggregations). Optimize: simplify JSONB paths, add expression indexes, increase work_mem for aggregations.
Planning vs Execution: (1) High planning (>100ms): complex query, many joins, consider prepared statements, (2) High execution (>1s): missing index, poor selectivity, increase work_mem. JSONB typical: planning 5-50ms, execution 10-500ms. Red flag: planning >50% total time for simple JSONB query → outdated statistics, run ANALYZE table.
BUFFERS shows I/O: shared hit (cache), read (disk), written (temp). JSONB query pattern: (1) High 'shared read' = index/table not cached, (2) 'temp written' = work_mem too small for JSONB aggregation, (3) Low 'shared hit' ratio (<95%) = increase shared_buffers. Example: Buffers: shared hit=1000 read=50 → 95% cache hit, good.
Cost units: arbitrary (not time). Pattern: higher cost = slower. JSONB operator costs: (data->>'key') = cheap (single key lookup), (data @> '{"key": "value"}') = moderate (containment check, uses GIN), jsonb_each() = expensive (full JSONB expansion). Compare costs: Seq Scan cost=1000 vs Index Scan cost=50 → use index.
BUFFERS shows I/O: shared hit (cache), read (disk), written (temp). JSONB query pattern: (1) High 'shared read' = index/table not cached, (2) 'temp written' = work_mem too small for JSONB aggregation, (3) Low 'shared hit' ratio (<95%) = increase shared_buffers. Example: Buffers: shared hit=1000 read=50 → 95% cache hit, good.
Cost units: arbitrary (not time). Pattern: higher cost = slower. JSONB operator costs: (data->>'key') = cheap (single key lookup), (data @> '{"key": "value"}') = moderate (containment check, uses GIN), jsonb_each() = expensive (full JSONB expansion). Compare costs: Seq Scan cost=1000 vs Index Scan cost=50 → use index.
Red flags in EXPLAIN output: (1) 'Seq Scan on table' with large row count, (2) Filter: (data->>'key' = 'value') removing most rows, (3) Rows Removed by Filter >> actual rows returned, (4) High execution time. Solution: CREATE INDEX USING GIN (data) or CREATE INDEX ((data->>'key')) for specific key. Re-run EXPLAIN to verify Index Scan.
Red flags in EXPLAIN output: (1) 'Seq Scan on table' with large row count, (2) Filter: (data->>'key' = 'value') removing most rows, (3) Rows Removed by Filter >> actual rows returned, (4) High execution time. Solution: CREATE INDEX USING GIN (data) or CREATE INDEX ((data->>'key')) for specific key. Re-run EXPLAIN to verify Index Scan.