explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OCEPH

Settings
# exclusive inclusive rows x rows loops node
1. 4.864 22,905.250 ↓ 1.0 2,199 1

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

2.          

SubPlan (for Seq Scan)

3. 24.189 22,900.386 ↑ 1.0 1 2,199

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

4. 46.179 22,876.197 ↑ 1.5 6 2,199

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

5. 30.786 22,830.018 ↑ 1.5 6 2,199

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

  • Sort Key: dialogue_state_log_1.id
  • Sort Method: quicksort Memory: 37kB
6. 20,077.676 22,799.232 ↑ 1.5 6 2,199

Nested Loop Left Join (cost=2,101.31..2,661.66 rows=9 width=56) (actual time=1.503..10.368 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. 343.629 2,581.626 ↑ 1.5 6 2,199

Nested Loop (cost=2,099.64..2,507.50 rows=9 width=635) (actual time=0.438..1.174 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. 108.686 1,268.823 ↑ 1.0 1 2,199

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

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

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

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

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

  • Group Key: dialogue_state_log_2.dialogue_state_id
11. 17.207 17.207 ↑ 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.207 rows=21,689 loops=1)

12. 523.260 523.260 ↑ 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.036..0.270 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. 127.404 445.914 ↑ 1.0 1 21,234

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

15. 318.510 318.510 ↓ 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.015 rows=71 loops=21,234)

  • Index Cond: (dialogue_state_id = dialogue_state.id)
16. 109.321 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.095 30.609 ↓ 4.7 141 1

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

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

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

19. 19.546 19.688 ↑ 21.3 141 1

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

20. 0.142 0.142 ↓ 1.1 32 1

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

21. 0.010 0.023 ↓ 1.1 32 1

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

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

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

Planning time : 12.144 ms
Execution time : 22,905.810 ms