explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wNqC

Settings
# exclusive inclusive rows x rows loops node
1. 3,496.741 119,413.082 ↑ 1.4 1,402,478 1

Merge Left Join (cost=13,809,122.04..13,964,092.20 rows=2,031,224 width=1,150) (actual time=113,488.462..119,413.082 rows=1,402,478 loops=1)

  • Merge Cond: (pol.policy_id = pol_rid_3.policy_id)
2. 387.328 113,200.788 ↑ 1.4 1,402,478 1

Merge Left Join (cost=13,218,951.00..13,240,455.29 rows=2,031,224 width=732) (actual time=110,812.778..113,200.788 rows=1,402,478 loops=1)

  • Merge Cond: (pol.policy_id = pws.policy_id)
3. 732.386 112,532.681 ↑ 1.4 1,402,286 1

Merge Left Join (cost=13,160,667.55..13,171,834.31 rows=2,031,224 width=730) (actual time=110,562.219..112,532.681 rows=1,402,286 loops=1)

  • Merge Cond: (pol.policy_id = pol_rid.policy_id)
4. 5,062.815 106,410.430 ↑ 1.4 1,402,286 1

Sort (cost=12,573,173.35..12,578,251.41 rows=2,031,224 width=312) (actual time=105,193.982..106,410.430 rows=1,402,286 loops=1)

  • Sort Key: pol.policy_id
  • Sort Method: external merge Disk: 314560kB
5. 363.100 101,347.615 ↑ 1.4 1,402,286 1

Merge Left Join (cost=12,107,062.63..12,152,082.84 rows=2,031,224 width=312) (actual time=94,155.304..101,347.615 rows=1,402,286 loops=1)

  • Merge Cond: (p.party_id = cb.party_id)
6. 509.632 100,747.171 ↑ 1.5 1,376,220 1

Merge Left Join (cost=11,987,554.22..12,017,526.29 rows=2,031,224 width=316) (actual time=93,947.084..100,747.171 rows=1,376,220 loops=1)

  • Merge Cond: (p.party_id = ph.party_id)
7. 789.462 95,084.750 ↑ 1.5 1,376,220 1

Merge Left Join (cost=11,724,915.32..11,747,232.82 rows=2,031,224 width=305) (actual time=89,022.765..95,084.750 rows=1,376,220 loops=1)

  • Merge Cond: (p.party_id = ad_1.party_id)
8. 992.372 75,352.151 ↑ 1.5 1,372,384 1

Merge Left Join (cost=7,399,852.17..7,415,178.39 rows=2,031,224 width=235) (actual time=71,810.576..75,352.151 rows=1,372,384 loops=1)

  • Merge Cond: (p.party_id = ad.party_id)
9. 6,152.101 44,704.540 ↑ 1.5 1,372,224 1

Sort (cost=3,833,743.01..3,838,821.07 rows=2,031,224 width=165) (actual time=43,161.136..44,704.540 rows=1,372,224 loops=1)

  • Sort Key: p.party_id
  • Sort Method: external merge Disk: 217736kB
10. 367.619 38,552.439 ↑ 1.5 1,372,224 1

Hash Join (cost=2,645,584.60..3,501,915.01 rows=2,031,224 width=165) (actual time=30,068.094..38,552.439 rows=1,372,224 loops=1)

  • Hash Cond: (pol.product_id = pd.product_id)
11. 2,392.281 38,184.616 ↑ 1.4 1,451,491 1

Hash Join (cost=2,645,529.33..3,433,223.93 rows=2,053,090 width=151) (actual time=30,067.873..38,184.616 rows=1,451,491 loops=1)

  • Hash Cond: (pr.party_id = p.party_id)
12. 1,667.805 31,638.172 ↑ 1.8 1,451,491 1

Hash Right Join (cost=2,079,346.58..2,678,486.78 rows=2,653,393 width=77) (actual time=25,911.077..31,638.172 rows=1,451,491 loops=1)

  • Hash Cond: (agr.policy_id = pol.policy_id)
13. 389.765 4,179.332 ↑ 1.1 1,209,451 1

Hash Join (cost=83,391.38..604,363.29 rows=1,280,050 width=13) (actual time=117.823..4,179.332 rows=1,209,451 loops=1)

  • Hash Cond: (agr.agent_id = ag.agent_id)
14. 3,672.703 3,672.703 ↑ 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.024..3,672.703 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. 31.955 116.864 ↑ 1.0 153,125 1

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

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

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

17. 486.801 25,791.035 ↑ 1.9 1,389,037 1

Hash (cost=1,878,624.93..1,878,624.93 rows=2,653,393 width=72) (actual time=25,791.035..25,791.035 rows=1,389,037 loops=1)

  • Buckets: 524288 Batches: 8 Memory Usage: 23181kB
18. 1,033.676 25,304.234 ↑ 1.9 1,389,037 1

Hash Right Join (cost=1,362,422.64..1,878,624.93 rows=2,653,393 width=72) (actual time=21,138.123..25,304.234 rows=1,389,037 loops=1)

  • Hash Cond: (agr_1.policy_id = pol.policy_id)
19. 27.930 3,736.574 ↑ 1.0 103,885 1

Hash Join (cost=83,391.38..564,672.85 rows=104,037 width=13) (actual time=602.474..3,736.574 rows=103,885 loops=1)

  • Hash Cond: (agr_1.agent_id = ag_1.agent_id)
20. 3,598.727 3,598.727 ↑ 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=491.838..3,598.727 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. 28.144 109.917 ↑ 1.0 153,125 1

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

  • Buckets: 262144 Batches: 1 Memory Usage: 9226kB
22. 81.773 81.773 ↑ 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.007..81.773 rows=153,125 loops=1)

23. 489.438 20,533.984 ↑ 1.9 1,388,778 1

Hash (cost=1,161,700.99..1,161,700.99 rows=2,653,393 width=67) (actual time=20,533.984..20,533.984 rows=1,388,778 loops=1)

  • Buckets: 524288 Batches: 8 Memory Usage: 23025kB
24. 1,760.361 20,044.546 ↑ 1.9 1,388,778 1

Hash Join (cost=584,781.19..1,161,700.99 rows=2,653,393 width=67) (actual time=11,531.093..20,044.546 rows=1,388,778 loops=1)

  • Hash Cond: (pr.policy_id = pol.policy_id)
25. 6,780.987 15,294.511 ↑ 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=8,539.642..15,294.511 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. 8,513.524 8,513.524 ↑ 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=8,513.524..8,513.524 rows=4,628,620 loops=1)

  • Index Cond: ((role_type)::text = ANY ('{01,13}'::text[]))
27. 441.488 2,989.674 ↑ 2.8 1,361,444 1

Hash (cost=351,231.80..351,231.80 rows=3,834,795 width=56) (actual time=2,989.674..2,989.674 rows=1,361,444 loops=1)

  • Buckets: 524288 Batches: 16 Memory Usage: 12033kB
28. 2,548.186 2,548.186 ↑ 2.8 1,361,444 1

Seq Scan on policy pol (cost=0.00..351,231.80 rows=3,834,795 width=56) (actual time=1.627..2,548.186 rows=1,361,444 loops=1)

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

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

  • Buckets: 524288 Batches: 16 Memory Usage: 22245kB
30. 3,178.548 3,178.548 ↑ 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=229.739..3,178.548 rows=3,145,507 loops=1)

31. 0.066 0.204 ↑ 1.0 320 1

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

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

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

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

Sort (cost=3,566,109.16..3,566,639.48 rows=212,129 width=78) (actual time=28,647.650..29,655.239 rows=2,626,804 loops=1)

  • Sort Key: ad.party_id
  • Sort Method: external sort Disk: 158088kB
34. 16,485.849 25,269.962 ↓ 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=8,832.443..25,269.962 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. 8,784.113 8,784.113 ↑ 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=8,784.113..8,784.113 rows=2,732,634 loops=1)

  • Index Cond: (address_type = 4)
36. 7,926.686 18,943.137 ↓ 36.4 2,857,887 1

Sort (cost=4,325,063.14..4,325,259.39 rows=78,499 width=78) (actual time=17,212.176..18,943.137 rows=2,857,887 loops=1)

  • Sort Key: ad_1.party_id
  • Sort Method: external sort Disk: 171024kB
37. 9,550.639 11,016.451 ↓ 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=1,514.241..11,016.451 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.001 1,465.812 ↓ 0.0 0 1

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

39. 211.869 211.869 ↑ 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=211.869..211.869 rows=2,732,634 loops=1)

  • Index Cond: (address_type = 4)
40. 1,253.942 1,253.942 ↓ 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=1,253.942..1,253.942 rows=1,094,814 loops=1)

  • Index Cond: (address_type = 1)
41. 1,622.718 5,152.789 ↓ 12.5 1,326,172 1

Sort (cost=262,638.89..262,903.17 rows=105,714 width=19) (actual time=4,924.313..5,152.789 rows=1,326,172 loops=1)

  • Sort Key: ph.party_id
  • Sort Method: external sort Disk: 46264kB
42. 3,530.071 3,530.071 ↓ 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.033..3,530.071 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. 83.382 237.344 ↑ 1.6 260,422 1

Sort (cost=119,508.21..120,542.83 rows=413,846 width=12) (actual time=208.215..237.344 rows=260,422 loops=1)

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

Append (cost=0.00..80,899.00 rows=413,846 width=12) (actual time=0.019..153.962 rows=238,868 loops=1)

45. 0.048 0.048 ↓ 1.1 62 1

Seq Scan on customer_banking cb (cost=0.00..7.32 rows=58 width=24) (actual time=0.019..0.048 rows=62 loops=1)

  • Filter: ((now() >= start_date) AND (now() <= end_date))
46. 111.059 111.059 ↑ 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.006..111.059 rows=238,806 loops=1)

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

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

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

Sort (cost=587,494.20..587,615.15 rows=48,378 width=426) (actual time=5,367.419..5,389.865 rows=173,439 loops=1)

  • Sort Key: pol_rid.policy_id
  • Sort Method: quicksort Memory: 18950kB
49. 31.973 5,120.463 ↓ 3.1 152,364 1

Hash Join (cost=13.12..583,729.90 rows=48,378 width=426) (actual time=0.060..5,120.463 rows=152,364 loops=1)

  • Hash Cond: (pol_rid.dbr_rider_id = rid.rider_id)
50. 9.742 5,088.470 ↑ 1.3 152,364 1

Append (cost=0.00..581,539.76 rows=193,512 width=16) (actual time=0.024..5,088.470 rows=152,364 loops=1)

51. 0.002 0.002 ↓ 0.0 0 1

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

  • Filter: ((dbr_rider_id IS NOT NULL) AND (now() >= start_date) AND (now() <= end_date))
52. 2,462.999 2,462.999 ↑ 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.022..2,462.999 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,615.727 2,615.727 ↓ 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,615.727..2,615.727 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.013 0.020 ↑ 1.1 45 1

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

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

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

56. 97.776 280.779 ↓ 1.1 269,991 1

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

  • Sort Key: pws.policy_id
  • Sort Method: quicksort Memory: 17558kB
57. 183.003 183.003 ↑ 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.022..183.003 rows=243,482 loops=1)

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

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

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

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

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

Append (cost=0.00..581,539.76 rows=275,019 width=16) (actual time=0.017..2,538.601 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. 323.535 323.535 ↑ 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.015..323.535 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,198.491 2,198.491 ↓ 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,198.491..2,198.491 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.009 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.014 0.014 ↑ 1.1 45 1

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