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. 26.479 206.134 ↓ 1,363.0 1,363 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_arra
  • Rows Removed by Filter: 3431
5. 9.142 179.651 ↓ 399.5 4,794 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. 71.372 170.505 ↓ 369.0 1,476 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
  • Rows Removed by Filter: 8280
7. 29.788 99.127 ↓ 21.2 9,756 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)

  • -> 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=1504
8. 4.298 69.339 ↓ 19.5 3,009 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)
  • Index Cond: (traffic_lights_faultabcprontosnapshots.id = faultabcprontosnapshots_id)
9. 51.534 64.985 ↓ 18.2 3,140 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. 13.451 13.451 ↓ 1.0 124,602 1

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

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

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. 0.006 0.006 ↑ 1.0 1 48,780

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)
14. 0.004 0.004 ↑ 2.3 3 7,378

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)
15. 0.004 0.004 ↑ 1.0 1 23,972

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)
16. 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
17. 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)

18. 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
19. 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)
20. 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)
21. 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)

22. 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
23. 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
24. 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)

25. 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)
26. 109.282 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)

  • -> 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 lo
27. 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
  • Index Cond: ((pronto_id = u0_1.pronto_id) AND (date >= '2019-09-10'::date) AND (date <= '2020-05-18'::date))
28. 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
29. 39.525 116.899 ↓ 4.6 526 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
30. 13.522 77.367 ↑ 2.3 4,502 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)

  • -> 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
31. 63.845 63.845 ↑ 2.2 1,550 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
  • -> Bitmap Index Scan on traffic_lights_faultabcprontosnapshots_date_4225a469_uniq (cost=0.00..2296.45 rows=124270 width=0) (actual time=11.583..11.583 rows=124602 loops
  • Index Cond: (date >= '2019-09-10'::date)
  • Index Cond: (u0_1.id = faultabcprontosnapshots_id)
32. 0.007 0.007 ↑ 1.0 1 22,508

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)
33. 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
34. 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
35. 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)
36. 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