explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rVk

Settings
# exclusive inclusive rows x rows loops node
1. 3.844 2,682.364 ↓ 1,637.0 6,548 1

Unique (cost=2,285.21..2,285.30 rows=4 width=37) (actual time=2,608.678..2,682.364 rows=6,548 loops=1)

2.          

CTE cte

3. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=8) (actual time=0.000..0.001 rows=1 loops=1)

4. 3.924 2,678.520 ↓ 10,587.2 42,349 1

Subquery Scan on a (cost=2,285.20..2,285.28 rows=4 width=37) (actual time=2,608.677..2,678.520 rows=42,349 loops=1)

5. 14.755 2,674.596 ↓ 10,587.2 42,349 1

Unique (cost=2,285.20..2,285.24 rows=4 width=53) (actual time=2,608.676..2,674.596 rows=42,349 loops=1)

6. 344.440 2,659.841 ↓ 24,899.8 99,599 1

Sort (cost=2,285.20..2,285.21 rows=4 width=53) (actual time=2,608.675..2,659.841 rows=99,599 loops=1)

  • Sort Key: dos.uid, (COALESCE(max(evt.date_time), cte.min_dat)), status_change.date_time
  • Sort Method: external merge Disk: 6456kB
7. 48.475 2,315.401 ↓ 24,899.8 99,599 1

Nested Loop (cost=104.67..2,285.16 rows=4 width=53) (actual time=0.202..2,315.401 rows=99,599 loops=1)

  • Join Filter: (status_change.date_time > (COALESCE(max(evt.date_time), cte.min_dat)))
8. 15.859 573.743 ↓ 9,054.5 99,599 1

Nested Loop (cost=85.43..2,073.15 rows=11 width=61) (actual time=0.189..573.743 rows=99,599 loops=1)

9. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on cte (cost=0.00..0.02 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1)

10. 84.115 557.882 ↓ 9,054.5 99,599 1

Nested Loop (cost=85.43..2,073.02 rows=11 width=53) (actual time=0.186..557.882 rows=99,599 loops=1)

  • Join Filter: (meta.dossier_id = dos.id)
11. 54.945 371.869 ↓ 9,263.5 101,898 1

Hash Join (cost=85.14..2,068.21 rows=11 width=32) (actual time=0.180..371.869 rows=101,898 loops=1)

  • 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: 17098
12. 28.487 316.910 ↓ 87.8 118,996 1

Hash Left Join (cost=47.01..2,026.53 rows=1,356 width=53) (actual time=0.159..316.910 rows=118,996 loops=1)

  • Hash Cond: (status_change.old_status_id = old_status_type.id)
13. 53.276 288.415 ↓ 87.8 118,996 1

Nested Loop (cost=8.89..1,984.85 rows=1,356 width=48) (actual time=0.139..288.415 rows=118,996 loops=1)

14. 29.533 88.617 ↓ 93.1 73,261 1

Nested Loop (cost=8.47..1,449.46 rows=787 width=32) (actual time=0.135..88.617 rows=73,261 loops=1)

15. 9.896 14.114 ↓ 90.7 22,485 1

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

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

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

17. 0.003 0.011 ↑ 1.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
18. 0.008 0.008 ↑ 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.008..0.008 rows=1 loops=1)

  • Index Cond: ((value)::text = 'Manual'::text)
19. 44.970 44.970 ↑ 4.0 3 22,485

Index Scan using ix_stipulation_dossier_id on stipulation stip (cost=0.29..0.97 rows=12 width=16) (actual time=0.001..0.002 rows=3 loops=22,485)

  • Index Cond: (dossier_id = meta.dossier_id)
20. 146.522 146.522 ↑ 1.5 2 73,261

Index Scan using ix_stipulation_status_change_stipulation_id on stipulation_status_change status_change (cost=0.42..0.65 rows=3 width=32) (actual time=0.002..0.002 rows=2 loops=73,261)

  • Index Cond: (stipulation_id = stip.id)
21. 0.005 0.008 ↑ 73.5 17 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 17kB
22. 0.003 0.003 ↑ 73.5 17 1

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

23. 0.008 0.014 ↑ 73.5 17 1

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

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

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

25. 101.898 101.898 ↑ 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.001..0.001 rows=1 loops=101,898)

  • Index Cond: (id = stip.dossier_id)
  • Filter: (lock_token IS NULL)
  • Rows Removed by Filter: 0
26. 0.000 1,693.183 ↑ 1.0 1 99,599

Aggregate (cost=19.24..19.25 rows=1 width=8) (actual time=0.017..0.017 rows=1 loops=99,599)

27. 1,693.183 1,693.183 ↓ 0.0 0 99,599

Index Scan using ix_dossier_event_metadata_id on dossier_event evt (cost=0.42..19.24 rows=1 width=8) (actual time=0.017..0.017 rows=0 loops=99,599)

  • Index Cond: (metadata_id = meta.id)
  • Filter: ((name)::text = 'aa'::text)
  • Rows Removed by Filter: 62
Planning time : 2.450 ms
Execution time : 2,684.082 ms