explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oZBv

Settings
# exclusive inclusive rows x rows loops node
1. 0.095 13,905.235 ↑ 1.0 100 1

Limit (cost=9.62..135.18 rows=100 width=80) (actual time=71.174..13,905.235 rows=100 loops=1)

2. 0.564 13,905.140 ↑ 10,000.0 100 1

Nested Loop Left Join (cost=9.62..1,255,546.15 rows=1,000,000 width=80) (actual time=71.173..13,905.140 rows=100 loops=1)

3. 0.371 13,895.076 ↑ 10,000.0 100 1

Nested Loop Left Join (cost=9.62..1,140,546.15 rows=1,000,000 width=1,578) (actual time=71.062..13,895.076 rows=100 loops=1)

4. 0.401 13,876.905 ↑ 10.0 100 1

Nested Loop Left Join (cost=9.38..1,120,545.90 rows=1,000 width=1,563) (actual time=70.874..13,876.905 rows=100 loops=1)

5. 18.915 13,875.504 ↑ 10.0 100 1

Nested Loop Left Join (cost=9.38..1,120,475.90 rows=1,000 width=1,563) (actual time=70.854..13,875.504 rows=100 loops=1)

  • Join Filter: ((rp_initial.agency_id = incident.agency_id) AND ((rp_initial.code)::text = (COALESCE(incident.response_priority_code_initial, get_incident_stats.first_arriver_enroute_response_priority_code, (initial_cad_code.response_priority_code)::character varying))::text))
  • Rows Removed by Join Filter: 74479
6. 18.983 13,840.989 ↑ 10.0 100 1

Nested Loop Left Join (cost=9.38..1,107,439.60 rows=1,000 width=1,569) (actual time=70.821..13,840.989 rows=100 loops=1)

  • Join Filter: ((rp_final.agency_id = incident.agency_id) AND ((rp_final.code)::text = (COALESCE(incident.response_priority_code_final, get_incident_stats.first_arriver_arrived_response_priority_code, (final_cad_code.response_priority_code)::character varying))::text))
  • Rows Removed by Join Filter: 74479
7. 0.542 13,806.706 ↑ 10.0 100 1

Nested Loop Left Join (cost=9.38..1,094,403.30 rows=1,000 width=1,572) (actual time=70.767..13,806.706 rows=100 loops=1)

8. 0.721 13,766.464 ↑ 10.0 100 1

Nested Loop Left Join (cost=9.12..1,094,383.05 rows=1,000 width=1,194) (actual time=70.423..13,766.464 rows=100 loops=1)

9. 3,849.458 13,751.143 ↓ 100.0 100 1

Nested Loop Left Join (cost=8.88..1,094,362.80 rows=1 width=1,178) (actual time=70.257..13,751.143 rows=100 loops=1)

  • Join Filter: ((final_cad_code.agency_id = agency.id) AND ((final_cad_code.code)::text = (incident.cad_code_final)::text))
  • Rows Removed by Join Filter: 13112803
10. 3,855.742 6,899.185 ↓ 100.0 100 1

Nested Loop Left Join (cost=8.88..1,088,444.75 rows=1 width=1,185) (actual time=42.066..6,899.185 rows=100 loops=1)

  • Join Filter: ((initial_cad_code.agency_id = agency.id) AND ((initial_cad_code.code)::text = (incident.cad_code_initial)::text))
  • Rows Removed by Join Filter: 13032052
11. 1.270 21.743 ↓ 100.0 100 1

Merge Join (cost=8.88..1,082,526.70 rows=1 width=1,188) (actual time=13.721..21.743 rows=100 loops=1)

  • Merge Cond: ("group".id = agency.group_id)
  • Join Filter: (incident.agency_id = agency.id)
  • Rows Removed by Join Filter: 1697
12. 0.524 19.140 ↓ 18.7 899 1

Nested Loop (cost=8.73..1,180,524.00 rows=48 width=1,184) (actual time=13.702..19.140 rows=899 loops=1)

13. 0.006 0.006 ↑ 48.0 1 1

Index Scan using groups_pkey on groups "group" (cost=0.14..156.46 rows=48 width=983) (actual time=0.005..0.006 rows=1 loops=1)

14. 1.353 18.610 ↓ 899.0 899 1

Materialize (cost=8.59..1,180,366.94 rows=1 width=201) (actual time=13.695..18.610 rows=899 loops=1)

15. 0.827 17.257 ↓ 899.0 899 1

Hash Left Join (cost=8.59..1,180,366.94 rows=1 width=201) (actual time=13.689..17.257 rows=899 loops=1)

  • Hash Cond: (incident.uuid = enrichment_data.incident_id)
  • Filter: (enrichment_data.id IS NULL)
16. 2.758 2.758 ↑ 5,096.4 899 1

Seq Scan on incidents incident (cost=0.00..1,163,176.97 rows=4,581,697 width=201) (actual time=0.007..2.758 rows=899 loops=1)

17. 0.000 13.672 ↓ 0.0 0 1

Hash (cost=8.58..8.58 rows=1 width=32) (actual time=13.672..13.672 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
18. 13.672 13.672 ↓ 0.0 0 1

Index Scan using incidents_enrichment_data_type_idx on incidents_enrichment_data enrichment_data (cost=0.56..8.58 rows=1 width=32) (actual time=13.671..13.672 rows=0 loops=1)

  • Index Cond: ((type)::text = 'performance'::text)
19. 1.333 1.333 ↓ 15.0 1,797 1

Index Scan using agencies_group_id_idx on agencies agency (cost=0.14..377.07 rows=120 width=8) (actual time=0.007..1.333 rows=1,797 loops=1)

20. 3,021.700 3,021.700 ↑ 1.0 130,322 100

Seq Scan on cad_codes initial_cad_code (cost=0.00..3,917.62 rows=133,362 width=12) (actual time=0.002..30.217 rows=130,322 loops=100)

21. 3,002.500 3,002.500 ↑ 1.0 131,129 100

Seq Scan on cad_codes final_cad_code (cost=0.00..3,917.62 rows=133,362 width=12) (actual time=0.001..30.025 rows=131,129 loops=100)

22. 14.600 14.600 ↓ 0.0 0 100

Function Scan on get_soc_definition (cost=0.25..10.25 rows=1,000 width=16) (actual time=0.146..0.146 rows=0 loops=100)

23. 39.700 39.700 ↑ 1.0 1 100

Function Scan on get_incident_stats (cost=0.25..0.26 rows=1 width=405) (actual time=0.397..0.397 rows=1 loops=100)

24. 15.086 15.300 ↓ 1.0 746 100

Materialize (cost=0.00..18.16 rows=744 width=8) (actual time=0.001..0.153 rows=746 loops=100)

25. 0.214 0.214 ↓ 1.0 753 1

Seq Scan on response_priorities rp_final (cost=0.00..14.44 rows=744 width=8) (actual time=0.007..0.214 rows=753 loops=1)

26. 15.359 15.600 ↓ 1.0 746 100

Materialize (cost=0.00..18.16 rows=744 width=8) (actual time=0.000..0.156 rows=746 loops=100)

27. 0.241 0.241 ↓ 1.0 753 1

Seq Scan on response_priorities rp_initial (cost=0.00..14.44 rows=744 width=8) (actual time=0.004..0.241 rows=753 loops=1)

28. 1.000 1.000 ↑ 1.0 1 100

Result (cost=0.00..0.05 rows=1 width=64) (actual time=0.010..0.010 rows=1 loops=100)

29. 17.800 17.800 ↑ 1,000.0 1 100

Function Scan on get_benchmarks (cost=0.25..10.25 rows=1,000 width=44) (actual time=0.177..0.178 rows=1 loops=100)

30. 9.500 9.500 ↑ 1.0 1 100

Result (cost=0.00..0.10 rows=1 width=32) (actual time=0.095..0.095 rows=1 loops=100)

Planning time : 3.836 ms
Execution time : 13,905.563 ms