explain.depesz.com

PostgreSQL's explain analyze made readable

Result: llnY

Settings
# exclusive inclusive rows x rows loops node
1. 3,392.326 127,056.652 ↑ 1.4 1,402,478 1

Merge Left Join (cost=13,821,163.40..13,975,924.25 rows=2,031,224 width=1,150) (actual time=121,257.314..127,056.652 rows=1,402,478 loops=1)

  • Merge Cond: (pol.policy_id = pol_rid_3.policy_id)
2. 380.102 120,649.331 ↑ 1.4 1,402,478 1

Merge Left Join (cost=13,230,992.36..13,252,326.34 rows=2,031,224 width=732) (actual time=118,284.462..120,649.331 rows=1,402,478 loops=1)

  • Merge Cond: (pol.policy_id = pws.policy_id)
3. 718.487 120,000.168 ↑ 1.4 1,402,286 1

Merge Left Join (cost=13,172,708.91..13,183,848.22 rows=2,031,224 width=730) (actual time=118,048.366..120,000.168 rows=1,402,286 loops=1)

  • Merge Cond: (pol.policy_id = pol_rid.policy_id)
4. 5,242.254 115,664.862 ↑ 1.4 1,402,286 1

Sort (cost=12,585,214.71..12,590,292.77 rows=2,031,224 width=312) (actual time=114,455.521..115,664.862 rows=1,402,286 loops=1)

  • Sort Key: pol.policy_id
  • Sort Method: external merge Disk: 314560kB
5. 344.609 110,422.608 ↑ 1.4 1,402,286 1

Merge Left Join (cost=12,119,112.46..12,164,124.20 rows=2,031,224 width=312) (actual time=103,737.618..110,422.608 rows=1,402,286 loops=1)

  • Merge Cond: (p.party_id = cb.party_id)
6. 517.908 109,832.816 ↑ 1.5 1,376,220 1

Merge Left Join (cost=11,999,595.34..12,029,567.41 rows=2,031,224 width=316) (actual time=103,525.667..109,832.816 rows=1,376,220 loops=1)

  • Merge Cond: (p.party_id = ph.party_id)
7. 799.272 105,170.035 ↑ 1.5 1,376,220 1

Merge Left Join (cost=11,736,956.44..11,759,273.94 rows=2,031,224 width=305) (actual time=99,622.196..105,170.035 rows=1,376,220 loops=1)

  • Merge Cond: (p.party_id = ad_1.party_id)
8. 977.538 86,768.735 ↑ 1.5 1,372,384 1

Merge Left Join (cost=7,411,893.29..7,427,219.51 rows=2,031,224 width=235) (actual time=83,199.787..86,768.735 rows=1,372,384 loops=1)

  • Merge Cond: (p.party_id = ad.party_id)
9. 4,491.631 57,842.958 ↑ 1.5 1,372,224 1

Sort (cost=3,845,784.13..3,850,862.19 rows=2,031,224 width=165) (actual time=56,438.083..57,842.958 rows=1,372,224 loops=1)

  • Sort Key: p.party_id
  • Sort Method: external merge Disk: 217736kB
10. 402.687 53,351.327 ↑ 1.5 1,372,224 1

Hash Join (cost=2,658,574.47..3,513,956.13 rows=2,031,224 width=165) (actual time=42,350.639..53,351.327 rows=1,372,224 loops=1)

  • Hash Cond: (pol.product_id = pd.product_id)
11. 2,886.496 52,948.320 ↑ 1.4 1,451,491 1

Hash Join (cost=2,658,519.20..3,445,265.05 rows=2,053,090 width=151) (actual time=42,350.301..52,948.320 rows=1,451,491 loops=1)

  • Hash Cond: (pr.party_id = p.party_id)
12. 1,862.416 43,705.755 ↑ 1.8 1,451,491 1

Hash Right Join (cost=2,092,336.45..2,690,527.90 rows=2,653,393 width=77) (actual time=35,981.245..43,705.755 rows=1,451,491 loops=1)

  • Hash Cond: (agr.policy_id = pol.policy_id)
13. 421.007 5,993.778 ↑ 1.1 1,209,451 1

Hash Join (cost=83,391.38..604,363.29 rows=1,280,050 width=13) (actual time=129.426..5,993.778 rows=1,209,451 loops=1)

  • Hash Cond: (agr.agent_id = ag.agent_id)
14. 5,444.276 5,444.276 ↑ 1.1 1,209,451 1

Seq Scan on agent_role agr (cost=0.00..477,770.23 rows=1,280,050 width=16) (actual time=0.048..5,444.276 rows=1,209,451 loops=1)

  • Filter: ((commissionable = 'Y'::bpchar) AND (active = 'Y'::bpchar) AND (now() >= start_date) AND (now() <= end_date) AND (now() >= role_start_date) AND (now() <= role_end_date))
  • Rows Removed by Filter: 3026938
15. 36.670 128.495 ↑ 1.0 153,125 1

Hash (cost=78,414.78..78,414.78 rows=153,126 width=13) (actual time=128.495..128.495 rows=153,125 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 9226kB
16. 91.825 91.825 ↑ 1.0 153,125 1

Seq Scan on agent ag (cost=0.00..78,414.78 rows=153,126 width=13) (actual time=0.008..91.825 rows=153,125 loops=1)

17. 474.113 35,849.561 ↑ 1.9 1,389,037 1

Hash (cost=1,891,614.80..1,891,614.80 rows=2,653,393 width=72) (actual time=35,849.561..35,849.561 rows=1,389,037 loops=1)

  • Buckets: 524288 Batches: 8 Memory Usage: 23181kB
18. 1,113.648 35,375.448 ↑ 1.9 1,389,037 1

Hash Right Join (cost=1,375,489.64..1,891,614.80 rows=2,653,393 width=72) (actual time=30,169.603..35,375.448 rows=1,389,037 loops=1)

  • Hash Cond: (agr_1.policy_id = pol.policy_id)
19. 30.879 4,213.332 ↑ 1.0 103,885 1

Hash Join (cost=83,391.38..564,672.85 rows=104,037 width=13) (actual time=119.224..4,213.332 rows=103,885 loops=1)

  • Hash Cond: (agr_1.agent_id = ag_1.agent_id)
20. 4,064.101 4,064.101 ↑ 1.0 103,885 1

Seq Scan on agent_role agr_1 (cost=0.00..477,770.23 rows=104,037 width=16) (actual time=0.048..4,064.101 rows=103,885 loops=1)

  • Filter: ((commissionable = 'N'::bpchar) AND (active = 'Y'::bpchar) AND (now() >= start_date) AND (now() <= end_date) AND (now() >= role_start_date) AND (now() <= role_end_date))
  • Rows Removed by Filter: 4132504
21. 31.431 118.352 ↑ 1.0 153,125 1

Hash (cost=78,414.78..78,414.78 rows=153,126 width=13) (actual time=118.352..118.352 rows=153,125 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 9226kB
22. 86.921 86.921 ↑ 1.0 153,125 1

Seq Scan on agent ag_1 (cost=0.00..78,414.78 rows=153,126 width=13) (actual time=0.005..86.921 rows=153,125 loops=1)

23. 492.151 30,048.468 ↑ 1.9 1,388,778 1

Hash (cost=1,174,767.99..1,174,767.99 rows=2,653,393 width=67) (actual time=30,048.468..30,048.468 rows=1,388,778 loops=1)

  • Buckets: 524288 Batches: 8 Memory Usage: 23025kB
24. 1,917.608 29,556.317 ↑ 1.9 1,388,778 1

Hash Join (cost=596,462.19..1,174,767.99 rows=2,653,393 width=67) (actual time=15,656.338..29,556.317 rows=1,388,778 loops=1)

  • Hash Cond: (pr.policy_id = pol.policy_id)
25. 12,016.658 22,524.785 ↑ 1.0 2,596,660 1

Bitmap Heap Scan on party_role pr (cost=71,468.55..480,340.12 rows=2,653,393 width=19) (actual time=10,540.298..22,524.785 rows=2,596,660 loops=1)

  • Recheck Cond: ((role_type)::text = ANY ('{01,13}'::text[]))
  • Filter: ((now() >= start_date) AND (now() <= end_date))
  • Rows Removed by Filter: 1997665
  • Heap Blocks: exact=100720
26. 10,508.127 10,508.127 ↑ 1.0 4,628,620 1

Bitmap Index Scan on idx_party_role_role_type (cost=0.00..70,805.20 rows=4,747,778 width=0) (actual time=10,508.127..10,508.127 rows=4,628,620 loops=1)

  • Index Cond: ((role_type)::text = ANY ('{01,13}'::text[]))
27. 629.659 5,113.924 ↑ 2.9 1,361,444 1

Hash (cost=356,911.80..356,911.80 rows=3,976,795 width=56) (actual time=5,113.924..5,113.924 rows=1,361,444 loops=1)

  • Buckets: 524288 Batches: 16 Memory Usage: 12033kB
28. 4,484.265 4,484.265 ↑ 2.9 1,361,444 1

Seq Scan on policy pol (cost=0.00..356,911.80 rows=3,976,795 width=56) (actual time=1.074..4,484.265 rows=1,361,444 loops=1)

  • Filter: ((now() >= start_date) AND (now() <= end_date))
  • Rows Removed by Filter: 6
29. 1,019.096 6,356.069 ↑ 1.0 3,145,507 1

Hash (cost=420,946.96..420,946.96 rows=3,145,532 width=82) (actual time=6,356.069..6,356.069 rows=3,145,507 loops=1)

  • Buckets: 524288 Batches: 16 Memory Usage: 22245kB
30. 5,336.973 5,336.973 ↑ 1.0 3,145,507 1

Seq Scan on party p (cost=0.00..420,946.96 rows=3,145,532 width=82) (actual time=537.928..5,336.973 rows=3,145,507 loops=1)

31. 0.099 0.320 ↑ 1.0 320 1

Hash (cost=44.87..44.87 rows=320 width=30) (actual time=0.320..0.320 rows=320 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
32. 0.221 0.221 ↑ 1.0 320 1

Seq Scan on product pd (cost=0.00..44.87 rows=320 width=30) (actual time=0.021..0.221 rows=320 loops=1)

  • Filter: ((product_line_grouping)::text = ANY ('{"Indexed Annuity","Fixed Annuity",SPIA}'::text[]))
  • Rows Removed by Filter: 2
33. 4,692.284 27,948.239 ↓ 12.4 2,626,804 1

Sort (cost=3,566,109.16..3,566,639.48 rows=212,129 width=78) (actual time=26,761.679..27,948.239 rows=2,626,804 loops=1)

  • Sort Key: ad.party_id
  • Sort Method: external sort Disk: 158088kB
34. 13,756.693 23,255.955 ↓ 12.1 2,570,022 1

Bitmap Heap Scan on address ad (cost=639,993.00..3,547,341.49 rows=212,129 width=78) (actual time=9,543.946..23,255.955 rows=2,570,022 loops=1)

  • Recheck Cond: (address_type = 4)
  • Filter: ((end_date > current_date) AND (now() >= COALESCE(cleansed_source_effective_date, '1900-01-01'::date)) AND (now() <= COALESCE(cleansed_source_expiration_date, '2300-01-01'::date)) AND (((lineage ->> 'src'::text) = 'as400'::text) OR ((lineage ->> 'src'::text) <> 'as400'::text)))
  • Rows Removed by Filter: 161818
  • Heap Blocks: exact=153413
35. 9,499.262 9,499.262 ↑ 1.0 2,732,634 1

Bitmap Index Scan on idx_address_address_type (cost=0.00..639,939.97 rows=2,829,255 width=0) (actual time=9,499.262..9,499.262 rows=2,732,634 loops=1)

  • Index Cond: (address_type = 4)
36. 7,145.793 17,602.028 ↓ 36.4 2,857,887 1

Sort (cost=4,325,063.14..4,325,259.39 rows=78,499 width=78) (actual time=16,422.387..17,602.028 rows=2,857,887 loops=1)

  • Sort Key: ad_1.party_id
  • Sort Method: external sort Disk: 171024kB
37. 7,732.287 10,456.235 ↓ 35.2 2,762,444 1

Bitmap Heap Scan on address ad_1 (cost=874,336.65..4,318,681.02 rows=78,499 width=78) (actual time=2,773.973..10,456.235 rows=2,762,444 loops=1)

  • Recheck Cond: ((address_type = 4) OR (address_type = 1))
  • Filter: ((end_date > current_date) AND (now() >= COALESCE(cleansed_source_effective_date, '1900-01-01'::date)) AND (now() <= COALESCE(cleansed_source_expiration_date, '2300-01-01'::date)) AND ((((lineage ->> 'src'::text) = 'as400'::text) AND (address_type = 4)) OR (((lineage ->> 'src'::text) <> 'as400'::text) AND (address_type = 1))))
  • Rows Removed by Filter: 1064208
  • Heap Blocks: exact=172994
38. 0.004 2,723.948 ↓ 0.0 0 1

BitmapOr (cost=874,336.65..874,336.65 rows=3,865,372 width=0) (actual time=2,723.948..2,723.948 rows=0 loops=1)

39. 231.065 231.065 ↑ 1.0 2,732,634 1

Bitmap Index Scan on idx_address_address_type (cost=0.00..639,939.97 rows=2,829,255 width=0) (actual time=231.065..231.065 rows=2,732,634 loops=1)

  • Index Cond: (address_type = 4)
40. 2,492.879 2,492.879 ↓ 1.1 1,094,814 1

Bitmap Index Scan on idx_address_address_type (cost=0.00..234,357.43 rows=1,036,116 width=0) (actual time=2,492.879..2,492.879 rows=1,094,814 loops=1)

  • Index Cond: (address_type = 1)
41. 1,425.117 4,144.873 ↓ 12.5 1,326,172 1

Sort (cost=262,638.89..262,903.17 rows=105,714 width=19) (actual time=3,903.449..4,144.873 rows=1,326,172 loops=1)

  • Sort Key: ph.party_id
  • Sort Method: external sort Disk: 46264kB
42. 2,719.756 2,719.756 ↓ 11.8 1,245,921 1

Seq Scan on phone ph (cost=0.00..253,817.16 rows=105,714 width=19) (actual time=0.595..2,719.756 rows=1,245,921 loops=1)

  • Filter: (((phone_type)::text = 'HOME'::text) AND (end_date > current_date) AND (now() >= COALESCE(source_effective_date, '1900-01-01'::date)) AND (now() <= COALESCE(source_expiration_date, '2300-01-01'::date)))
  • Rows Removed by Filter: 997082
43. 90.096 245.183 ↑ 1.6 260,422 1

Sort (cost=119,516.92..120,551.69 rows=413,908 width=12) (actual time=211.938..245.183 rows=260,422 loops=1)

  • Sort Key: cb.party_id
  • Sort Method: quicksort Memory: 17341kB
44. 14.506 155.087 ↑ 1.7 238,868 1

Append (cost=0.00..80,901.48 rows=413,908 width=12) (actual time=0.033..155.087 rows=238,868 loops=1)

45. 0.067 0.067 ↑ 1.9 62 1

Seq Scan on customer_banking cb (cost=0.00..9.80 rows=120 width=24) (actual time=0.032..0.067 rows=62 loops=1)

  • Filter: ((now() >= start_date) AND (now() <= end_date))
46. 113.912 113.912 ↑ 1.7 238,806 1

Seq Scan on customer_banking_current cb_1 (cost=0.00..34,438.64 rows=413,787 width=12) (actual time=0.011..113.912 rows=238,806 loops=1)

  • Filter: ((now() >= start_date) AND (now() <= end_date))
47. 26.602 26.602 ↓ 0.0 0 1

Seq Scan on customer_banking_history cb_2 (cost=0.00..46,453.04 rows=1 width=12) (actual time=26.602..26.602 rows=0 loops=1)

  • Filter: ((now() >= start_date) AND (now() <= end_date))
  • Rows Removed by Filter: 451
48. 70.082 3,616.819 ↓ 3.6 173,439 1

Sort (cost=587,494.20..587,615.15 rows=48,378 width=426) (actual time=3,592.832..3,616.819 rows=173,439 loops=1)

  • Sort Key: pol_rid.policy_id
  • Sort Method: quicksort Memory: 18950kB
49. 32.058 3,546.737 ↓ 3.1 152,364 1

Hash Join (cost=13.12..583,729.90 rows=48,378 width=426) (actual time=0.626..3,546.737 rows=152,364 loops=1)

  • Hash Cond: (pol_rid.dbr_rider_id = rid.rider_id)
50. 9.343 3,514.651 ↑ 1.3 152,364 1

Append (cost=0.00..581,539.76 rows=193,512 width=16) (actual time=0.564..3,514.651 rows=152,364 loops=1)

51. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on policy_rider pol_rid (cost=0.00..0.00 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=1)

  • Filter: ((dbr_rider_id IS NOT NULL) AND (now() >= start_date) AND (now() <= end_date))
52. 1,303.384 1,303.384 ↑ 1.3 152,364 1

Seq Scan on policy_rider_current pol_rid_1 (cost=0.00..135,392.24 rows=193,510 width=16) (actual time=0.559..1,303.384 rows=152,364 loops=1)

  • Filter: ((dbr_rider_id IS NOT NULL) AND (now() >= start_date) AND (now() <= end_date))
  • Rows Removed by Filter: 1213317
53. 2,201.921 2,201.921 ↓ 0.0 0 1

Seq Scan on policy_rider_history pol_rid_2 (cost=0.00..446,147.52 rows=1 width=16) (actual time=2,201.921..2,201.921 rows=0 loops=1)

  • Filter: ((dbr_rider_id IS NOT NULL) AND (now() >= start_date) AND (now() <= end_date))
  • Rows Removed by Filter: 6386506
54. 0.016 0.028 ↑ 1.1 45 1

Hash (cost=11.50..11.50 rows=50 width=426) (actual time=0.028..0.028 rows=45 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
55. 0.012 0.012 ↑ 1.1 45 1

Seq Scan on rider rid (cost=0.00..11.50 rows=50 width=426) (actual time=0.007..0.012 rows=45 loops=1)

56. 96.689 269.061 ↓ 1.1 269,991 1

Sort (cost=58,283.33..58,912.75 rows=251,770 width=10) (actual time=236.076..269.061 rows=269,991 loops=1)

  • Sort Key: pws.policy_id
  • Sort Method: quicksort Memory: 17558kB
57. 172.372 172.372 ↑ 1.0 243,482 1

Seq Scan on policy_withdrawal_schedule pws (cost=0.00..35,697.36 rows=251,770 width=10) (actual time=0.020..172.372 rows=243,482 loops=1)

  • Filter: ((now() >= start_date) AND (now() <= end_date))
  • Rows Removed by Filter: 154594
58. 125.338 3,014.995 ↓ 4.6 317,759 1

Sort (cost=590,171.04..590,342.93 rows=68,755 width=434) (actual time=2,972.736..3,014.995 rows=317,759 loops=1)

  • Sort Key: pol_rid_3.policy_id
  • Sort Method: quicksort Memory: 31570kB
59. 49.431 2,889.657 ↓ 3.9 269,038 1

Hash Join (cost=13.12..584,646.86 rows=68,755 width=434) (actual time=0.068..2,889.657 rows=269,038 loops=1)

  • Hash Cond: (pol_rid_3.ir_rider_id = rid_1.rider_id)
60. 14.916 2,840.203 ↑ 1.0 269,038 1

Append (cost=0.00..581,539.76 rows=275,019 width=16) (actual time=0.024..2,840.203 rows=269,038 loops=1)

61. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on policy_rider pol_rid_3 (cost=0.00..0.00 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=1)

  • Filter: ((ir_rider_id IS NOT NULL) AND (now() >= start_date) AND (now() <= end_date))
62. 303.541 303.541 ↑ 1.0 269,038 1

Seq Scan on policy_rider_current pol_rid_4 (cost=0.00..135,392.24 rows=275,017 width=16) (actual time=0.019..303.541 rows=269,038 loops=1)

  • Filter: ((ir_rider_id IS NOT NULL) AND (now() >= start_date) AND (now() <= end_date))
  • Rows Removed by Filter: 1096643
63. 2,521.744 2,521.744 ↓ 0.0 0 1

Seq Scan on policy_rider_history pol_rid_5 (cost=0.00..446,147.52 rows=1 width=16) (actual time=2,521.744..2,521.744 rows=0 loops=1)

  • Filter: ((ir_rider_id IS NOT NULL) AND (now() >= start_date) AND (now() <= end_date))
  • Rows Removed by Filter: 6386506
64. 0.013 0.023 ↑ 1.1 45 1

Hash (cost=11.50..11.50 rows=50 width=426) (actual time=0.023..0.023 rows=45 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
65. 0.010 0.010 ↑ 1.1 45 1

Seq Scan on rider rid_1 (cost=0.00..11.50 rows=50 width=426) (actual time=0.004..0.010 rows=45 loops=1)