explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XqCU

Settings
# exclusive inclusive rows x rows loops node
1. 4.850 21,198.812 ↓ 1.0 2,199 1

Seq Scan on matter matterx (cost=0.00..5,803,849.53 rows=2,180 width=36) (actual time=2.246..21,198.812 rows=2,199 loops=1)

2.          

SubPlan (for Seq Scan)

3. 17.592 21,193.962 ↑ 1.0 1 2,199

Aggregate (cost=2,662.26..2,662.27 rows=1 width=32) (actual time=9.638..9.638 rows=1 loops=2,199)

4. 32.985 21,176.370 ↑ 1.5 6 2,199

WindowAgg (cost=2,661.80..2,662.05 rows=9 width=60) (actual time=9.619..9.630 rows=6 loops=2,199)

5. 19.791 21,143.385 ↑ 1.5 6 2,199

Sort (cost=2,661.80..2,661.83 rows=9 width=56) (actual time=9.614..9.615 rows=6 loops=2,199)

  • Sort Key: dialogue_state_log_1.id
  • Sort Method: quicksort Memory: 37kB
6. 18,764.873 21,123.594 ↑ 1.5 6 2,199

Nested Loop Left Join (cost=2,101.31..2,661.66 rows=9 width=56) (actual time=1.353..9.606 rows=6 loops=2,199)

  • Join Filter: (COALESCE(((dialogue_state_log_1.details)::jsonb ->> 'segmentId'::text), ((dialogue_state_log_1.details)::jsonb ->> 'destinationId'::text)) = ((((jsonb_array_elements_text(((guided_navigation_log.config)::jsonb -> 'segments'::text))))::jsonb) ->> 'id'::text))
  • Rows Removed by Join Filter: 892
7. 314.568 2,218.791 ↑ 1.5 6 2,199

Nested Loop (cost=2,099.64..2,507.50 rows=9 width=635) (actual time=0.378..1.009 rows=6 loops=2,199)

  • Join Filter: ((dialogue_state.id = dialogue_state_log_1.dialogue_state_id) AND ((dialogue_state_log_1.id = (SubPlan 1)) OR (((dialogue_state_log_1.details)::jsonb ->> 'destinationId'::text) IS NOT NULL)))
  • Rows Removed by Join Filter: 3
8. 100.260 1,084.107 ↑ 1.0 1 2,199

Nested Loop (cost=2,099.35..2,403.90 rows=1 width=12) (actual time=0.343..0.493 rows=1 loops=2,199)

  • Join Filter: (dialogue_state.id = dialogue_state_log_2.dialogue_state_id)
  • Rows Removed by Join Filter: 901
9. 703.680 703.680 ↑ 1.0 1 2,199

Seq Scan on dialogue_state (cost=0.00..242.87 rows=1 width=8) (actual time=0.167..0.320 rows=1 loops=2,199)

  • Filter: ((module = 'matter'::text) AND (module_id = matterx.id))
  • Rows Removed by Filter: 2,472
10. 263.032 280.167 ↑ 1.9 999 1,987

HashAggregate (cost=2,099.35..2,118.33 rows=1,898 width=8) (actual time=0.013..0.141 rows=999 loops=1,987)

  • Group Key: dialogue_state_log_2.dialogue_state_id
11. 17.135 17.135 ↑ 1.0 21,689 1

Seq Scan on dialogue_state_log dialogue_state_log_2 (cost=0.00..2,045.08 rows=21,708 width=4) (actual time=0.004..17.135 rows=21,689 loops=1)

12. 416.670 416.670 ↑ 1.0 11 1,938

Index Scan using ""dialogue_state_log$auto_1_idx"" on dialogue_state_log dialogue_state_log_1 (cost=0.29..3.43 rows=11 width=635) (actual time=0.025..0.215 rows=11 loops=1,938)

  • Index Cond: (dialogue_state_id = dialogue_state_log_2.dialogue_state_id)
  • Filter: ((((details)::jsonb ->> 'sourceId'::text) IS NULL) OR (((details)::jsonb ->> 'destinationId'::text) IS NOT NULL))
  • Rows Removed by Filter: 0
13.          

SubPlan (for Nested Loop)

14. 106.170 403.446 ↑ 1.0 1 21,234

Aggregate (cost=9.07..9.08 rows=1 width=4) (actual time=0.019..0.019 rows=1 loops=21,234)

15. 297.276 297.276 ↓ 6.5 71 21,234

Index Scan using ""dialogue_state_log$auto_1_idx"" on dialogue_state_log (cost=0.29..9.05 rows=11 width=4) (actual time=0.003..0.014 rows=71 loops=21,234)

  • Index Cond: (dialogue_state_id = dialogue_state.id)
16. 103.406 139.930 ↓ 4.7 141 13,993

Materialize (cost=1.68..145.46 rows=30 width=48) (actual time=0.000..0.010 rows=141 loops=13,993)

17. 0.099 36.524 ↓ 4.7 141 1

Hash Join (cost=1.68..145.31 rows=30 width=48) (actual time=0.171..36.524 rows=141 loops=1)

  • Hash Cond: (guided_navigation_log.guided_navigation_id = guided_navigation.id)
18. 12.921 36.396 ↑ 21.3 141 1

Result (cost=0.00..105.75 rows=3,000 width=36) (actual time=0.127..36.396 rows=141 loops=1)

19. 23.320 23.475 ↑ 21.3 141 1

ProjectSet (cost=0.00..30.75 rows=3,000 width=36) (actual time=0.094..23.475 rows=141 loops=1)

20. 0.155 0.155 ↓ 1.1 32 1

Seq Scan on guided_navigation_log (cost=0.00..15.30 rows=30 width=689) (actual time=0.014..0.155 rows=32 loops=1)

21. 0.013 0.029 ↓ 1.1 32 1

Hash (cost=1.30..1.30 rows=30 width=20) (actual time=0.029..0.029 rows=32 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
22. 0.016 0.016 ↓ 1.1 32 1

Seq Scan on guided_navigation (cost=0.00..1.30 rows=30 width=20) (actual time=0.009..0.016 rows=32 loops=1)

Planning time : 2.473 ms
Execution time : 21,199.301 ms