explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XPKW

Settings
# exclusive inclusive rows x rows loops node
1. 17,626.499 124,540.048 ↑ 1.4 1,402,469 1

Sort (cost=14,702,477.64..14,707,442.95 rows=1,986,124 width=1,150) (actual time=121,518.341..124,540.048 rows=1,402,469 loops=1)

  • Sort Key: pol.combined_policy_number
  • Sort Method: external merge Disk: 354616kB
2. 3,888.088 106,913.549 ↑ 1.4 1,402,469 1

Merge Left Join (cost=13,627,506.67..13,781,918.93 rows=1,986,124 width=1,150) (actual time=100,236.383..106,913.549 rows=1,402,469 loops=1)

  • Merge Cond: (pol.policy_id = pol_rid_3.policy_id)
3. 429.286 99,711.078 ↑ 1.4 1,402,469 1

Merge Left Join (cost=13,037,335.63..13,060,708.08 rows=1,986,124 width=732) (actual time=96,977.782..99,711.078 rows=1,402,469 loops=1)

  • Merge Cond: (pol.policy_id = pws.policy_id)
4. 802.203 98,852.435 ↑ 1.4 1,402,279 1

Merge Left Join (cost=12,979,052.18..12,990,349.06 rows=1,986,124 width=730) (actual time=96,594.504..98,852.435 rows=1,402,279 loops=1)

  • Merge Cond: (pol.policy_id = pol_rid.policy_id)
5. 4,056.167 93,292.553 ↑ 1.4 1,402,279 1

Sort (cost=12,391,557.98..12,396,523.29 rows=1,986,124 width=312) (actual time=91,864.919..93,292.553 rows=1,402,279 loops=1)

  • Sort Key: pol.policy_id
  • Sort Method: external merge Disk: 314552kB
6. 365.827 89,236.386 ↑ 1.4 1,402,279 1

Merge Left Join (cost=11,935,680.31..11,980,136.77 rows=1,986,124 width=312) (actual time=83,541.419..89,236.386 rows=1,402,279 loops=1)

  • Merge Cond: (p.party_id = cb.party_id)
7. 534.517 87,872.589 ↑ 1.4 1,376,213 1

Merge Left Join (cost=11,816,171.89..11,845,692.97 rows=1,986,124 width=316) (actual time=82,573.616..87,872.589 rows=1,376,213 loops=1)

  • Merge Cond: (p.party_id = ph.party_id)
8. 821.487 83,338.001 ↑ 1.4 1,376,213 1

Merge Left Join (cost=11,553,533.00..11,575,512.25 rows=1,986,124 width=305) (actual time=78,809.729..83,338.001 rows=1,376,213 loops=1)

  • Merge Cond: (p.party_id = ad_1.party_id)
9. 1,032.378 65,759.044 ↑ 1.4 1,372,384 1

Merge Left Join (cost=7,228,469.85..7,243,570.57 rows=1,986,124 width=235) (actual time=62,736.660..65,759.044 rows=1,372,384 loops=1)

  • Merge Cond: (p.party_id = ad.party_id)
10. 3,619.952 32,557.393 ↑ 1.4 1,372,224 1

Sort (cost=3,662,360.69..3,667,326.00 rows=1,986,124 width=165) (actual time=31,169.382..32,557.393 rows=1,372,224 loops=1)

  • Sort Key: p.party_id
  • Sort Method: external merge Disk: 217736kB
11. 382.920 28,937.441 ↑ 1.4 1,372,224 1

Hash Join (cost=2,550,654.77..3,338,221.98 rows=1,986,124 width=165) (actual time=22,016.990..28,937.441 rows=1,372,224 loops=1)

  • Hash Cond: (pol.product_id = pd.product_id)
12. 1,961.915 28,554.201 ↑ 1.4 1,451,491 1

Hash Join (cost=2,550,599.50..3,271,054.86 rows=2,007,504 width=151) (actual time=22,016.648..28,554.201 rows=1,451,491 loops=1)

  • Hash Cond: (pr.policy_id = pol.policy_id)
13. 2,266.105 11,033.890 ↑ 1.4 1,388,778 1

Hash Join (cost=632,330.89..1,191,156.22 rows=2,007,504 width=93) (actual time=6,456.249..11,033.890 rows=1,388,778 loops=1)

  • Hash Cond: (pr.party_id = p.party_id)
14. 2,340.628 3,123.738 ↑ 1.0 2,596,660 1

Bitmap Heap Scan on party_role pr (cost=71,468.39..480,337.97 rows=2,653,381 width=19) (actual time=810.142..3,123.738 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
15. 783.110 783.110 ↑ 1.0 4,628,620 1

Bitmap Index Scan on idx_party_role_role_type (cost=0.00..70,805.04 rows=4,747,755 width=0) (actual time=783.110..783.110 rows=4,628,620 loops=1)

  • Index Cond: ((role_type)::text = ANY ('{01,13}'::text[]))
16. 1,000.677 5,644.047 ↓ 1.0 3,144,740 1

Hash (cost=418,851.64..418,851.64 rows=3,075,688 width=82) (actual time=5,644.047..5,644.047 rows=3,144,740 loops=1)

  • Buckets: 524288 Batches: 16 Memory Usage: 22241kB
17. 4,643.370 4,643.370 ↓ 1.0 3,144,740 1

Seq Scan on party p (cost=0.00..418,851.64 rows=3,075,688 width=82) (actual time=0.638..4,643.370 rows=3,144,740 loops=1)

18. 521.049 15,558.396 ↑ 1.8 1,422,646 1

Hash (cost=1,804,905.18..1,804,905.18 rows=2,563,675 width=66) (actual time=15,558.396..15,558.396 rows=1,422,646 loops=1)

  • Buckets: 524288 Batches: 8 Memory Usage: 21722kB
19. 1,288.533 15,037.347 ↑ 1.8 1,422,646 1

Hash Right Join (cost=1,197,489.45..1,804,905.18 rows=2,563,675 width=66) (actual time=9,182.453..15,037.347 rows=1,422,646 loops=1)

  • Hash Cond: (agr.policy_id = pol.policy_id)
20. 434.749 4,685.770 ↑ 1.1 1,209,450 1

Hash Join (cost=83,391.38..604,363.29 rows=1,280,050 width=13) (actual time=117.048..4,685.770 rows=1,209,450 loops=1)

  • Hash Cond: (agr.agent_id = ag.agent_id)
21. 4,135.078 4,135.078 ↑ 1.1 1,209,450 1

Seq Scan on agent_role agr (cost=0.00..477,770.23 rows=1,280,050 width=16) (actual time=0.029..4,135.078 rows=1,209,450 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: 3026936
22. 31.349 115.943 ↑ 1.0 153,125 1

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

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

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

24. 490.712 9,063.044 ↑ 1.9 1,361,699 1

Hash (cost=1,003,238.63..1,003,238.63 rows=2,563,675 width=61) (actual time=9,063.044..9,063.044 rows=1,361,699 loops=1)

  • Buckets: 524288 Batches: 8 Memory Usage: 20100kB
25. 841.501 8,572.332 ↑ 1.9 1,361,699 1

Hash Right Join (cost=492,133.81..1,003,238.63 rows=2,563,675 width=61) (actual time=4,800.264..8,572.332 rows=1,361,699 loops=1)

  • Hash Cond: (agr_1.policy_id = pol.policy_id)
26. 31.643 3,217.633 ↑ 1.0 103,885 1

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

  • Hash Cond: (agr_1.agent_id = ag_1.agent_id)
27. 2,980.372 2,980.372 ↑ 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=77.812..2,980.372 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: 4132501
28. 38.064 205.618 ↑ 1.0 153,125 1

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

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

30. 693.564 4,513.198 ↑ 1.9 1,361,444 1

Hash (cost=300,387.00..300,387.00 rows=2,563,675 width=56) (actual time=4,513.198..4,513.198 rows=1,361,444 loops=1)

  • Buckets: 524288 Batches: 8 Memory Usage: 19976kB
31. 3,819.634 3,819.634 ↑ 1.9 1,361,444 1

Seq Scan on policy pol (cost=0.00..300,387.00 rows=2,563,675 width=56) (actual time=0.027..3,819.634 rows=1,361,444 loops=1)

  • Filter: ((now() >= start_date) AND (now() <= end_date))
  • Rows Removed by Filter: 6
32. 0.076 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
33. 0.244 0.244 ↑ 1.0 320 1

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

  • Filter: ((product_line_grouping)::text = ANY ('{"Indexed Annuity","Fixed Annuity",SPIA}'::text[]))
  • Rows Removed by Filter: 2
34. 4,165.683 32,169.273 ↓ 12.4 2,626,803 1

Sort (cost=3,566,109.16..3,566,639.48 rows=212,129 width=78) (actual time=31,567.264..32,169.273 rows=2,626,803 loops=1)

  • Sort Key: ad.party_id
  • Sort Method: external sort Disk: 158088kB
35. 17,414.367 28,003.590 ↓ 12.1 2,570,016 1

Bitmap Heap Scan on address ad (cost=639,993.00..3,547,341.49 rows=212,129 width=78) (actual time=10,689.213..28,003.590 rows=2,570,016 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=153409
36. 10,589.223 10,589.223 ↑ 1.0 2,732,628 1

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

  • Index Cond: (address_type = 4)
37. 5,124.457 16,757.470 ↓ 36.4 2,857,878 1

Sort (cost=4,325,063.14..4,325,259.39 rows=78,499 width=78) (actual time=16,073.060..16,757.470 rows=2,857,878 loops=1)

  • Sort Key: ad_1.party_id
  • Sort Method: external sort Disk: 170976kB
38. 8,898.605 11,633.013 ↓ 35.2 2,761,696 1

Bitmap Heap Scan on address ad_1 (cost=874,336.65..4,318,681.02 rows=78,499 width=78) (actual time=2,812.607..11,633.013 rows=2,761,696 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: 1064200
  • Heap Blocks: exact=172962
39. 0.001 2,734.408 ↓ 0.0 0 1

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

40. 248.602 248.602 ↑ 1.0 2,732,628 1

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

  • Index Cond: (address_type = 4)
41. 2,485.805 2,485.805 ↓ 1.1 1,094,062 1

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

  • Index Cond: (address_type = 1)
42. 1,489.091 4,000.071 ↓ 12.5 1,326,168 1

Sort (cost=262,638.89..262,903.17 rows=105,714 width=19) (actual time=3,763.873..4,000.071 rows=1,326,168 loops=1)

  • Sort Key: ph.party_id
  • Sort Method: external sort Disk: 46240kB
43. 2,510.980 2,510.980 ↓ 11.8 1,245,339 1

Seq Scan on phone ph (cost=0.00..253,817.16 rows=105,714 width=19) (actual time=2.827..2,510.980 rows=1,245,339 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: 996524
44. 106.913 997.970 ↑ 1.6 260,418 1

Sort (cost=119,508.21..120,542.83 rows=413,846 width=12) (actual time=967.794..997.970 rows=260,418 loops=1)

  • Sort Key: cb.party_id
  • Sort Method: quicksort Memory: 17341kB
45. 17.060 891.057 ↑ 1.7 238,864 1

Append (cost=0.00..80,899.00 rows=413,846 width=12) (actual time=0.023..891.057 rows=238,864 loops=1)

46. 0.045 0.045 ↑ 1.0 58 1

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

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

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

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

  • Filter: ((now() >= start_date) AND (now() <= end_date))
  • Rows Removed by Filter: 451
49. 77.521 4,757.679 ↓ 3.6 173,437 1

Sort (cost=587,494.20..587,615.15 rows=48,378 width=426) (actual time=4,729.567..4,757.679 rows=173,437 loops=1)

  • Sort Key: pol_rid.policy_id
  • Sort Method: quicksort Memory: 18950kB
50. 38.557 4,680.158 ↓ 3.1 152,364 1

Hash Join (cost=13.12..583,729.90 rows=48,378 width=426) (actual time=1.473..4,680.158 rows=152,364 loops=1)

  • Hash Cond: (pol_rid.dbr_rider_id = rid.rider_id)
51. 10.167 4,641.068 ↑ 1.3 152,364 1

Append (cost=0.00..581,539.76 rows=193,512 width=16) (actual time=0.914..4,641.068 rows=152,364 loops=1)

52. 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))
53. 1,434.830 1,434.830 ↑ 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.909..1,434.830 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
54. 3,196.068 3,196.068 ↓ 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=3,196.068..3,196.068 rows=0 loops=1)

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
56. 0.514 0.514 ↑ 1.1 45 1

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

57. 118.697 429.357 ↓ 1.1 269,988 1

Sort (cost=58,283.33..58,912.75 rows=251,770 width=10) (actual time=383.265..429.357 rows=269,988 loops=1)

  • Sort Key: pws.policy_id
  • Sort Method: quicksort Memory: 17558kB
58. 310.660 310.660 ↑ 1.0 243,479 1

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

  • Filter: ((now() >= start_date) AND (now() <= end_date))
  • Rows Removed by Filter: 154594
59. 144.119 3,314.383 ↓ 4.6 317,758 1

Sort (cost=590,171.04..590,342.93 rows=68,755 width=434) (actual time=3,258.486..3,314.383 rows=317,758 loops=1)

  • Sort Key: pol_rid_3.policy_id
  • Sort Method: quicksort Memory: 31570kB
60. 55.637 3,170.264 ↓ 3.9 269,038 1

Hash Join (cost=13.12..584,646.86 rows=68,755 width=434) (actual time=0.059..3,170.264 rows=269,038 loops=1)

  • Hash Cond: (pol_rid_3.ir_rider_id = rid_1.rider_id)
61. 17.929 3,114.606 ↑ 1.0 269,038 1

Append (cost=0.00..581,539.76 rows=275,019 width=16) (actual time=0.023..3,114.606 rows=269,038 loops=1)

62. 0.001 0.001 ↓ 0.0 0 1

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

  • Filter: ((ir_rider_id IS NOT NULL) AND (now() >= start_date) AND (now() <= end_date))
63. 338.837 338.837 ↑ 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..338.837 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
64. 2,757.839 2,757.839 ↓ 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,757.839..2,757.839 rows=0 loops=1)

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

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

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

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