explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7ZZv

Settings
# exclusive inclusive rows x rows loops node
1. 5,036.571 28,080.325 ↓ 3,183.0 3,183 1

Nested Loop Semi Join (cost=114,588.30..170,326.53 rows=1 width=17) (actual time=1,269.062..28,080.325 rows=3,183 loops=1)

  • Join Filter: (traffic_lights_faultabcprontosnapshots.id = v0.id)
  • Rows Removed by Join Filter: 79361833
2. 5.310 460.256 ↓ 4,193.0 4,193 1

Nested Loop Semi Join (cost=3,611.52..59,345.46 rows=1 width=21) (actual time=18.131..460.256 rows=4,193 loops=1)

3. 0.000 46.046 ↓ 6,815.0 6,815 1

Gather (cost=3,610.23..59,319.16 rows=1 width=21) (actual time=18.024..46.046 rows=6,815 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
4. 7.305 206.134 ↓ 1,363.0 1,363 5 / 5

Nested Loop Left Join (cost=2,610.23..58,319.06 rows=1 width=21) (actual time=8.959..206.134 rows=1,363 loops=5)

  • Filter: (((traffic_lights_faultabcprontosnapshots.system_release_array && '{SBTS20A}'::character varying(255)[]) AND (traffic_lights_faultabcprontosnapshots_cfs.cf_id IS NULL)) OR (traffic_lights_cf.system_release_array && '{SBTS20A}'::character varying(255)[]))
  • Rows Removed by Filter: 3431
5. 3.244 179.651 ↓ 399.5 4,794 5 / 5

Nested Loop Left Join (cost=2,609.81..58,313.34 rows=12 width=56) (actual time=8.935..179.651 rows=4,794 loops=5)

6. 12.842 170.505 ↓ 369.0 1,476 5 / 5

Nested Loop Left Join (cost=2,609.37..58,238.96 rows=4 width=52) (actual time=8.924..170.505 rows=1,476 loops=5)

  • Filter: (((traffic_lights_faultabcprontosnapshots.state_id = ANY ('{"Correction Plan Ready",Reopened,"First Correction Complete",Investigating,"First Correction Ready For Testing",New,"Correction Plan Sent"}'::text[])) AND (t6.cf_id IS NULL)) OR ((t7.system_release_array && '{SBTS20A}'::character varying(255)[]) AND (t7.state_id = ANY ('{Testing,Correcting,"Ready for Testing"}'::text[]))))
  • Rows Removed by Filter: 8280
7. 5.719 99.127 ↓ 21.2 9,756 5 / 5

Nested Loop Left Join (cost=2,608.95..58,015.21 rows=460 width=71) (actual time=8.052..99.127 rows=9,756 loops=5)

8. 4.298 69.339 ↓ 19.5 3,009 5 / 5

Hash Join (cost=2,608.51..55,151.53 rows=154 width=67) (actual time=7.944..69.339 rows=3,009 loops=5)

  • Hash Cond: (traffic_lights_faultabcprontosnapshots.type_id = traffic_lights_type.id)
9. 62.295 64.985 ↓ 18.2 3,140 5 / 5

Parallel Bitmap Heap Scan on traffic_lights_faultabcprontosnapshots (cost=2,607.30..55,149.66 rows=173 width=71) (actual time=7.630..64.985 rows=3,140 loops=5)

  • Recheck Cond: ((date >= '2019-09-10'::date) AND (date <= '2020-05-18'::date))
  • Filter: (((date_part('dow'::text, (date)::timestamp without time zone) + '1'::double precision) = '1'::double precision) OR (date = '2019-11-07'::date))
  • Rows Removed by Filter: 21781
  • Heap Blocks: exact=18
10. 2.690 2.690 ↓ 1.0 124,602 1 / 5

Bitmap Index Scan on traffic_lights_faultabcprontosnapshots_date_4225a469_uniq (cost=0.00..2,607.13 rows=124,270 width=0) (actual time=13.451..13.451 rows=124,602 loops=1)

  • Index Cond: ((date >= '2019-09-10'::date) AND (date <= '2020-05-18'::date))
11. 0.010 0.056 ↑ 1.0 8 5 / 5

Hash (cost=1.11..1.11 rows=8 width=4) (actual time=0.056..0.056 rows=8 loops=5)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
12. 0.046 0.046 ↑ 1.0 8 5 / 5

Seq Scan on traffic_lights_type (cost=0.00..1.11 rows=8 width=4) (actual time=0.042..0.046 rows=8 loops=5)

  • Filter: (value <> 'Hardware'::text)
  • Rows Removed by Filter: 1
13. 24.069 24.069 ↑ 2.3 3 15,043 / 5

Index Scan using traffic_lights_faultabcprontosnapshots_cfs_6f48eae1 on traffic_lights_faultabcprontosnapshots_cfs t6 (cost=0.43..18.53 rows=7 width=8) (actual time=0.007..0.008 rows=3 loops=15,043)

  • Index Cond: (traffic_lights_faultabcprontosnapshots.id = faultabcprontosnapshots_id)
14. 58.536 58.536 ↑ 1.0 1 48,780 / 5

Index Scan using traffic_lights_cf_pkey on traffic_lights_cf t7 (cost=0.43..0.46 rows=1 width=47) (actual time=0.006..0.006 rows=1 loops=48,780)

  • Index Cond: (t6.cf_id = id)
15. 5.902 5.902 ↑ 2.3 3 7,378 / 5

Index Scan using traffic_lights_faultabcprontosnapshots_cfs_6f48eae1 on traffic_lights_faultabcprontosnapshots_cfs (cost=0.43..18.53 rows=7 width=8) (actual time=0.003..0.004 rows=3 loops=7,378)

  • Index Cond: (traffic_lights_faultabcprontosnapshots.id = faultabcprontosnapshots_id)
16. 19.178 19.178 ↑ 1.0 1 23,972 / 5

Index Scan using traffic_lights_cf_pkey on traffic_lights_cf (cost=0.43..0.46 rows=1 width=36) (actual time=0.004..0.004 rows=1 loops=23,972)

  • Index Cond: (traffic_lights_faultabcprontosnapshots_cfs.cf_id = id)
17. 35.468 408.900 ↑ 1.0 1 6,815

Nested Loop Left Join (cost=1.29..26.29 rows=1 width=9) (actual time=0.060..0.060 rows=1 loops=6,815)

  • Filter: (((u0.system_release_array && '{SBTS20A}'::character varying(255)[]) AND (u1.cf_id IS NULL)) OR (u2.system_release_array && '{SBTS20A}'::character varying(255)[]))
  • Rows Removed by Filter: 2
18. 13.865 320.305 ↑ 1.0 3 6,815

Nested Loop Left Join (cost=0.86..24.87 rows=3 width=44) (actual time=0.047..0.047 rows=3 loops=6,815)

19. 272.600 272.600 ↑ 1.0 1 6,815

Index Scan using traffic_lights_faultabcprontosnapshots_pronto_id_38ce75c0_idx on traffic_lights_faultabcprontosnapshots u0 (cost=0.43..16.07 rows=1 width=44) (actual time=0.040..0.040 rows=1 loops=6,815)

  • Index Cond: ((pronto_id = traffic_lights_faultabcprontosnapshots.pronto_id) AND (date >= '2019-09-10'::date))
  • Filter: last_state
  • Rows Removed by Filter: 27
20. 33.840 33.840 ↑ 2.3 3 6,768

Index Scan using traffic_lights_faultabcprontosnapshots_cfs_6f48eae1 on traffic_lights_faultabcprontosnapshots_cfs u1 (cost=0.43..8.73 rows=7 width=8) (actual time=0.005..0.005 rows=3 loops=6,768)

  • Index Cond: (u0.id = faultabcprontosnapshots_id)
21. 53.127 53.127 ↑ 1.0 1 17,709

Index Scan using traffic_lights_cf_pkey on traffic_lights_cf u2 (cost=0.43..0.46 rows=1 width=36) (actual time=0.003..0.003 rows=1 loops=17,709)

  • Index Cond: (u1.cf_id = id)
22. 15,421.854 22,583.498 ↓ 132.4 18,928 4,193

Unique (cost=110,976.78..110,977.85 rows=143 width=25) (actual time=0.298..5.386 rows=18,928 loops=4,193)

23. 6,033.343 7,161.644 ↓ 189.9 27,156 4,193

Sort (cost=110,976.78..110,977.14 rows=143 width=25) (actual time=0.298..1.708 rows=27,156 loops=4,193)

  • Sort Key: v0.fault_analysis_char, v0.date, v0.reported_date
  • Sort Method: quicksort Memory: 4695kB
24. 213.515 1,128.301 ↓ 282.7 40,430 1

Nested Loop Left Join (cost=90,835.12..110,971.66 rows=143 width=25) (actual time=129.203..1,128.301 rows=40,430 loops=1)

  • Filter: (((v0.system_release_array && '{SBTS20A}'::character varying(255)[]) AND (v1.cf_id IS NULL)) OR (v2.system_release_array && '{SBTS20A}'::character varying(255)[]))
  • Rows Removed by Filter: 144315
25. 57.954 545.296 ↓ 14.2 184,745 1

Nested Loop Left Join (cost=90,834.70..104,768.73 rows=13,020 width=60) (actual time=129.185..545.296 rows=184,745 loops=1)

26. 16.933 257.344 ↓ 8.8 38,333 1

Hash Join (cost=90,834.26..96,124.91 rows=4,348 width=56) (actual time=129.170..257.344 rows=38,333 loops=1)

  • Hash Cond: (v0.type_id = v3.id)
27. 25.924 240.383 ↓ 7.9 38,686 1

Nested Loop (cost=90,833.05..96,104.82 rows=4,891 width=60) (actual time=129.122..240.383 rows=38,686 loops=1)

28. 2.903 131.101 ↓ 5.6 2,526 1

HashAggregate (cost=90,832.62..90,837.17 rows=455 width=9) (actual time=129.072..131.101 rows=2,526 loops=1)

  • Group Key: u0_1.pronto_id
29. 11.299 128.198 ↓ 5.8 2,630 1

Gather (cost=3,300.78..90,831.49 rows=455 width=9) (actual time=15.362..128.198 rows=2,630 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
30. 8.021 116.899 ↓ 4.6 526 5 / 5

Nested Loop Left Join (cost=2,300.78..89,785.99 rows=114 width=9) (actual time=6.181..116.899 rows=526 loops=5)

  • Filter: (((u0_1.system_release_array && '{SBTS20A}'::character varying(255)[]) AND (u1_1.cf_id IS NULL)) OR (u2_1.system_release_array && '{SBTS20A}'::character varying(255)[]))
  • Rows Removed by Filter: 3976
31. 2.675 77.367 ↑ 2.3 4,502 5 / 5

Nested Loop Left Join (cost=2,300.35..84,843.65 rows=10,374 width=44) (actual time=5.154..77.367 rows=4,502 loops=5)

32. 61.528 63.845 ↑ 2.2 1,550 5 / 5

Parallel Bitmap Heap Scan on traffic_lights_faultabcprontosnapshots u0_1 (cost=2,299.92..54,376.26 rows=3,464 width=44) (actual time=5.035..63.845 rows=1,550 loops=5)

  • Recheck Cond: (date >= '2019-09-10'::date)
  • Filter: last_state
  • Rows Removed by Filter: 23371
  • Heap Blocks: exact=6723
33. 2.317 2.317 ↓ 1.0 124,602 1 / 5

Bitmap Index Scan on traffic_lights_faultabcprontosnapshots_date_4225a469_uniq (cost=0.00..2,296.45 rows=124,270 width=0) (actual time=11.583..11.583 rows=124,602 loops=1)

  • Index Cond: (date >= '2019-09-10'::date)
34. 10.847 10.847 ↑ 2.3 3 7,748 / 5

Index Scan using traffic_lights_faultabcprontosnapshots_cfs_6f48eae1 on traffic_lights_faultabcprontosnapshots_cfs u1_1 (cost=0.43..8.73 rows=7 width=8) (actual time=0.006..0.007 rows=3 loops=7,748)

  • Index Cond: (u0_1.id = faultabcprontosnapshots_id)
35. 31.511 31.511 ↑ 1.0 1 22,508 / 5

Index Scan using traffic_lights_cf_pkey on traffic_lights_cf u2_1 (cost=0.43..0.46 rows=1 width=36) (actual time=0.007..0.007 rows=1 loops=22,508)

  • Index Cond: (u1_1.cf_id = id)
36. 83.358 83.358 ↓ 5.0 15 2,526

Index Scan using traffic_lights_faultabcprontosnapshots_pronto_id_38ce75c0_idx on traffic_lights_faultabcprontosnapshots v0 (cost=0.43..11.55 rows=3 width=69) (actual time=0.017..0.033 rows=15 loops=2,526)

  • Index Cond: ((pronto_id = u0_1.pronto_id) AND (date >= '2019-09-10'::date) AND (date <= '2020-05-18'::date))
37. 0.016 0.028 ↑ 1.0 8 1

Hash (cost=1.11..1.11 rows=8 width=4) (actual time=0.028..0.028 rows=8 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
38. 0.012 0.012 ↑ 1.0 8 1

Seq Scan on traffic_lights_type v3 (cost=0.00..1.11 rows=8 width=4) (actual time=0.009..0.012 rows=8 loops=1)

  • Filter: (value <> 'Hardware'::text)
  • Rows Removed by Filter: 1
39. 229.998 229.998 ↑ 1.4 5 38,333

Index Scan using traffic_lights_faultabcprontosnapshots_cfs_6f48eae1 on traffic_lights_faultabcprontosnapshots_cfs v1 (cost=0.43..1.92 rows=7 width=8) (actual time=0.005..0.006 rows=5 loops=38,333)

  • Index Cond: (v0.id = faultabcprontosnapshots_id)
40. 369.490 369.490 ↑ 1.0 1 184,745

Index Scan using traffic_lights_cf_pkey on traffic_lights_cf v2 (cost=0.43..0.46 rows=1 width=36) (actual time=0.002..0.002 rows=1 loops=184,745)

  • Index Cond: (v1.cf_id = id)
Planning time : 5.992 ms
Execution time : 28,084.122 ms