explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1p2e

Settings
# exclusive inclusive rows x rows loops node
1. 0.080 5,147.573 ↓ 2.5 10 1

Limit (cost=8,338.30..8,338.32 rows=4 width=37) (actual time=5,147.459..5,147.573 rows=10 loops=1)

2. 0.015 5,147.493 ↓ 2.5 10 1

Unique (cost=8,338.30..8,338.32 rows=4 width=37) (actual time=5,147.458..5,147.493 rows=10 loops=1)

3. 423.606 5,147.478 ↓ 36.0 144 1

Sort (cost=8,338.30..8,338.31 rows=4 width=37) (actual time=5,147.457..5,147.478 rows=144 loops=1)

  • Sort Key: dos.uid
  • Sort Method: external merge Disk: 4584kB
4. 106.994 4,723.872 ↓ 24,899.8 99,599 1

Nested Loop (cost=2,260.09..8,338.26 rows=4 width=37) (actual time=26.739..4,723.872 rows=99,599 loops=1)

  • Join Filter: (meta.dossier_id = dos.id)
5. 129.906 4,311.184 ↓ 25,474.5 101,898 1

Hash Join (cost=2,259.80..8,336.51 rows=4 width=16) (actual time=26.731..4,311.184 rows=101,898 loops=1)

  • Hash Cond: (stip.dossier_id = meta.dossier_id)
  • Join Filter: (status_change.date_time > COALESCE((SubPlan 1), '1753-01-01 00:00:00'::timestamp without time zone))
6. 76.086 385.066 ↓ 105.7 160,261 1

Gather (cost=1,076.54..6,935.64 rows=1,516 width=16) (actual time=0.709..385.066 rows=160,261 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
7. 201.060 308.980 ↓ 84.5 53,420 3

Nested Loop (cost=76.54..5,784.04 rows=632 width=16) (actual time=0.287..308.980 rows=53,420 loops=3)

8. 47.724 107.917 ↓ 84.5 53,420 3

Hash Join (cost=76.25..5,534.55 rows=632 width=16) (actual time=0.278..107.917 rows=53,420 loops=3)

  • Hash Cond: (status_change.new_status_id = new_status_type.id)
  • Join Filter: (((new_status_type.value)::text = ANY ('{"To be received",Received,"Provisionally approved","To be replaced",Approved,Cancelled,Deferred}'::text[])) OR ((old_status_type.value)::text = ANY ('{"To be received","To be replaced",Received}'::text[])))
  • Rows Removed by Join Filter: 10153
9. 45.747 60.158 ↑ 1.2 63,574 3

Hash Left Join (cost=38.12..5,288.81 rows=79,081 width=37) (actual time=0.170..60.158 rows=63,574 loops=3)

  • Hash Cond: (status_change.old_status_id = old_status_type.id)
10. 14.398 14.398 ↑ 1.2 63,574 3

Parallel Seq Scan on stipulation_status_change status_change (cost=0.00..5,042.81 rows=79,081 width=32) (actual time=0.142..14.398 rows=63,574 loops=3)

11. 0.007 0.013 ↑ 73.5 17 3

Hash (cost=22.50..22.50 rows=1,250 width=21) (actual time=0.013..0.013 rows=17 loops=3)

  • Buckets: 2048 Batches: 1 Memory Usage: 17kB
12. 0.006 0.006 ↑ 73.5 17 3

Seq Scan on stipulation_status_type old_status_type (cost=0.00..22.50 rows=1,250 width=21) (actual time=0.002..0.006 rows=17 loops=3)

13. 0.009 0.035 ↑ 73.5 17 3

Hash (cost=22.50..22.50 rows=1,250 width=21) (actual time=0.035..0.035 rows=17 loops=3)

  • Buckets: 2048 Batches: 1 Memory Usage: 17kB
14. 0.026 0.026 ↑ 73.5 17 3

Seq Scan on stipulation_status_type new_status_type (cost=0.00..22.50 rows=1,250 width=21) (actual time=0.022..0.026 rows=17 loops=3)

15. 0.003 0.003 ↑ 1.0 1 160,261

Index Scan using pk_stipulation on stipulation stip (cost=0.29..0.39 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=160,261)

  • Index Cond: (id = status_change.stipulation_id)
16. 5.295 25.986 ↓ 90.7 22,485 1

Hash (cost=1,180.16..1,180.16 rows=248 width=16) (actual time=25.986..25.986 rows=22,485 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1310kB
17. 11.925 20.691 ↓ 90.7 22,485 1

Hash Join (cost=8.17..1,180.16 rows=248 width=16) (actual time=0.155..20.691 rows=22,485 loops=1)

  • Hash Cond: (meta.handling_id = handling.id)
18. 8.752 8.752 ↓ 1.0 34,719 1

Seq Scan on metadata meta (cost=0.00..1,078.11 rows=34,711 width=24) (actual time=0.131..8.752 rows=34,719 loops=1)

19. 0.004 0.014 ↑ 1.0 1 1

Hash (cost=8.16..8.16 rows=1 width=8) (actual time=0.014..0.014 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 0.010 0.010 ↑ 1.0 1 1

Index Scan using ix_dossier_handling_type_value on dossier_handling_type handling (cost=0.14..8.16 rows=1 width=8) (actual time=0.009..0.010 rows=1 loops=1)

  • Index Cond: ((value)::text = 'Manual'::text)
21.          

SubPlan (forHash Join)

22. 203.796 3,770.226 ↓ 0.0 0 101,898

Limit (cost=19.25..19.25 rows=1 width=8) (actual time=0.037..0.037 rows=0 loops=101,898)

23. 203.796 3,566.430 ↓ 0.0 0 101,898

Sort (cost=19.25..19.25 rows=1 width=8) (actual time=0.035..0.035 rows=0 loops=101,898)

  • Sort Key: evt.date_time DESC
  • Sort Method: quicksort Memory: 25kB
24. 3,362.634 3,362.634 ↓ 0.0 0 101,898

Index Scan using ix_dossier_event_metadata_id on dossier_event evt (cost=0.42..19.24 rows=1 width=8) (actual time=0.033..0.033 rows=0 loops=101,898)

  • Index Cond: (metadata_id = meta.id)
  • Filter: ((name)::text = 'aa'::text)
  • Rows Removed by Filter: 62
25. 305.694 305.694 ↑ 1.0 1 101,898

Index Scan using pk_dossier on dossier dos (cost=0.29..0.42 rows=1 width=45) (actual time=0.003..0.003 rows=1 loops=101,898)

  • Index Cond: (id = stip.dossier_id)
  • Filter: (lock_token IS NULL)
  • Rows Removed by Filter: 0
Planning time : 5.438 ms
Execution time : 5,148.811 ms