explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oosAi

Settings
# exclusive inclusive rows x rows loops node
1. 0.398 77,232.164 ↑ 1.0 1,000 1

Limit (cost=10,005,832,997.65..10,051,260,274.83 rows=1,000 width=63) (actual time=26,049.089..77,232.164 rows=1,000 loops=1)

2. 343.418 77,231.766 ↑ 10.2 1,000 1

Merge Right Join (cost=10,005,832,997.65..10,471,326,306.92 rows=10,247 width=63) (actual time=26,049.088..77,231.766 rows=1,000 loops=1)

  • Merge Cond: ((rppder.payprocessid = rppd.payprocessid) AND (rppder.edtcode = (pbe.edtcodeee)::text) AND (rppder.payamountsourcepid = rppd.payamountsourcepid))
3. 8,900.663 11,362.829 ↑ 1.3 4,706,635 1

Sort (cost=2,656,332.45..2,671,370.09 rows=6,015,058 width=23) (actual time=10,884.495..11,362.829 rows=4,706,635 loops=1)

  • Sort Key: rppder.payprocessid, rppder.edtcode, rppder.payamountsourcepid
  • Sort Method: external merge Disk: 218008kB
4. 2,231.212 2,462.166 ↓ 1.0 6,055,702 1

Bitmap Heap Scan on rpt_pay_process_detail rppder (cost=67,521.26..1,908,542.49 rows=6,015,058 width=23) (actual time=270.767..2,462.166 rows=6,055,702 loops=1)

  • Recheck Cond: (companyid = 1596)
  • Heap Blocks: exact=213784
5. 230.954 230.954 ↓ 1.0 6,055,702 1

Bitmap Index Scan on ix_rppd_compid (cost=0.00..66,017.50 rows=6,015,058 width=0) (actual time=230.954..230.954 rows=6,055,702 loops=1)

  • Index Cond: (companyid = 1596)
6. 1.653 65,525.519 ↑ 10.2 1,000 1

Materialize (cost=10,003,176,665.20..10,468,594,683.77 rows=10,247 width=69) (actual time=14,409.321..65,525.519 rows=1,000 loops=1)

7. 2.319 65,523.866 ↑ 10.2 1,000 1

Nested Loop Left Join (cost=10,003,176,665.20..10,468,594,658.15 rows=10,247 width=69) (actual time=14,409.316..65,523.866 rows=1,000 loops=1)

8. 444.640 14,466.547 ↑ 10.2 1,000 1

Merge Left Join (cost=3,176,664.77..3,267,018.74 rows=10,247 width=69) (actual time=14,365.119..14,466.547 rows=1,000 loops=1)

  • Merge Cond: ((rppd.payprocessid = rppdee.payprocessid) AND (((pbe.edtcodeee)::text) = rppdee.edtcode) AND (rppd.payamountsourcepid = rppdee.payamountsourcepid))
9. 442.246 2,787.104 ↑ 10.2 1,000 1

Sort (cost=520,332.32..520,357.94 rows=10,247 width=64) (actual time=2,786.353..2,787.104 rows=1,000 loops=1)

  • Sort Key: rppd.payprocessid, ((pbe.edtcodeee)::text), rppd.payamountsourcepid
  • Sort Method: quicksort Memory: 27847kB
10. 66.338 2,344.858 ↓ 15.1 154,327 1

Hash Join (cost=503,612.00..519,649.72 rows=10,247 width=64) (actual time=2,234.307..2,344.858 rows=154,327 loops=1)

  • Hash Cond: ((COALESCE(pbeh.activepid, rppd.payamountsourcepid)) = pbe.personbeneelectionpid)
11. 112.888 2,221.103 ↑ 1.1 154,417 1

HashAggregate (cost=441,566.59..443,222.20 rows=165,561 width=49) (actual time=2,176.796..2,221.103 rows=154,417 loops=1)

  • Group Key: rppd.payamountsourcepid, COALESCE(pbeh.activepid, rppd.payamountsourcepid), rppd.payamountsource, rppd.payprocessid, rppd.checkdate, rppd.payyear, rppd.payperiod, pbeh.coverageamount, pbeh.monthlyamount, pbeh.monthlyemployeramount
12. 46.322 2,108.215 ↓ 1.1 174,065 1

Hash Left Join (cost=288,158.76..437,427.56 rows=165,561 width=49) (actual time=1,923.810..2,108.215 rows=174,065 loops=1)

  • Hash Cond: (rppd.payamountsourcepid = pbeh.personbeneelectionpid)
13. 145.156 1,859.815 ↓ 1.6 168,026 1

Bitmap Heap Scan on rpt_pay_process_detail rppd (cost=266,302.61..405,932.73 rows=106,441 width=27) (actual time=1,721.059..1,859.815 rows=168,026 loops=1)

  • Recheck Cond: ((companyid = 1596) AND (checkdate >= (CURRENT_DATE - '3 mons'::interval)) AND (checkdate <= CURRENT_DATE) AND (payamountsource = 'DB'::text))
  • Filter: (amount <> '0'::numeric)
  • Rows Removed by Filter: 162509
  • Heap Blocks: exact=21184
14. 29.396 1,714.659 ↓ 0.0 0 1

BitmapAnd (cost=266,302.61..266,302.61 rows=140,804 width=0) (actual time=1,714.659..1,714.659 rows=0 loops=1)

15. 240.091 240.091 ↓ 1.0 6,055,702 1

Bitmap Index Scan on ix_rppd_compid (cost=0.00..66,017.50 rows=6,015,058 width=0) (actual time=240.091..240.091 rows=6,055,702 loops=1)

  • Index Cond: (companyid = 1596)
16. 415.576 415.576 ↓ 1.0 5,762,938 1

Bitmap Index Scan on ix_rppd_chkdt (cost=0.00..77,502.91 rows=5,750,834 width=0) (actual time=415.576..415.576 rows=5,762,938 loops=1)

  • Index Cond: ((checkdate >= (CURRENT_DATE - '3 mons'::interval)) AND (checkdate <= CURRENT_DATE))
17. 1,029.596 1,029.596 ↑ 1.0 11,130,888 1

Bitmap Index Scan on ix_rppd_payamtsrc (cost=0.00..122,701.87 rows=11,178,707 width=0) (actual time=1,029.596..1,029.596 rows=11,130,888 loops=1)

  • Index Cond: (payamountsource = 'DB'::text)
18. 109.077 202.078 ↑ 1.0 562,851 1

Hash (cost=14,820.51..14,820.51 rows=562,851 width=30) (actual time=202.078..202.078 rows=562,851 loops=1)

  • Buckets: 1048576 Batches: 1 Memory Usage: 45039kB
19. 93.001 93.001 ↑ 1.0 562,851 1

Seq Scan on dim_person_bene_election_history pbeh (cost=0.00..14,820.51 rows=562,851 width=30) (actual time=0.010..93.001 rows=562,851 loops=1)

20. 15.865 57.417 ↑ 1.0 107,387 1

Hash (cost=60,691.07..60,691.07 rows=108,348 width=23) (actual time=57.417..57.417 rows=107,387 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 6701kB
21. 41.552 41.552 ↑ 1.0 107,387 1

Index Scan using ix_dimpbe_compid on dim_person_bene_election pbe (cost=0.43..60,691.07 rows=108,348 width=23) (actual time=0.031..41.552 rows=107,387 loops=1)

  • Index Cond: (companyid = 1596)
22. 317.960 11,234.803 ↑ 1.3 4,706,638 1

Materialize (cost=2,656,332.45..2,686,407.74 rows=6,015,058 width=23) (actual time=10,418.682..11,234.803 rows=4,706,638 loops=1)

23. 8,606.516 10,916.843 ↑ 1.3 4,706,635 1

Sort (cost=2,656,332.45..2,671,370.09 rows=6,015,058 width=23) (actual time=10,418.676..10,916.843 rows=4,706,635 loops=1)

  • Sort Key: rppdee.payprocessid, rppdee.edtcode, rppdee.payamountsourcepid
  • Sort Method: external merge Disk: 218008kB
24. 2,064.647 2,310.327 ↓ 1.0 6,055,702 1

Bitmap Heap Scan on rpt_pay_process_detail rppdee (cost=67,521.26..1,908,542.49 rows=6,015,058 width=23) (actual time=285.372..2,310.327 rows=6,055,702 loops=1)

  • Recheck Cond: (companyid = 1596)
  • Heap Blocks: exact=213784
25. 245.680 245.680 ↓ 1.0 6,055,702 1

Bitmap Index Scan on ix_rppd_compid (cost=0.00..66,017.50 rows=6,015,058 width=0) (actual time=245.680..245.680 rows=6,055,702 loops=1)

  • Index Cond: (companyid = 1596)
26. 2.000 51,055.000 ↑ 1.0 1 1,000

Limit (cost=0.43..45,411.09 rows=1 width=4) (actual time=51.054..51.055 rows=1 loops=1,000)

27. 51,053.000 51,053.000 ↑ 1.0 1 1,000

Index Scan using ix_fbt_compid on fact_benefits_transact fbt (cost=0.43..45,411.09 rows=1 width=4) (actual time=51.053..51.053 rows=1 loops=1,000)

  • Index Cond: (companyid = 1596)
  • Filter: ((rppd.checkdate >= startdate) AND (rppd.checkdate <= enddate) AND (pbe.personbeneelectionkey = personbeneelectionkey))
  • Rows Removed by Filter: 208151
Planning time : 13.092 ms