explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Rqw9

Settings
# exclusive inclusive rows x rows loops node
1. 39.183 831.154 ↓ 71.0 71 1

Nested Loop Left Join (cost=7,435.46..10,738.37 rows=1 width=462) (actual time=342.115..831.154 rows=71 loops=1)

  • Join Filter: (pt.status = p_status.code)
  • Rows Removed by Join Filter: 2,485
2.          

Initplan (for Nested Loop Left Join)

3. 0.000 0.000 ↓ 0.0 0

Seq Scan on property_categories (cost=0.00..1.18 rows=1 width=8) (never executed)

  • Filter: ((description)::text = 'Evidence'::text)
4. 0.000 0.000 ↓ 0.0 0

Seq Scan on property_categories property_categories_1 (cost=0.00..1.18 rows=1 width=8) (never executed)

  • Filter: ((description)::text = 'Missing Recovered'::text)
5. 0.000 0.000 ↓ 0.0 0

Seq Scan on property_categories property_categories_2 (cost=0.00..1.18 rows=1 width=8) (never executed)

  • Filter: ((description)::text = 'Missing'::text)
6. 0.000 0.000 ↓ 0.0 0

Seq Scan on property_categories property_categories_3 (cost=0.00..1.18 rows=1 width=8) (never executed)

  • Filter: ((description)::text = 'Found'::text)
7. 0.000 0.000 ↓ 0.0 0

Seq Scan on property_categories property_categories_4 (cost=0.00..1.18 rows=1 width=8) (never executed)

  • Filter: ((description)::text = 'Missing'::text)
8. 0.000 0.000 ↓ 0.0 0

Seq Scan on property_categories property_categories_5 (cost=0.00..1.18 rows=1 width=8) (never executed)

  • Filter: ((description)::text = 'Safekeeping'::text)
9. 0.000 0.000 ↓ 0.0 0

Seq Scan on property_categories property_categories_6 (cost=0.00..1.18 rows=1 width=8) (never executed)

  • Filter: ((description)::text = 'Stolen Recovered'::text)
10. 0.006 0.006 ↑ 1.0 1 1

Seq Scan on property_categories property_categories_7 (cost=0.00..1.18 rows=1 width=8) (actual time=0.005..0.006 rows=1 loops=1)

  • Filter: ((description)::text = 'Missing Recovered'::text)
  • Rows Removed by Filter: 13
11. 0.000 0.000 ↓ 0.0 0

Seq Scan on property_categories property_categories_8 (cost=0.00..1.18 rows=1 width=8) (never executed)

  • Filter: ((description)::text = 'Stolen'::text)
12. 0.000 0.000 ↓ 0.0 0

Seq Scan on property_categories property_categories_9 (cost=0.00..1.18 rows=1 width=8) (never executed)

  • Filter: ((description)::text = 'Seized'::text)
13. 0.025 0.025 ↑ 1.0 1 1

Seq Scan on property_categories property_categories_10 (cost=0.00..1.18 rows=1 width=8) (actual time=0.022..0.025 rows=1 loops=1)

  • Filter: ((description)::text = 'Evidence'::text)
  • Rows Removed by Filter: 13
14. 0.000 0.000 ↓ 0.0 0

Seq Scan on property_categories property_categories_11 (cost=0.00..1.18 rows=1 width=8) (never executed)

  • Filter: ((description)::text = 'Destroyed / Damaged / Vandalized'::text)
15. 0.000 0.000 ↓ 0.0 0

Seq Scan on property_categories property_categories_12 (cost=0.00..1.18 rows=1 width=8) (never executed)

  • Filter: ((description)::text = 'Other'::text)
16. 0.000 0.000 ↓ 0.0 0

Seq Scan on property_categories property_categories_13 (cost=0.00..1.18 rows=1 width=8) (never executed)

  • Filter: ((description)::text = 'Missing'::text)
17. 0.000 0.000 ↓ 0.0 0

Seq Scan on property_categories property_categories_14 (cost=0.00..1.18 rows=1 width=8) (never executed)

  • Filter: ((description)::text = 'Safekeeping'::text)
18. 0.000 0.000 ↓ 0.0 0

Seq Scan on property_categories property_categories_15 (cost=0.00..1.18 rows=1 width=8) (never executed)

  • Filter: ((description)::text = 'Missing'::text)
19. 0.000 0.000 ↓ 0.0 0

Seq Scan on property_categories property_categories_16 (cost=0.00..1.18 rows=1 width=8) (never executed)

  • Filter: ((description)::text = 'Stolen'::text)
20. 0.000 0.000 ↓ 0.0 0

Seq Scan on property_categories property_categories_17 (cost=0.00..1.18 rows=1 width=8) (never executed)

  • Filter: ((description)::text = 'Seized'::text)
21. 0.000 0.000 ↓ 0.0 0

Seq Scan on property_categories property_categories_18 (cost=0.00..1.18 rows=1 width=8) (never executed)

  • Filter: ((description)::text = 'Destroyed / Damaged / Vandalized'::text)
22. 0.004 0.004 ↑ 1.0 1 1

Seq Scan on property_categories property_categories_19 (cost=0.00..1.18 rows=1 width=8) (actual time=0.002..0.004 rows=1 loops=1)

  • Filter: ((description)::text = 'Evidence'::text)
  • Rows Removed by Filter: 13
23. 0.000 0.000 ↓ 0.0 0

Seq Scan on property_categories property_categories_20 (cost=0.00..1.18 rows=1 width=8) (never executed)

  • Filter: ((description)::text = 'Found'::text)
24. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on property_categories property_categories_21 (cost=0.00..1.18 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=1)

  • Filter: ((description)::text = 'Other'::text)
  • Rows Removed by Filter: 14
25. 78.271 778.656 ↓ 71.0 71 1

Nested Loop Left Join (cost=7,409.61..10,633.63 rows=1 width=462) (actual time=317.862..778.656 rows=71 loops=1)

  • Join Filter: (pe.prop_index = pt.prop_index)
  • Rows Removed by Join Filter: 379,495
26. 0.660 347.657 ↓ 71.0 71 1

Nested Loop Left Join (cost=7,409.61..9,795.42 rows=1 width=462) (actual time=298.793..347.657 rows=71 loops=1)

  • Join Filter: (pe.seize_by = seize_by.code)
  • Rows Removed by Join Filter: 6,062
27. 0.750 333.365 ↓ 71.0 71 1

Nested Loop Left Join (cost=7,409.61..9,722.47 rows=1 width=439) (actual time=298.272..333.365 rows=71 loops=1)

  • Join Filter: (pe.officer_id = off.code)
  • Rows Removed by Join Filter: 6,035
28. 0.293 319.480 ↓ 71.0 71 1

Nested Loop Left Join (cost=7,409.61..9,649.52 rows=1 width=440) (actual time=298.224..319.480 rows=71 loops=1)

  • Join Filter: (pe.drug = drug_type.code)
  • Rows Removed by Join Filter: 1,420
29. 0.327 275.664 ↓ 71.0 71 1

Nested Loop (cost=7,409.61..9,577.39 rows=1 width=438) (actual time=266.809..275.664 rows=71 loops=1)

30. 83.752 173.452 ↓ 71.0 71 1

Hash Join (cost=7,409.32..9,569.08 rows=1 width=422) (actual time=165.539..173.452 rows=71 loops=1)

  • Hash Cond: (pe.inc_index = i.inc_index)
31. 22.146 22.146 ↑ 1.0 13,363 1

Seq Scan on propertyevidence pe (cost=0.00..2,109.63 rows=13,363 width=422) (actual time=0.023..22.146 rows=13,363 loops=1)

32. 0.008 67.554 ↑ 1.0 1 1

Hash (cost=7,409.31..7,409.31 rows=1 width=16) (actual time=67.554..67.554 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
33. 67.546 67.546 ↑ 1.0 1 1

Seq Scan on incidents i (cost=0.00..7,409.31 rows=1 width=16) (actual time=67.546..67.546 rows=1 loops=1)

  • Filter: (incident_no = '19-000187'::text)
  • Rows Removed by Filter: 10,264
34. 101.885 101.885 ↑ 1.0 1 71

Index Scan using stg_combined_cases_wtrun_convertedid_idx on stg_combined_cases stg_c (cost=0.29..8.30 rows=1 width=32) (actual time=1.434..1.435 rows=1 loops=71)

  • Index Cond: ((wtrun = 1) AND (convertedid = i.incidents_zid))
35. 43.523 43.523 ↑ 1.0 20 71

Seq Scan on codes drug_type (cost=0.00..71.88 rows=20 width=8) (actual time=0.517..0.613 rows=20 loops=71)

  • Filter: (code_id = 87)
  • Rows Removed by Filter: 1,570
36. 13.135 13.135 ↑ 1.0 86 71

Seq Scan on codes off (cost=0.00..71.88 rows=86 width=8) (actual time=0.002..0.185 rows=86 loops=71)

  • Filter: (code_id = 10)
  • Rows Removed by Filter: 1,504
37. 13.632 13.632 ↑ 1.0 86 71

Seq Scan on codes seize_by (cost=0.00..71.88 rows=86 width=32) (actual time=0.002..0.192 rows=86 loops=71)

  • Filter: (code_id = 10)
  • Rows Removed by Filter: 1,504
38. 352.728 352.728 ↓ 78.6 5,346 71

Seq Scan on property_trak pt (cost=0.00..837.36 rows=68 width=16) (actual time=0.004..4.968 rows=5,346 loops=71)

  • Filter: (last = 1)
  • Rows Removed by Filter: 8,283
39. 13.277 13.277 ↑ 1.0 36 71

Seq Scan on codes p_status (cost=0.00..71.88 rows=36 width=8) (actual time=0.056..0.187 rows=36 loops=71)

  • Filter: (code_id = 63)
  • Rows Removed by Filter: 1,554
Planning time : 39.245 ms
Execution time : 1,846.108 ms