explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oKJX

Settings
# exclusive inclusive rows x rows loops node
1. 753.372 426,231.890 ↑ 1.6 59,568 1

Result (cost=23,817,779.13..24,600,137.73 rows=92,499 width=424) (actual time=424,130.930..426,231.890 rows=59,568 loops=1)

  • Buffers: shared hit=2797090 read=922807 dirtied=675, temp read=155222 written=155160
2.          

CTE all_bookings

3. 91.737 208,049.345 ↑ 1.6 59,568 1

HashAggregate (cost=16,770,097.33..16,771,022.32 rows=92,499 width=24) (actual time=208,012.325..208,049.345 rows=59,568 loops=1)

  • Group Key: a.lead_id, a.booking_date, a.applied_date
  • Buffers: shared hit=452122 read=594361 dirtied=1
4. 49.679 207,957.608 ↑ 1.6 59,568 1

Append (cost=0.00..16,769,403.59 rows=92,499 width=24) (actual time=0.770..207,957.608 rows=59,568 loops=1)

  • Buffers: shared hit=452122 read=594361 dirtied=1
5. 5,129.501 5,129.501 ↓ 8.3 58,467 1

Seq Scan on applications a (cost=0.00..131,211.56 rows=7,024 width=24) (actual time=0.769..5,129.501 rows=58,467 loops=1)

  • Filter: ((booking_date IS NOT NULL) AND ((product)::text <> 'SCC'::text) AND ((appops_status_code)::text = ANY ('{690,990}'::text[])))
  • Rows Removed by Filter: 1375859
  • Buffers: shared hit=25 read=109672
6. 140.955 202,778.428 ↑ 77.6 1,101 1

Nested Loop (cost=193.12..16,637,267.04 rows=85,475 width=24) (actual time=1,118.667..202,778.428 rows=1,101 loops=1)

  • Buffers: shared hit=452097 read=484689 dirtied=1
7. 528.073 528.073 ↓ 1.0 86,004 1

Seq Scan on applications a_1 (cost=0.00..127,625.80 rows=85,475 width=16) (actual time=0.013..528.073 rows=86,004 loops=1)

  • Filter: ((product)::text = 'SCC'::text)
  • Rows Removed by Filter: 1348322
  • Buffers: shared hit=109697
8. 202,109.400 202,109.400 ↓ 0.0 0 86,004

Aggregate (cost=193.12..193.13 rows=1 width=12) (actual time=2.350..2.350 rows=0 loops=86,004)

  • Filter: ((bool_or(((consolidated_lead_logs_backup.appops_status_code)::text = ANY ('{590,690,990}'::text[]))))::integer = 1)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=342400 read=484689 dirtied=1
  • -> Index Scan using idx_lead_id_consolidated_lead_logs on consolidated_lead_logs_backup (cost=0.57..191.06 rows=165 width=11) (actual time=0.724..2.339 rows=
  • Index Cond: (lead_id = a_1.lead_id)
  • Buffers: shared hit=342400 read=484689 dirtied=1
9. 283.715 424,168.022 ↑ 1.6 59,568 1

Sort (cost=7,046,756.81..7,046,988.06 rows=92,499 width=444) (actual time=424,130.326..424,168.022 rows=59,568 loops=1)

  • Sort Key: ab.booking_date
  • Sort Method: quicksort Memory: 59175kB
  • Buffers: shared hit=2220932 read=922807 dirtied=675, temp read=155222 written=155160
10. 249.128 423,884.307 ↑ 1.6 59,568 1

Nested Loop Left Join (cost=2,406,171.47..7,039,126.96 rows=92,499 width=444) (actual time=307,005.176..423,884.307 rows=59,568 loops=1)

  • Buffers: shared hit=2220932 read=922807 dirtied=675, temp read=155222 written=155160
11. 162.850 409,934.539 ↑ 1.6 59,568 1

Nested Loop Left Join (cost=2,406,170.91..6,762,092.46 rows=92,499 width=236) (actual time=307,003.969..409,934.539 rows=59,568 loops=1)

  • Buffers: shared hit=1958478 read=887438 dirtied=675, temp read=155222 written=155160
12. 162.744 407,746.377 ↑ 1.6 59,568 1

Hash Left Join (cost=2,406,162.74..6,003,321.11 rows=92,499 width=228) (actual time=307,003.930..407,746.377 rows=59,568 loops=1)

  • Hash Cond: (((jsonb_object_agg(COALESCE(q.slug, q.question_field_name), lua.answer)) ->> 'residentialPincode'::text) = p.pincode)
  • Buffers: shared hit=1384057 read=885737 dirtied=675, temp read=155222 written=155160
13. 160.420 407,556.070 ↑ 1.6 59,568 1

Nested Loop Left Join (cost=2,405,523.99..6,001,503.03 rows=92,499 width=200) (actual time=306,976.176..407,556.070 rows=59,568 loops=1)

  • Buffers: shared hit=1384054 read=885528 dirtied=675, temp read=155222 written=155160
14. 120.246 321,677.298 ↑ 1.6 59,568 1

Nested Loop Left Join (cost=2,405,492.11..3,050,068.82 rows=92,499 width=176) (actual time=306,974.398..321,677.298 rows=59,568 loops=1)

  • Buffers: shared hit=1160962 read=625322 dirtied=1, temp read=155222 written=155160
15. 4,073.122 311,132.652 ↑ 1.6 59,568 1

Hash Join (cost=2,405,491.68..2,950,227.11 rows=92,499 width=167) (actual time=306,972.837..311,132.652 rows=59,568 loops=1)

  • Hash Cond: (ab.lead_id = l.id)
  • Buffers: shared hit=953439 read=594404 dirtied=1, temp read=155222 written=155160
16. 208,108.709 208,108.709 ↑ 1.6 59,568 1

CTE Scan on all_bookings ab (cost=0.00..1,849.98 rows=92,499 width=24) (actual time=208,012.329..208,108.709 rows=59,568 loops=1)

  • Buffers: shared hit=452122 read=594361 dirtied=1
17. 15,411.951 98,950.821 ↓ 1.0 26,226,880 1

Hash (cost=1,540,148.95..1,540,148.95 rows=26,216,298 width=143) (actual time=98,950.821..98,950.821 rows=26,226,880 loops=1)

  • Buckets: 2097152 Batches: 32 Memory Usage: 65900kB
  • Buffers: shared hit=501317 read=43, temp written=154805
18. 21,948.252 83,538.870 ↓ 1.0 26,226,880 1

Hash Left Join (cost=81.48..1,540,148.95 rows=26,216,298 width=143) (actual time=6.909..83,538.870 rows=26,226,880 loops=1)

  • Hash Cond: (l.appops_status_id = aps.id)
  • Buffers: shared hit=501317 read=43
19. 22,377.326 61,590.537 ↓ 1.0 26,226,880 1

Hash Left Join (cost=79.35..1,209,427.75 rows=26,216,298 width=109) (actual time=6.808..61,590.537 rows=26,226,880 loops=1)

  • Hash Cond: (l.product_family_id = pf.id)
  • Buffers: shared hit=501316 read=43
20. 26,334.193 39,213.151 ↓ 1.0 26,226,880 1

Hash Left Join (cost=77.41..880,290.84 rows=26,216,298 width=109) (actual time=6.728..39,213.151 rows=26,226,880 loops=1)

  • Hash Cond: (l.lender_offer_id = lo.id)
  • Buffers: shared hit=501315 read=43
21. 12,872.262 12,872.262 ↓ 1.0 26,226,880 1

Seq Scan on leads l (cost=0.00..763,113.98 rows=26,216,298 width=59) (actual time=0.013..12,872.262 rows=26,226,880 loops=1)

  • Buffers: shared hit=500951
22. 0.279 6.696 ↓ 1.0 439 1

Hash (cost=72.07..72.07 rows=427 width=70) (actual time=6.696..6.696 rows=439 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 45kB
  • Buffers: shared hit=364 read=43
23. 2.847 6.417 ↓ 1.0 439 1

Merge Left Join (cost=0.55..72.07 rows=427 width=70) (actual time=0.030..6.417 rows=439 loops=1)

  • Merge Cond: (lo.lender_id = len.id)
  • Buffers: shared hit=364 read=43
24. 3.570 3.570 ↓ 1.1 1,197 1

Index Scan using lenders_pkey on lenders len (cost=0.28..58.39 rows=1,101 width=31) (actual time=0.011..3.570 rows=1,197 loops=1)

  • Buffers: shared hit=77 read=35
25. 0.031 0.060 ↓ 1.2 49 1

Hash (cost=1.42..1.42 rows=42 width=8) (actual time=0.060..0.060 rows=49 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=1
26. 0.029 0.029 ↓ 1.2 49 1

Seq Scan on product_families pf (cost=0.00..1.42 rows=42 width=8) (actual time=0.008..0.029 rows=49 loops=1)

  • Buffers: shared hit=1
27. 0.034 0.081 ↓ 1.2 60 1

Hash (cost=1.50..1.50 rows=50 width=42) (actual time=0.081..0.081 rows=60 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
  • Buffers: shared hit=1
28. 0.047 0.047 ↓ 1.2 60 1

Seq Scan on appops_status aps (cost=0.00..1.50 rows=50 width=42) (actual time=0.009..0.047 rows=60 loops=1)

  • Buffers: shared hit=1
29. 10,424.400 10,424.400 ↑ 1.0 1 59,568

Index Scan using idx_lead_id_lead_params on lead_parameters lp (cost=0.43..1.08 rows=1 width=17) (actual time=0.175..0.175 rows=1 loops=59,568)

  • Index Cond: (lead_id = ab.lead_id)
  • Buffers: shared hit=207523 read=30918
30. 1,787.040 85,718.352 ↑ 1.0 1 59,568

Aggregate (cost=31.88..31.89 rows=1 width=32) (actual time=1.439..1.439 rows=1 loops=59,568)

  • Buffers: shared hit=223092 read=260206 dirtied=674
31. 953.088 83,931.312 ↑ 3.9 16 59,568

Unique (cost=30.77..31.09 rows=63 width=56) (actual time=1.389..1.409 rows=16 loops=59,568)

  • Buffers: shared hit=223092 read=260206 dirtied=674
32. 1,250.928 82,978.224 ↑ 3.9 16 59,568

Sort (cost=30.77..30.93 rows=63 width=56) (actual time=1.387..1.393 rows=16 loops=59,568)

  • Sort Key: q.question_id, lua.created_at DESC
  • Sort Method: quicksort Memory: 27kB
  • Buffers: shared hit=223092 read=260206 dirtied=674
33. 1,131.456 81,727.296 ↑ 3.9 16 59,568

Hash Join (cost=13.90..28.89 rows=63 width=56) (actual time=0.640..1.372 rows=16 loops=59,568)

  • Hash Cond: (lua.question_id = q.question_id)
  • Buffers: shared hit=223092 read=260206 dirtied=674
34. 80,595.504 80,595.504 ↑ 3.9 16 59,568

Index Scan using idx_lead_id_lender_user_answers on lender_user_answers lua (cost=0.57..14.76 rows=63 width=21) (actual time=0.638..1.353 rows=16 loops=59,568)

  • Index Cond: (lead_id = l.id)
  • Buffers: shared hit=223086 read=260206 dirtied=674
35. 0.177 0.336 ↑ 1.1 306 1

Hash (cost=9.26..9.26 rows=326 width=39) (actual time=0.336..0.336 rows=306 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
  • Buffers: shared hit=6
36. 0.159 0.159 ↑ 1.1 306 1

Seq Scan on questions q (cost=0.00..9.26 rows=326 width=39) (actual time=0.009..0.159 rows=306 loops=1)

  • Buffers: shared hit=6
37. 10.834 27.563 ↑ 1.0 19,100 1

Hash (cost=400.00..400.00 rows=19,100 width=35) (actual time=27.563..27.563 rows=19,100 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1372kB
  • Buffers: shared read=209
38. 16.729 16.729 ↑ 1.0 19,100 1

Seq Scan on pincodes p (cost=0.00..400.00 rows=19,100 width=35) (actual time=0.528..16.729 rows=19,100 loops=1)

  • Buffers: shared read=209
39. 178.704 2,025.312 ↑ 1.0 1 59,568

Aggregate (cost=8.17..8.18 rows=1 width=8) (actual time=0.034..0.034 rows=1 loops=59,568)

  • Buffers: shared hit=574421 read=1701
40. 1,846.608 1,846.608 ↑ 1.0 2 59,568

Index Scan using idx_user_id_customer_profiles on customer_profiles (cost=0.44..8.17 rows=2 width=8) (actual time=0.028..0.031 rows=2 loops=59,568)

  • Index Cond: (user_id = l.user_id)
  • Filter: (created_on <= COALESCE(ab.applied_date, l.date_of_referral, ab.booking_date))
  • Rows Removed by Filter: 5
  • Buffers: shared hit=574421 read=1701
41. 13,700.640 13,700.640 ↑ 1.0 1 59,568

Index Scan using idx_customer_profile_id_customer_profiles on customer_profiles cp (cost=0.56..2.98 rows=1 width=24) (actual time=0.230..0.230 rows=1 loops=59,568)

  • Index Cond: (customer_profile_id = (max(customer_profiles.customer_profile_id)))
  • Buffers: shared hit=262454 read=35369
42.          

SubPlan (forResult)

43. 357.408 1,310.496 ↑ 1.0 1 59,568

Aggregate (cost=8.17..8.18 rows=1 width=8) (actual time=0.022..0.022 rows=1 loops=59,568)

  • Buffers: shared hit=576122
44. 953.088 953.088 ↑ 1.0 7 59,568

Index Scan using idx_user_id_customer_profiles on customer_profiles customer_profiles_1 (cost=0.44..8.15 rows=7 width=8) (actual time=0.006..0.016 rows=7 loops=59,568)

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