explain.depesz.com

PostgreSQL's explain analyze made readable

Result: L5MK

Settings
# exclusive inclusive rows x rows loops node
1. 92.077 109,101.340 ↓ 3,210.0 3,210 1

Nested Loop Anti Join (cost=223.09..2,033,327.91 rows=1 width=193) (actual time=3,059.370..109,101.340 rows=3,210 loops=1)

  • Buffers: shared hit=1146479 read=11852 dirtied=3902
  • I/O Timings: read=5969.276
2. 9.127 108,758.821 ↓ 3,544.0 3,544 1

Nested Loop Left Join (cost=222.67..2,033,301.72 rows=1 width=176) (actual time=3,058.953..108,758.821 rows=3,544 loops=1)

  • Buffers: shared hit=1099593 read=11521 dirtied=3902
  • I/O Timings: read=5803.597
3. 9.769 108,615.022 ↓ 3,544.0 3,544 1

Nested Loop Left Join (cost=222.10..2,033,300.06 rows=1 width=154) (actual time=3,058.919..108,615.022 rows=3,544 loops=1)

  • Buffers: shared hit=1082000 read=11394 dirtied=3902
  • I/O Timings: read=5730.530
4. 6.627 107,637.741 ↓ 3,544.0 3,544 1

Nested Loop (cost=221.67..2,033,298.68 rows=1 width=48) (actual time=3,058.437..107,637.741 rows=3,544 loops=1)

  • Buffers: shared hit=1067012 read=9513 dirtied=1436
  • I/O Timings: read=4819.037
5. 10.485 106,319.834 ↓ 3,544.0 3,544 1

Nested Loop (cost=221.23..2,033,297.30 rows=1 width=50) (actual time=3,057.499..106,319.834 rows=3,544 loops=1)

  • Buffers: shared hit=1055190 read=7155 dirtied=371
  • I/O Timings: read=3568.829
6. 10.434 105,654.209 ↓ 3,580.0 3,580 1

Nested Loop (cost=220.67..2,033,188.41 rows=1 width=41) (actual time=3,057.390..105,654.209 rows=3,580 loops=1)

  • Buffers: shared hit=734366 read=6950 dirtied=371
  • I/O Timings: read=3447.028
7. 17,205.217 105,006.535 ↓ 3,580.0 3,580 1

Hash Join (cost=220.23..2,033,187.02 rows=1 width=33) (actual time=3,056.847..105,006.535 rows=3,580 loops=1)

  • Hash Cond: (l.id = fs.lead_id)
  • Buffers: shared hit=717799 read=5993 dirtied=371
  • I/O Timings: read=2869.176
8. 32,598.972 84,760.664 ↓ 1.0 35,258,066 1

Hash Left Join (cost=58.44..1,901,197.38 rows=35,154,092 width=25) (actual time=1.459..84,760.664 rows=35,258,066 loops=1)

  • Hash Cond: (l.product_status_id = ps.id)
  • Buffers: shared hit=663482
9. 34,792.999 52,160.338 ↓ 1.0 35,258,066 1

Hash Left Join (cost=3.89..1,457,476.98 rows=35,154,092 width=20) (actual time=0.088..52,160.338 rows=35,258,066 loops=1)

  • Hash Cond: (l.product_family_id = pf.id)
  • Buffers: shared hit=663454
10. 17,367.274 17,367.274 ↓ 1.0 35,258,066 1

Seq Scan on leads l (cost=0.00..1,014,992.92 rows=35,154,092 width=36) (actual time=0.007..17,367.274 rows=35,258,066 loops=1)

  • Buffers: shared hit=663452
11. 0.031 0.065 ↑ 1.6 54 1

Hash (cost=2.84..2.84 rows=84 width=8) (actual time=0.065..0.065 rows=54 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
  • Buffers: shared hit=2
12. 0.034 0.034 ↑ 1.6 54 1

Seq Scan on product_families pf (cost=0.00..2.84 rows=84 width=8) (actual time=0.007..0.034 rows=54 loops=1)

  • Buffers: shared hit=2
13. 0.666 1.354 ↓ 1.1 1,310 1

Hash (cost=39.80..39.80 rows=1,180 width=13) (actual time=1.354..1.354 rows=1,310 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 76kB
  • Buffers: shared hit=28
14. 0.688 0.688 ↓ 1.1 1,310 1

Seq Scan on product_status ps (cost=0.00..39.80 rows=1,180 width=13) (actual time=0.005..0.688 rows=1,310 loops=1)

  • Buffers: shared hit=28
15. 3.004 3,040.654 ↓ 3,580.0 3,580 1

Hash (cost=161.78..161.78 rows=1 width=8) (actual time=3,040.654..3,040.654 rows=3,580 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 172kB
  • Buffers: shared hit=54314 read=5993 dirtied=371
  • I/O Timings: read=2869.176
16. 26.642 3,037.650 ↓ 3,580.0 3,580 1

Index Scan using form_submits__index_on_submitted_at on form_submits fs (cost=0.44..161.78 rows=1 width=8) (actual time=0.040..3,037.650 rows=3,580 loops=1)

  • Index Cond: ((submitted_at >= '2019-04-13 15:00:00'::timestamp without time zone) AND (submitted_at < '2019-04-13 18:00:00'::timestamp without time zone))
  • Filter: (((product)::text = ANY ('{CC,PL,BL}'::text[])) AND (rel_id = (SubPlan 4)))
  • Rows Removed by Filter: 12375
  • Buffers: shared hit=54314 read=5993 dirtied=371
  • I/O Timings: read=2869.176
17.          

SubPlan (forIndex Scan)

18. 40.326 3,011.008 ↑ 1.0 1 6,721

Aggregate (cost=7.22..7.23 rows=1 width=8) (actual time=0.448..0.448 rows=1 loops=6,721)

  • Buffers: shared hit=40158 read=5993 dirtied=173
  • I/O Timings: read=2869.176
19. 2,970.682 2,970.682 ↓ 1.2 7 6,721

Index Scan using form_submits__index_on_lead_id on form_submits (cost=0.44..7.21 rows=6 width=8) (actual time=0.137..0.442 rows=7 loops=6,721)

  • Index Cond: (lead_id = fs.lead_id)
  • Buffers: shared hit=40158 read=5993 dirtied=173
  • I/O Timings: read=2869.176
20. 637.240 637.240 ↑ 1.0 1 3,580

Index Only Scan using users_pkey on users u (cost=0.43..1.39 rows=1 width=8) (actual time=0.178..0.178 rows=1 loops=3,580)

  • Index Cond: (user_id = l.user_id)
  • Heap Fetches: 3579
  • Buffers: shared hit=16567 read=957
  • I/O Timings: read=577.852
21. 154.024 655.140 ↑ 1.0 1 3,580

Index Scan using idx_user_id_customer_profiles on customer_profiles cp (cost=0.56..108.88 rows=1 width=9) (actual time=0.083..0.183 rows=1 loops=3,580)

  • Index Cond: (user_id = l.user_id)
  • Filter: (((customer_type)::text = ANY ('{Green,Amber}'::text[])) AND (customer_profile_id = (SubPlan 3)))
  • Rows Removed by Filter: 6
  • Buffers: shared hit=320824 read=205
  • I/O Timings: read=121.801
22.          

SubPlan (forIndex Scan)

23. 136.668 501.116 ↑ 1.0 1 22,778

Aggregate (cost=11.52..11.53 rows=1 width=8) (actual time=0.022..0.022 rows=1 loops=22,778)

  • Buffers: shared hit=284731 read=118
  • I/O Timings: read=68.092
24. 364.448 364.448 ↑ 1.0 9 22,778

Index Scan using idx_user_id_customer_profiles on customer_profiles customer_profiles_2 (cost=0.56..11.50 rows=9 width=8) (actual time=0.003..0.016 rows=9 loops=22,778)

  • Index Cond: (user_id = cp.user_id)
  • Buffers: shared hit=284731 read=118
  • I/O Timings: read=68.092
25. 1,311.280 1,311.280 ↑ 1.0 1 3,544

Index Scan using user_details_pkey on user_details ud (cost=0.43..1.38 rows=1 width=18) (actual time=0.370..0.370 rows=1 loops=3,544)

  • Index Cond: (user_id = l.user_id)
  • Buffers: shared hit=11822 read=2358 dirtied=1065
  • I/O Timings: read=1250.208
26. 967.512 967.512 ↑ 1.0 1 3,544

Index Scan using form_field_inputs__index_on_lead_id on form_field_inputs ffi (cost=0.43..1.37 rows=1 width=122) (actual time=0.166..0.273 rows=1 loops=3,544)

  • Index Cond: (lead_id = l.id)
  • Buffers: shared hit=14988 read=1881 dirtied=2466
  • I/O Timings: read=911.493
27. 7.088 134.672 ↑ 1.0 1 3,544

Limit (cost=0.56..1.64 rows=1 width=22) (actual time=0.037..0.038 rows=1 loops=3,544)

  • Buffers: shared hit=17593 read=127
  • I/O Timings: read=73.067
28. 127.584 127.584 ↑ 12.0 1 3,544

Index Scan Backward using idx_user_id_created_at_usl on utm_source_log (cost=0.56..13.42 rows=12 width=22) (actual time=0.036..0.036 rows=1 loops=3,544)

  • Index Cond: (user_id = l.user_id)
  • Buffers: shared hit=17593 read=127
  • I/O Timings: read=73.067
29. 187.832 187.832 ↓ 0.0 0 3,544

Index Only Scan using applications__index_on_lead_id on applications (cost=0.43..1.65 rows=1 width=8) (actual time=0.053..0.053 rows=0 loops=3,544)

  • Index Cond: (lead_id = fs.lead_id)
  • Heap Fetches: 334
  • Buffers: shared hit=10639 read=331
  • I/O Timings: read=165.680
30.          

SubPlan (forNested Loop Anti Join)

31. 25.680 57.780 ↑ 1.0 1 3,210

Aggregate (cost=11.52..11.53 rows=1 width=8) (actual time=0.017..0.018 rows=1 loops=3,210)

  • Buffers: shared hit=32692
32. 32.100 32.100 ↑ 1.3 7 3,210

Index Scan using idx_user_id_customer_profiles on customer_profiles (cost=0.56..11.50 rows=9 width=8) (actual time=0.004..0.010 rows=7 loops=3,210)

  • Index Cond: (user_id = l.user_id)
  • Buffers: shared hit=32692
33. 2.070 4.830 ↑ 1.0 1 690

Aggregate (cost=11.52..11.53 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=690)

  • Buffers: shared hit=3555
34. 2.760 2.760 ↑ 9.0 1 690

Index Scan using idx_user_id_customer_profiles on customer_profiles customer_profiles_1 (cost=0.56..11.50 rows=9 width=8) (actual time=0.003..0.004 rows=1 loops=690)

  • Index Cond: (user_id = l.user_id)
  • Buffers: shared hit=3555
Planning time : 752.876 ms
Execution time : 109,103.763 ms