explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LWQU

Settings
# exclusive inclusive rows x rows loops node
1. 0.542 85,575.469 ↑ 1.0 1,000 1

Limit (cost=10,005,860,014.78..10,052,012,588.23 rows=1,000 width=63) (actual time=32,988.317..85,575.469 rows=1,000 loops=1)

2. 353.214 85,574.927 ↑ 10.2 1,000 1

Merge Right Join (cost=10,005,860,014.78..10,478,277,756.64 rows=10,236 width=63) (actual time=32,988.316..85,574.927 rows=1,000 loops=1)

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

Sort (cost=2,667,255.30..2,682,454.91 rows=6,079,845 width=23) (actual time=17,080.246..17,583.487 rows=4,706,635 loops=1)

  • Sort Key: rppder.payprocessid, rppder.edtcode, rppder.payamountsourcepid
  • Sort Method: external merge Disk: 218008kB
4. 8,355.449 8,728.754 ↑ 1.0 6,055,702 1

Bitmap Heap Scan on rpt_pay_process_detail rppder (cost=68,252.36..1,910,940.43 rows=6,079,845 width=23) (actual time=413.833..8,728.754 rows=6,055,702 loops=1)

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

Bitmap Index Scan on ix_rppd_compid (cost=0.00..66,732.40 rows=6,079,845 width=0) (actual time=373.305..373.305 rows=6,055,702 loops=1)

  • Index Cond: (companyid = 1596)
6. 2.217 67,638.226 ↑ 10.2 1,000 1

Materialize (cost=10,003,192,759.49..10,475,534,400.91 rows=10,236 width=69) (actual time=15,121.300..67,638.226 rows=1,000 loops=1)

7. 2.931 67,636.009 ↑ 10.2 1,000 1

Nested Loop Left Join (cost=10,003,192,759.49..10,475,534,375.32 rows=10,236 width=69) (actual time=15,121.294..67,636.009 rows=1,000 loops=1)

8. 443.983 15,170.078 ↑ 10.2 1,000 1

Merge Left Join (cost=3,192,759.06..3,284,084.69 rows=10,236 width=69) (actual time=15,068.652..15,170.078 rows=1,000 loops=1)

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

Sort (cost=525,503.76..525,529.35 rows=10,236 width=64) (actual time=2,889.867..2,890.715 rows=1,000 loops=1)

  • Sort Key: rppd.payprocessid, ((pbe.edtcodeee)::text), rppd.payamountsourcepid
  • Sort Method: quicksort Memory: 27855kB
10. 75.038 2,511.321 ↓ 15.1 154,383 1

Hash Join (cost=503,950.35..524,821.97 rows=10,236 width=64) (actual time=2,391.565..2,511.321 rows=154,383 loops=1)

  • Hash Cond: ((COALESCE(pbeh.activepid, rppd.payamountsourcepid)) = pbe.personbeneelectionpid)
11. 125.806 2,377.075 ↑ 1.1 154,473 1

HashAggregate (cost=441,905.43..443,550.52 rows=164,509 width=49) (actual time=2,332.266..2,377.075 rows=154,473 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. 51.945 2,251.269 ↓ 1.1 174,506 1

Hash Left Join (cost=287,479.86..437,792.70 rows=164,509 width=49) (actual time=2,033.311..2,251.269 rows=174,506 loops=1)

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

Bitmap Heap Scan on rpt_pay_process_detail rppd (cost=265,572.20..404,555.95 rows=106,126 width=27) (actual time=1,733.155..1,899.833 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.728 1,726.658 ↓ 0.0 0 1

BitmapAnd (cost=265,572.20..265,572.20 rows=140,125 width=0) (actual time=1,726.658..1,726.658 rows=0 loops=1)

15. 239.758 239.758 ↑ 1.0 6,055,702 1

Bitmap Index Scan on ix_rppd_compid (cost=0.00..66,732.40 rows=6,079,845 width=0) (actual time=239.758..239.758 rows=6,055,702 loops=1)

  • Index Cond: (companyid = 1596)
16. 411.885 411.885 ↓ 1.0 5,763,003 1

Bitmap Index Scan on ix_rppd_chkdt (cost=0.00..76,874.35 rows=5,703,978 width=0) (actual time=411.885..411.885 rows=5,763,003 loops=1)

  • Index Cond: ((checkdate >= (CURRENT_DATE - '3 mons'::interval)) AND (checkdate <= CURRENT_DATE))
17. 1,045.287 1,045.287 ↓ 1.0 11,136,352 1

Bitmap Index Scan on ix_rppd_payamtsrc (cost=0.00..121,885.35 rows=11,103,571 width=0) (actual time=1,045.287..1,045.287 rows=11,136,352 loops=1)

  • Index Cond: (payamountsource = 'DB'::text)
18. 150.298 299.491 ↑ 1.0 564,163 1

Hash (cost=14,855.63..14,855.63 rows=564,163 width=30) (actual time=299.491..299.491 rows=564,163 loops=1)

  • Buckets: 1048576 Batches: 1 Memory Usage: 45124kB
19. 149.193 149.193 ↑ 1.0 564,163 1

Seq Scan on dim_person_bene_election_history pbeh (cost=0.00..14,855.63 rows=564,163 width=30) (actual time=0.009..149.193 rows=564,163 loops=1)

20. 15.718 59.208 ↑ 1.0 107,418 1

Hash (cost=60,693.24..60,693.24 rows=108,135 width=23) (actual time=59.208..59.208 rows=107,418 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 6703kB
21. 43.490 43.490 ↑ 1.0 107,418 1

Index Scan using ix_dimpbe_compid on dim_person_bene_election pbe (cost=0.43..60,693.24 rows=108,135 width=23) (actual time=0.034..43.490 rows=107,418 loops=1)

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

Materialize (cost=2,667,255.30..2,697,654.52 rows=6,079,845 width=23) (actual time=10,998.528..11,835.380 rows=4,706,638 loops=1)

23. 9,013.937 11,507.777 ↑ 1.3 4,706,635 1

Sort (cost=2,667,255.30..2,682,454.91 rows=6,079,845 width=23) (actual time=10,998.524..11,507.777 rows=4,706,635 loops=1)

  • Sort Key: rppdee.payprocessid, rppdee.edtcode, rppdee.payamountsourcepid
  • Sort Method: external merge Disk: 218008kB
24. 2,245.031 2,493.840 ↑ 1.0 6,055,702 1

Bitmap Heap Scan on rpt_pay_process_detail rppdee (cost=68,252.36..1,910,940.43 rows=6,079,845 width=23) (actual time=288.659..2,493.840 rows=6,055,702 loops=1)

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

Bitmap Index Scan on ix_rppd_compid (cost=0.00..66,732.40 rows=6,079,845 width=0) (actual time=248.809..248.809 rows=6,055,702 loops=1)

  • Index Cond: (companyid = 1596)
26. 1.000 52,463.000 ↑ 1.0 1 1,000

Limit (cost=0.43..46,136.19 rows=1 width=4) (actual time=52.463..52.463 rows=1 loops=1,000)

27. 52,462.000 52,462.000 ↑ 1.0 1 1,000

Index Scan using ix_fbt_compid on fact_benefits_transact fbt (cost=0.43..46,136.19 rows=1 width=4) (actual time=52.462..52.462 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: 208495
Planning time : 10.440 ms