explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4Yl

Settings
# exclusive inclusive rows x rows loops node
1. 7,378,463.518 7,378,463.518 ↓ 77.4 1,925,298 1

CTE Scan on dbt_incr_sbq (cost=24,568,869.27..24,569,366.87 rows=24,880 width=4,030) (actual time=16,043.267..7,378,463.518 rows=1,925,298 loops=1)

  • Buffers: shared hit=530866468 read=12537674 dirtied=8333, temp read=43878 written=111890
  • I/O Timings: read=5615635.836
2.          

CTE dbt_incr_sbq

3. 21,717.575 7,369,646.401 ↓ 77.4 1,925,298 1

Nested Loop Semi Join (cost=92,644.50..24,568,869.27 rows=24,880 width=666) (actual time=16,043.261..7,369,646.401 rows=1,925,298 loops=1)

  • Buffers: shared hit=530866468 read=12537674 dirtied=8333, temp read=43878 written=43876
  • I/O Timings: read=5615635.836
4. 4,412.359 7,017,650.851 ↓ 22.3 1,932,265 1

Nested Loop Left Join (cost=92,622.54..20,973,409.43 rows=86,551 width=952) (actual time=16,042.983..7,017,650.851 rows=1,932,265 loops=1)

  • Buffers: shared hit=432052841 read=12537674 dirtied=8333, temp read=43878 written=43876
  • I/O Timings: read=5615635.836
5. 3,439.424 6,779,434.427 ↓ 22.3 1,932,265 1

Nested Loop Left Join (cost=92,621.98..20,715,271.07 rows=86,551 width=947) (actual time=16,042.489..6,779,434.427 rows=1,932,265 loops=1)

  • Buffers: shared hit=422852360 read=12074464 dirtied=8333, temp read=43878 written=43876
  • I/O Timings: read=5395970.104
6. 4,697.579 6,764,401.413 ↓ 22.3 1,932,265 1

Nested Loop Left Join (cost=92,612.72..19,911,976.75 rows=86,551 width=939) (actual time=16,042.476..6,764,401.413 rows=1,932,265 loops=1)

  • Buffers: shared hit=415999751 read=12074464 dirtied=8333, temp read=43878 written=43876
  • I/O Timings: read=5395970.104
7. 4,408.014 6,585,799.984 ↓ 22.3 1,932,265 1

Nested Loop Left Join (cost=92,603.51..19,112,252.65 rows=86,551 width=927) (actual time=16,042.421..6,585,799.984 rows=1,932,265 loops=1)

  • Buffers: shared hit=409489541 read=11732062 dirtied=8243, temp read=43878 written=43876
  • I/O Timings: read=5240843.149
8. 4,670.184 6,341,791.110 ↓ 22.3 1,932,265 1

Nested Loop Semi Join (cost=92,603.08..18,153,284.52 rows=86,551 width=915) (actual time=16,042.395..6,341,791.110 rows=1,932,265 loops=1)

  • Buffers: shared hit=384680048 read=11718907 dirtied=8243, temp read=43878 written=43876
  • I/O Timings: read=5237926.056
9. 4,930.832 6,329,389.498 ↓ 17.0 1,932,857 1

Nested Loop Left Join (cost=92,602.52..17,944,425.44 rows=113,442 width=915) (actual time=16,042.374..6,329,389.498 rows=1,932,857 loops=1)

  • Buffers: shared hit=374924243 read=11718902 dirtied=8243, temp read=43878 written=43876
  • I/O Timings: read=5237922.707
10. 6,231.953 5,812,251.561 ↓ 17.0 1,932,857 1

Nested Loop Left Join (cost=92,590.99..16,633,411.24 rows=113,442 width=907) (actual time=16,034.686..5,812,251.561 rows=1,932,857 loops=1)

  • Buffers: shared hit=351667925 read=10861851 dirtied=6486, temp read=43878 written=43876
  • I/O Timings: read=4781548.603
11. 4,931.283 5,388,522.496 ↓ 17.0 1,932,857 1

Nested Loop Left Join (cost=92,580.33..15,420,285.13 rows=113,442 width=899) (actual time=16,033.038..5,388,522.496 rows=1,932,857 loops=1)

  • Buffers: shared hit=341988391 read=9642121 dirtied=6480, temp read=43878 written=43876
  • I/O Timings: read=4394109.759
12. 4,732.007 5,203,835.512 ↓ 17.0 1,932,857 1

Nested Loop Left Join (cost=92,571.59..14,426,459.39 rows=113,442 width=667) (actual time=16,033.011..5,203,835.512 rows=1,932,857 loops=1)

  • Buffers: shared hit=315021740 read=9376259 dirtied=6480, temp read=43878 written=43876
  • I/O Timings: read=4249000.043
13. 6,094.734 4,205,615.007 ↓ 17.0 1,932,857 1

Nested Loop Left Join (cost=92,571.03..13,434,335.27 rows=113,442 width=435) (actual time=16,026.411..4,205,615.007 rows=1,932,857 loops=1)

  • Buffers: shared hit=295024653 read=7648608 dirtied=82, temp read=43878 written=43876
  • I/O Timings: read=3304490.176
14. 5,760.150 3,559,744.606 ↓ 17.0 1,932,857 1

Nested Loop Left Join (cost=92,570.46..13,218,397.82 rows=113,442 width=413) (actual time=16,025.871..3,559,744.606 rows=1,932,857 loops=1)

  • Buffers: shared hit=286798236 read=6252106 dirtied=82, temp read=43878 written=43876
  • I/O Timings: read=2691147.456
15. 8,295.672 26,520.431 ↓ 17.0 1,932,857 1

Hash Right Join (cost=92,455.71..196,927.23 rows=113,442 width=197) (actual time=16,024.297..26,520.431 rows=1,932,857 loops=1)

  • Hash Cond: (lp.lead_id = l.id)
  • Buffers: shared hit=1918097 read=53113, temp read=43878 written=43876
  • I/O Timings: read=462.759
16. 2,201.092 2,201.092 ↑ 1.0 2,980,397 1

Seq Scan on lead_parameters lp (cost=0.00..92,968.50 rows=3,041,950 width=31) (actual time=0.015..2,201.092 rows=2,980,397 loops=1)

  • Buffers: shared hit=9436 read=53113
  • I/O Timings: read=462.759
17. 2,075.911 16,023.667 ↓ 17.0 1,932,857 1

Hash (cost=91,037.68..91,037.68 rows=113,442 width=174) (actual time=16,023.667..16,023.667 rows=1,932,857 loops=1)

  • Buckets: 2097152 (originally 131072) Batches: 2 (originally 1) Memory Usage: 245761kB
  • Buffers: shared hit=1908661, temp written=15971
18. 2,062.726 13,947.756 ↓ 17.0 1,932,857 1

Hash Left Join (cost=285.18..91,037.68 rows=113,442 width=174) (actual time=8.193..13,947.756 rows=1,932,857 loops=1)

  • Hash Cond: (l.appops_status_id = aps.id)
  • Buffers: shared hit=1908661
19. 1,924.249 11,884.937 ↓ 17.0 1,932,857 1

Hash Left Join (cost=283.05..89,604.35 rows=113,442 width=140) (actual time=8.085..11,884.937 rows=1,932,857 loops=1)

  • Hash Cond: (l.allocated_to = iu.id)
  • Buffers: shared hit=1908660
20. 1,917.116 9,957.505 ↓ 17.0 1,932,857 1

Hash Left Join (cost=160.47..88,049.93 rows=113,442 width=136) (actual time=4.880..9,957.505 rows=1,932,857 loops=1)

  • Hash Cond: (l.product_status_id = ps.id)
  • Buffers: shared hit=1908597
21. 1,898.714 8,038.754 ↓ 17.0 1,932,857 1

Hash Left Join (cost=105.92..86,563.67 rows=113,442 width=131) (actual time=3.227..8,038.754 rows=1,932,857 loops=1)

  • Hash Cond: (l.product_family_id = pf.id)
  • Buffers: shared hit=1908569
22. 2,547.245 6,139.966 ↓ 17.0 1,932,857 1

Hash Left Join (cost=102.03..85,131.87 rows=113,442 width=131) (actual time=3.140..6,139.966 rows=1,932,857 loops=1)

  • Hash Cond: (l.lender_offer_id = lo.id)
  • Buffers: shared hit=1908567
23. 3,589.663 3,589.663 ↓ 17.0 1,932,857 1

Index Scan using idx_apps_leads on leads l (cost=0.43..84,520.99 rows=113,442 width=101) (actual time=0.062..3,589.663 rows=1,932,857 loops=1)

  • Buffers: shared hit=1908507
24. 0.283 3.058 ↓ 1.0 451 1

Hash (cost=95.98..95.98 rows=450 width=42) (actual time=3.058..3.058 rows=451 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 33kB
  • Buffers: shared hit=57
25. 0.498 2.775 ↓ 1.0 451 1

Hash Left Join (cost=70.80..95.98 rows=450 width=42) (actual time=1.972..2.775 rows=451 loops=1)

  • Hash Cond: (lo.lender_id = len.id)
  • Buffers: shared hit=57
26. 0.325 0.325 ↓ 1.0 451 1

Seq Scan on lender_offers lo (cost=0.00..19.50 rows=450 width=18) (actual time=0.008..0.325 rows=451 loops=1)

  • Buffers: shared hit=15
27. 0.789 1.952 ↓ 1.1 1,416 1

Hash (cost=54.80..54.80 rows=1,280 width=32) (actual time=1.952..1.952 rows=1,416 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 108kB
  • Buffers: shared hit=42
28. 1.163 1.163 ↓ 1.1 1,416 1

Seq Scan on lenders len (cost=0.00..54.80 rows=1,280 width=32) (actual time=0.017..1.163 rows=1,416 loops=1)

  • Buffers: shared hit=42
29. 0.034 0.074 ↑ 1.6 54 1

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

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

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

  • Buffers: shared hit=2
31. 0.716 1.635 ↓ 1.1 1,310 1

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

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

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

  • Buffers: shared hit=28
33. 1.420 3.183 ↑ 1.0 2,648 1

Hash (cost=89.48..89.48 rows=2,648 width=12) (actual time=3.183..3.183 rows=2,648 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 155kB
  • Buffers: shared hit=63
34. 1.763 1.763 ↑ 1.0 2,648 1

Seq Scan on internal_users iu (cost=0.00..89.48 rows=2,648 width=12) (actual time=0.019..1.763 rows=2,648 loops=1)

  • Buffers: shared hit=63
35. 0.042 0.093 ↓ 1.4 68 1

Hash (cost=1.50..1.50 rows=50 width=42) (actual time=0.093..0.093 rows=68 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
  • Buffers: shared hit=1
36. 0.051 0.051 ↓ 1.4 68 1

Seq Scan on appops_status aps (cost=0.00..1.50 rows=50 width=42) (actual time=0.016..0.051 rows=68 loops=1)

  • Buffers: shared hit=1
37. 305,391.406 3,527,464.025 ↑ 1.0 1 1,932,857

Aggregate (cost=114.76..114.77 rows=1 width=216) (actual time=1.825..1.825 rows=1 loops=1,932,857)

  • Buffers: shared hit=284880139 read=6198993 dirtied=82
  • I/O Timings: read=2690684.697
38. 44,455.629 3,222,072.619 ↓ 1.0 22 1,932,857

Hash Left Join (cost=5.16..112.38 rows=21 width=579) (actual time=0.483..1.667 rows=22 loops=1,932,857)

  • Hash Cond: (ll_1.appops_status_id = aps_2.id)
  • Buffers: shared hit=284880136 read=6198993 dirtied=82
  • I/O Timings: read=2690684.697
39. 58,601.088 3,177,616.908 ↓ 1.0 22 1,932,857

Nested Loop Left Join (cost=3.03..109.99 rows=21 width=545) (actual time=0.481..1.644 rows=22 loops=1,932,857)

  • Buffers: shared hit=284880135 read=6198993 dirtied=82
  • I/O Timings: read=2690684.697
40. 66,332.516 3,034,585.490 ↓ 1.0 22 1,932,857

Nested Loop Left Join (cost=2.75..64.97 rows=21 width=541) (actual time=0.476..1.570 rows=22 loops=1,932,857)

  • Buffers: shared hit=158231848 read=6198993 dirtied=82
  • I/O Timings: read=2690684.697
41. 40,589.997 2,883,822.644 ↓ 1.0 22 1,932,857

Nested Loop (cost=2.47..28.73 rows=21 width=536) (actual time=0.470..1.492 rows=22 loops=1,932,857)

  • Buffers: shared hit=34294059 read=6198993 dirtied=82
  • I/O Timings: read=2690684.697
42. 44,455.711 44,455.711 ↑ 1.0 1 1,932,857

Index Only Scan using leads_pkey on leads l_2 (cost=0.56..2.98 rows=1 width=8) (actual time=0.022..0.023 rows=1 loops=1,932,857)

  • Index Cond: (id = l.id)
  • Heap Fetches: 1932424
  • Buffers: shared hit=10866717 read=118479 dirtied=37
  • I/O Timings: read=28731.402
43. 56,052.814 2,798,776.936 ↓ 1.0 22 1,932,857

Hash Join (cost=1.91..25.54 rows=21 width=540) (actual time=0.446..1.448 rows=22 loops=1,932,857)

  • Hash Cond: (ll_1.lead_log_type_id = llt_1.id)
  • Buffers: shared hit=23427342 read=6080514 dirtied=45
  • I/O Timings: read=2661953.295
44. 2,742,724.083 2,742,724.083 ↑ 1.2 22 1,932,857

Index Scan using idx_lead_id_lead_logs on lead_logs ll_1 (cost=0.57..23.88 rows=26 width=36) (actual time=0.444..1.419 rows=22 loops=1,932,857)

  • Index Cond: (lead_id = l.id)
  • Buffers: shared hit=23427341 read=6080514 dirtied=45
  • I/O Timings: read=2661953.295
45. 0.016 0.039 ↓ 1.6 24 1

Hash (cost=1.15..1.15 rows=15 width=520) (actual time=0.039..0.039 rows=24 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=1
46. 0.023 0.023 ↓ 1.6 24 1

Seq Scan on lead_log_types llt_1 (cost=0.00..1.15 rows=15 width=520) (actual time=0.011..0.023 rows=24 loops=1)

  • Buffers: shared hit=1
47. 84,430.330 84,430.330 ↑ 1.0 1 42,215,165

Index Scan using product_status_pkey on product_status ps_2 (cost=0.28..1.73 rows=1 width=13) (actual time=0.002..0.002 rows=1 loops=42,215,165)

  • Index Cond: (ll_1.product_status_id = id)
  • Buffers: shared hit=123937789
48. 84,430.330 84,430.330 ↑ 1.0 1 42,215,165

Index Scan using internal_users_pkey on internal_users iu_1 (cost=0.28..2.14 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=42,215,165)

  • Index Cond: (ll_1.updated_by = id)
  • Buffers: shared hit=126648287
49. 0.040 0.082 ↓ 1.4 68 1

Hash (cost=1.50..1.50 rows=50 width=42) (actual time=0.082..0.082 rows=68 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
  • Buffers: shared hit=1
50. 0.042 0.042 ↓ 1.4 68 1

Seq Scan on appops_status aps_2 (cost=0.00..1.50 rows=50 width=42) (actual time=0.007..0.042 rows=68 loops=1)

  • Buffers: shared hit=1
51. 3,865.714 639,775.667 ↑ 1.0 1 1,932,857

Limit (cost=0.56..1.88 rows=1 width=22) (actual time=0.331..0.331 rows=1 loops=1,932,857)

  • Buffers: shared hit=8226417 read=1396502
  • I/O Timings: read=613342.720
52. 635,909.953 635,909.953 ↑ 4.0 1 1,932,857

Index Scan Backward using idx_user_id_created_at_usl on utm_source_log (cost=0.56..5.84 rows=4 width=22) (actual time=0.329..0.329 rows=1 loops=1,932,857)

  • Index Cond: ((user_id = l.user_id) AND (created_at <= (min(ll_1.updated_at) FILTER (WHERE (((ps_2.status)::text ~* '07$'::text) OR ((aps_2.appops_status_code)::text = '150'::text))))))
  • Buffers: shared hit=8226417 read=1396502
  • I/O Timings: read=613342.720
53. 3,865.714 993,488.498 ↑ 1.0 1 1,932,857

Limit (cost=0.56..8.73 rows=1 width=232) (actual time=0.513..0.514 rows=1 loops=1,932,857)

  • Buffers: shared hit=19997087 read=1727651 dirtied=6398
  • I/O Timings: read=944509.867
54. 989,622.784 989,622.784 ↑ 1.0 1 1,932,857

Index Scan using idx_ui_user_events on user_events (cost=0.56..8.73 rows=1 width=232) (actual time=0.512..0.512 rows=1 loops=1,932,857)

  • Index Cond: (user_id = l.user_id)
  • Filter: ((created_at <= (min(ll_1.updated_at) FILTER (WHERE (((ps_2.status)::text ~* '07$'::text) OR ((aps_2.appops_status_code)::text = '150'::text))))) AND (created_at = utm_source_log.created_at))
  • Rows Removed by Filter: 7
  • Buffers: shared hit=19997087 read=1727651 dirtied=6398
  • I/O Timings: read=944509.867
55. 1,932.857 179,755.701 ↓ 0.0 0 1,932,857

Limit (cost=8.74..8.74 rows=1 width=232) (actual time=0.093..0.093 rows=0 loops=1,932,857)

  • Buffers: shared hit=26966651 read=265862
  • I/O Timings: read=145109.716
56. 7,731.428 177,822.844 ↓ 0.0 0 1,932,857

Sort (cost=8.74..8.74 rows=1 width=232) (actual time=0.092..0.092 rows=0 loops=1,932,857)

  • Sort Key: user_events_1.created_at DESC
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=26966651 read=265862
  • I/O Timings: read=145109.716
57. 170,091.416 170,091.416 ↓ 0.0 0 1,932,857

Index Scan using idx_ui_user_events on user_events user_events_1 (cost=0.56..8.73 rows=1 width=232) (actual time=0.087..0.088 rows=0 loops=1,932,857)

  • Index Cond: (user_id = l.user_id)
  • Filter: ((created_at <= (min(ll_1.updated_at) FILTER (WHERE (((ps_2.status)::text ~* '07$'::text) OR ((aps_2.appops_status_code)::text = '150'::text))))) AND (created_at > utm_source_log.created_at))
  • Rows Removed by Filter: 11
  • Buffers: shared hit=26966651 read=265862
  • I/O Timings: read=145109.716
58. 9,664.285 417,497.112 ↑ 1.0 1 1,932,857

Aggregate (cost=10.66..10.67 rows=1 width=104) (actual time=0.216..0.216 rows=1 loops=1,932,857)

  • Buffers: shared hit=9679534 read=1219730 dirtied=6
  • I/O Timings: read=387438.844
59. 407,832.827 407,832.827 ↑ 4.5 4 1,932,857

Index Scan using idx_lead_id_lead_contacts on lead_contacts (cost=0.56..10.57 rows=18 width=14) (actual time=0.130..0.211 rows=4 loops=1,932,857)

  • Index Cond: (lead_id = l.id)
  • Buffers: shared hit=9679534 read=1219730 dirtied=6
  • I/O Timings: read=387438.844
60. 7,731.428 512,207.105 ↑ 1.0 1 1,932,857

Aggregate (cost=11.53..11.54 rows=1 width=8) (actual time=0.264..0.265 rows=1 loops=1,932,857)

  • Buffers: shared hit=23256318 read=857051 dirtied=1757
  • I/O Timings: read=456374.103
61. 504,475.677 504,475.677 ↑ 1.0 3 1,932,857

Index Scan using idx_user_id_customer_profiles on customer_profiles customer_profiles_2 (cost=0.56..11.52 rows=3 width=8) (actual time=0.232..0.261 rows=3 loops=1,932,857)

  • Index Cond: (user_id = l.user_id)
  • Filter: (created_on <= COALESCE((min(ll_1.updated_at) FILTER (WHERE (((ps_2.status)::text ~* '07$'::text) OR ((aps_2.appops_status_code)::text = '150'::text)))), 'infinity'::timestamp without time zone))
  • Rows Removed by Filter: 7
  • Buffers: shared hit=23256318 read=857051 dirtied=1757
  • I/O Timings: read=456374.103
62. 7,731.428 7,731.428 ↑ 9.0 1 1,932,857

Index Only Scan using idx_user_id_customer_profiles on customer_profiles customer_profiles_1 (cost=0.56..7.76 rows=9 width=4) (actual time=0.004..0.004 rows=1 loops=1,932,857)

  • Index Cond: (user_id = l.user_id)
  • Heap Fetches: 1924644
  • Buffers: shared hit=9755805 read=5
  • I/O Timings: read=3.349
63. 12,115.168 239,600.860 ↓ 0.0 0 1,932,265

Index Scan using idx_lead_id_rbl_logs on rbl_logs rbl_cc_api (cost=0.42..11.07 rows=1 width=20) (actual time=0.100..0.124 rows=0 loops=1,932,265)

  • Index Cond: (lead_id = l.id)
  • Filter: (id = (SubPlan 2))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=24809493 read=13155
  • I/O Timings: read=2917.094
64.          

SubPlan (forIndex Scan)

65. 25,878.002 227,485.692 ↑ 1.0 1 601,814

Aggregate (cost=2.97..2.98 rows=1 width=8) (actual time=0.378..0.378 rows=1 loops=601,814)

  • Buffers: shared hit=18733110 read=5098
  • I/O Timings: read=665.797
66. 201,607.690 201,607.690 ↓ 101.0 101 601,814

Index Scan using idx_lead_id_rbl_logs on rbl_logs rl2 (cost=0.42..2.96 rows=1 width=8) (actual time=0.005..0.335 rows=101 loops=601,814)

  • Index Cond: (lead_id = rbl_cc_api.lead_id)
  • Filter: (rbl_api_level_id = 1)
  • Rows Removed by Filter: 1314
  • Buffers: shared hit=18733110 read=5098
  • I/O Timings: read=665.797
67. 1,932.265 173,903.850 ↓ 0.0 0 1,932,265

Limit (cost=9.21..9.22 rows=1 width=20) (actual time=0.090..0.090 rows=0 loops=1,932,265)

  • Buffers: shared hit=6510210 read=342402 dirtied=90
  • I/O Timings: read=155126.954
68. 5,796.795 171,971.585 ↓ 0.0 0 1,932,265

Sort (cost=9.21..9.22 rows=1 width=20) (actual time=0.089..0.089 rows=0 loops=1,932,265)

  • Sort Key: sbi_logs.id DESC
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=6510210 read=342402 dirtied=90
  • I/O Timings: read=155126.954
69. 166,174.790 166,174.790 ↓ 0.0 0 1,932,265

Index Scan using idx_lead_id_sbi_logs on sbi_logs (cost=0.43..9.20 rows=1 width=20) (actual time=0.068..0.086 rows=0 loops=1,932,265)

  • Index Cond: (lead_id = l.id)
  • Filter: ((api_level)::text = 'AIS'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=6510207 read=342402 dirtied=90
  • I/O Timings: read=155126.954
70. 3,864.530 11,593.590 ↓ 0.0 0 1,932,265

Limit (cost=9.26..9.26 rows=1 width=8) (actual time=0.006..0.006 rows=0 loops=1,932,265)

  • Buffers: shared hit=6852609
71. 3,864.530 7,729.060 ↓ 0.0 0 1,932,265

Sort (cost=9.26..9.26 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=1,932,265)

  • Sort Key: sbi_logs_1.created_at
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=6852609
72. 3,864.530 3,864.530 ↓ 0.0 0 1,932,265

Index Scan using idx_lead_id_sbi_logs on sbi_logs sbi_logs_1 (cost=0.43..9.25 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=1,932,265)

  • Index Cond: (lead_id = l.id)
  • Filter: (((api_level)::text = 'ATS'::text) AND ((status_code)::text = ANY ('{118,120,170}'::text[])))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=6852609
73. 233,804.065 233,804.065 ↑ 1.0 1 1,932,265

Index Scan using idx_customer_profile_id_customer_profiles on customer_profiles cp (cost=0.56..2.98 rows=1 width=13) (actual time=0.121..0.121 rows=1 loops=1,932,265)

  • Index Cond: (customer_profile_id = (max(customer_profiles_2.customer_profile_id)))
  • Buffers: shared hit=9200481 read=463210
  • I/O Timings: read=219665.733
74. 22,592.043 291,772.015 ↑ 1.0 1 1,932,265

Nested Loop Left Join (cost=21.96..38.20 rows=1 width=12) (actual time=0.151..0.151 rows=1 loops=1,932,265)

  • Filter: (((aps_1.appops_status_code)::text = '150'::text) OR ((ps_1.status)::text ~* '07$'::text))
  • Rows Removed by Filter: 5
  • Buffers: shared hit=74766320
75. 48,319.103 247,329.920 ↑ 1.3 6 1,932,265

Hash Right Join (cost=21.68..23.44 rows=8 width=54) (actual time=0.101..0.128 rows=6 loops=1,932,265)

  • Hash Cond: (aps_1.id = ll.appops_status_id)
  • Buffers: shared hit=42415156
76. 32,836.027 32,836.027 ↑ 1.4 37 1,931,531

Seq Scan on appops_status aps_1 (cost=0.00..1.50 rows=50 width=42) (actual time=0.003..0.017 rows=37 loops=1,931,531)

  • Buffers: shared hit=1931531
77. 25,119.445 166,174.790 ↓ 2.8 22 1,932,265

Hash (cost=21.58..21.58 rows=8 width=20) (actual time=0.086..0.086 rows=22 loops=1,932,265)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=40483625
78. 42,509.800 141,055.345 ↓ 2.8 22 1,932,265

Hash Join (cost=2.47..21.58 rows=8 width=20) (actual time=0.015..0.073 rows=22 loops=1,932,265)

  • Hash Cond: (ll.lead_log_type_id = llt.id)
  • Buffers: shared hit=40483625
79. 42,509.830 98,545.515 ↓ 2.2 22 1,932,265

Nested Loop (cost=1.14..20.12 rows=10 width=24) (actual time=0.013..0.051 rows=22 loops=1,932,265)

  • Buffers: shared hit=40483624
80. 11,593.590 11,593.590 ↑ 1.0 1 1,932,265

Index Only Scan using leads_pkey on leads l_1 (cost=0.56..2.60 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=1,932,265)

  • Index Cond: (id = l.id)
  • Heap Fetches: 1931833
  • Buffers: shared hit=10981861
81. 44,442.095 44,442.095 ↑ 1.2 22 1,932,265

Index Scan using idx_lead_id_lead_logs on lead_logs ll (cost=0.57..17.26 rows=26 width=28) (actual time=0.006..0.023 rows=22 loops=1,932,265)

  • Index Cond: (lead_id = l_1.id)
  • Buffers: shared hit=29501763
82. 0.014 0.030 ↓ 1.6 24 1

Hash (cost=1.15..1.15 rows=15 width=4) (actual time=0.030..0.030 rows=24 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
83. 0.016 0.016 ↓ 1.6 24 1

Seq Scan on lead_log_types llt (cost=0.00..1.15 rows=15 width=4) (actual time=0.005..0.016 rows=24 loops=1)

  • Buffers: shared hit=1
84. 21,850.052 21,850.052 ↑ 1.0 1 10,925,026

Index Scan using product_status_pkey on product_status ps_1 (cost=0.28..1.83 rows=1 width=13) (actual time=0.002..0.002 rows=1 loops=10,925,026)

  • Index Cond: (ll.product_status_id = id)
  • Buffers: shared hit=32351164
85.          

SubPlan (forNested Loop Semi Join)

86. 15,402.384 38,505.960 ↑ 1.0 1 1,925,298

Aggregate (cost=11.52..11.53 rows=1 width=8) (actual time=0.020..0.020 rows=1 loops=1,925,298)

  • Buffers: shared hit=24047307
87. 23,103.576 23,103.576 ↑ 1.0 9 1,925,298

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.012 rows=9 loops=1,925,298)

  • Index Cond: (user_id = l.user_id)
  • Buffers: shared hit=24047307