explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Riwh

Settings
# exclusive inclusive rows x rows loops node
1. 1.530 25,248.607 ↓ 1.8 359 1

GroupAggregate (cost=6,866.42..7,389.29 rows=200 width=202) (actual time=25,207.919..25,248.607 rows=359 loops=1)

  • Group Key: users.id
2. 2.626 25,247.077 ↑ 15.8 681 1

Merge Join (cost=6,866.42..7,062.96 rows=10,761 width=162) (actual time=25,207.892..25,247.077 rows=681 loops=1)

  • Merge Cond: ((users.id)::text = (prs.user_id)::text)
3. 38.135 38.135 ↓ 11.7 7,859 1

Foreign Scan on users (cost=100.00..133.47 rows=671 width=98) (actual time=1.675..38.135 rows=7,859 loops=1)

4. 1.326 25,206.316 ↑ 4.7 681 1

Sort (cost=6,766.42..6,774.44 rows=3,207 width=162) (actual time=25,206.196..25,206.316 rows=681 loops=1)

  • Sort Key: prs.user_id
  • Sort Method: quicksort Memory: 635kB
5. 0.635 25,204.990 ↑ 4.7 681 1

Hash Join (cost=5,372.71..6,579.66 rows=3,207 width=162) (actual time=24,764.156..25,204.990 rows=681 loops=1)

  • Hash Cond: ((prs_1.id)::text = (prs.pr_id)::text)
6. 5.522 443.046 ↓ 1.3 681 1

Nested Loop (cost=100.00..1,193.24 rows=531 width=130) (actual time=2.830..443.046 rows=681 loops=1)

7. 385.612 385.612 ↓ 48.9 25,956 1

Foreign Scan on pr prs_1 (cost=100.00..125.93 rows=531 width=130) (actual time=2.749..385.612 rows=25,956 loops=1)

8. 51.912 51.912 ↓ 0.0 0 25,956

Function Scan on jsonb_array_elements pr_payments (cost=0.01..2.00 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=25,956)

  • Filter: (((value #>> '{paymentEventType}'::text[]) ~~* '%refund%'::text) AND ((value #>> '{paymentEventType}'::text[]) !~~* '%error'::text))
  • Rows Removed by Filter: 1
9. 9.680 24,761.309 ↓ 21.5 25,956 1

Hash (cost=5,257.61..5,257.61 rows=1,208 width=228) (actual time=24,761.309..24,761.309 rows=25,956 loops=1)

  • Buckets: 32768 (originally 2048) Batches: 1 (originally 1) Memory Usage: 3114kB
10. 5.068 24,751.629 ↓ 21.5 25,956 1

Subquery Scan on prs (cost=4,269.06..5,257.61 rows=1,208 width=228) (actual time=23,721.576..24,751.629 rows=25,956 loops=1)

11. 40.292 24,746.561 ↓ 21.5 25,956 1

Nested Loop Left Join (cost=4,269.06..5,245.53 rows=1,208 width=2,761) (actual time=23,721.576..24,746.561 rows=25,956 loops=1)

12.          

CTE cte_prs_payments

13. 147.721 9,788.836 ↑ 2.0 22,448 1

Nested Loop (cost=100.00..2,983.20 rows=44,000 width=282) (actual time=38.953..9,788.836 rows=22,448 loops=1)

14. 9,589.203 9,589.203 ↓ 59.0 25,956 1

Foreign Scan on pr prs_3 (cost=100.00..123.20 rows=440 width=162) (actual time=38.604..9,589.203 rows=25,956 loops=1)

15. 51.912 51.912 ↑ 100.0 1 25,956

Function Scan on jsonb_array_elements pr_payments_1 (cost=0.01..1.00 rows=100 width=32) (actual time=0.001..0.002 rows=1 loops=25,956)

16.          

CTE cte_deduped_full_payment_events

17. 4.704 9,929.282 ↓ 161.4 21,633 1

Unique (cost=998.56..999.66 rows=134 width=282) (actual time=9,919.034..9,929.282 rows=21,633 loops=1)

18. 107.947 9,924.578 ↓ 98.3 21,633 1

Sort (cost=998.56..999.11 rows=220 width=282) (actual time=9,919.032..9,924.578 rows=21,633 loops=1)

  • Sort Key: cte_prs_payments.pr_id, cte_prs_payments.timestamp_utc
  • Sort Method: external merge Disk: 2888kB
19. 9,816.631 9,816.631 ↓ 98.3 21,633 1

CTE Scan on cte_prs_payments (cost=0.00..990.00 rows=220 width=282) (actual time=38.957..9,816.631 rows=21,633 loops=1)

  • Filter: (event_type = 'FULL_PAYMENT'::text)
  • Rows Removed by Filter: 815
20. 16.793 24,576.489 ↓ 43.0 25,956 1

Hash Left Join (cost=286.19..335.52 rows=604 width=228) (actual time=23,721.544..24,576.489 rows=25,956 loops=1)

  • Hash Cond: ((prs_2.id)::text = (pr_purchase.pr_id)::text)
21. 803.820 14,616.164 ↓ 43.0 25,956 1

Hash Right Join (cost=281.84..324.85 rows=604 width=228) (actual time=13,777.994..14,616.164 rows=25,956 loops=1)

  • Hash Cond: ((users_1.account_id)::text = (prs_2.account_id)::text)
22. 41.453 41.453 ↓ 21.2 7,868 1

Foreign Scan on users users_1 (cost=100.00..121.16 rows=372 width=196) (actual time=1.202..41.453 rows=7,868 loops=1)

23. 2,091.517 13,770.891 ↓ 79.9 25,956 1

Hash (cost=177.77..177.77 rows=325 width=228) (actual time=13,770.891..13,770.891 rows=25,956 loops=1)

  • Buckets: 1024 (originally 1024) Batches: 512 (originally 1) Memory Usage: 4100kB
24. 22.148 11,679.374 ↓ 79.9 25,956 1

Merge Left Join (cost=166.42..177.77 rows=325 width=228) (actual time=11,494.622..11,679.374 rows=25,956 loops=1)

  • Merge Cond: ((((prs_2.details #>> '{affiliation,agentReferrerCode}'::text[])) = _agent_referrer_mapping.agent_referrer_code) AND ((lower((prs_2.details #>> '{serviceCategory}'::text[]))) = _agent_referrer_mapping.service_category))
25. 1,764.653 11,654.686 ↓ 79.9 25,956 1

Sort (cost=133.31..134.12 rows=325 width=228) (actual time=11,494.581..11,654.686 rows=25,956 loops=1)

  • Sort Key: ((prs_2.details #>> '{affiliation,agentReferrerCode}'::text[])), (lower((prs_2.details #>> '{serviceCategory}'::text[])))
  • Sort Method: external merge Disk: 382496kB
26. 9,890.033 9,890.033 ↓ 79.9 25,956 1

Foreign Scan on pr prs_2 (cost=100.00..119.75 rows=325 width=228) (actual time=37.575..9,890.033 rows=25,956 loops=1)

27. 2.527 2.540 ↓ 60.4 25,961 1

Sort (cost=33.11..34.18 rows=430 width=64) (actual time=0.035..2.540 rows=25,961 loops=1)

  • Sort Key: _agent_referrer_mapping.agent_referrer_code, _agent_referrer_mapping.service_category
  • Sort Method: quicksort Memory: 25kB
28. 0.013 0.013 ↑ 35.8 12 1

Seq Scan on _agent_referrer_mapping (cost=0.00..14.30 rows=430 width=64) (actual time=0.011..0.013 rows=12 loops=1)

29. 4.361 9,943.532 ↓ 161.4 21,633 1

Hash (cost=2.68..2.68 rows=134 width=98) (actual time=9,943.532..9,943.532 rows=21,633 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1714kB
30. 9,939.171 9,939.171 ↓ 161.4 21,633 1

CTE Scan on cte_deduped_full_payment_events pr_purchase (cost=0.00..2.68 rows=134 width=98) (actual time=9,919.037..9,939.171 rows=21,633 loops=1)

31. 129.780 129.780 ↑ 2.0 1 25,956

Function Scan on jsonb_array_elements pr_pricing_items_gross_premium (cost=0.01..1.50 rows=2 width=32) (actual time=0.004..0.005 rows=1 loops=25,956)

  • Filter: ((value #>> '{amountType}'::text[]) = ANY ('{grossPremium,proratedPreTaxMonthlyPremium}'::text[]))
  • Rows Removed by Filter: 7
Planning time : 2.255 ms
Execution time : 25,334.897 ms