explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2Z2m : B202003

Settings
# exclusive inclusive rows x rows loops node
1. 1,629.778 95,045.978 ↓ 160.6 972,518 1

Sort (cost=3,429,312.46..3,429,327.59 rows=6,054 width=65) (actual time=94,805.758..95,045.978 rows=972,518 loops=1)

  • Sort Key: x4.payment_account_id, x2.order_id, x2.order_item_id, x3.playround_code
  • Sort Method: external merge Disk: 78480kB
2. 95.197 93,416.200 ↓ 160.6 972,518 1

Nested Loop Anti Join (cost=676,712.09..3,428,932.15 rows=6,054 width=65) (actual time=39,108.170..93,416.200 rows=972,518 loops=1)

  • Join Filter: ((pending_billed_item_event.order_id = x2.order_id) AND (pending_billed_item_event.order_item_id = x2.order_item_id) AND ((pending_billed_item_event.playround_code)::text = (x3.playround_code)::text))
3. 594.761 61,227.909 ↓ 160.6 972,518 1

Nested Loop (cost=676,711.59..3,382,181.43 rows=6,054 width=65) (actual time=7,426.986..61,227.909 rows=972,518 loops=1)

4. 5,207.799 40,210.270 ↓ 160.6 972,518 1

Hash Join (cost=676,711.02..3,337,968.03 rows=6,055 width=61) (actual time=7,425.163..40,210.270 rows=972,518 loops=1)

  • Hash Cond: ((x2.order_id = x4.order_id) AND (x2.order_item_id = x4.order_item_id) AND ((SubPlan 2) = x4.version))
5. 0.000 461.532 ↓ 1.0 972,518 1

Gather (cost=19,265.29..2,157,878.23 rows=967,319 width=53) (actual time=89.557..461.532 rows=972,518 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 2,915.108 2,943.555 ↑ 1.2 324,173 3 / 3

Parallel Bitmap Heap Scan on debit_request x2 (cost=18,265.29..2,060,146.33 rows=403,050 width=53) (actual time=86.734..2,943.555 rows=324,173 loops=3)

  • Recheck Cond: ((debit_playround_code)::text = 'B202003'::text)
  • Heap Blocks: exact=1
7. 28.447 28.447 ↓ 1.0 972,518 1 / 3

Bitmap Index Scan on dere_ind3 (cost=0.00..18,023.46 rows=967,319 width=0) (actual time=85.342..85.342 rows=972,518 loops=1)

  • Index Cond: ((debit_playround_code)::text = 'B202003'::text)
8. 3,773.799 7,310.435 ↓ 1.0 13,749,246 1

Hash (cost=336,329.54..336,329.54 rows=13,746,754 width=24) (actual time=7,310.434..7,310.435 rows=13,749,246 loops=1)

  • Buckets: 65536 Batches: 256 Memory Usage: 3430kB
9. 3,536.636 3,536.636 ↓ 1.0 13,749,246 1

Seq Scan on billable_item x4 (cost=0.00..336,329.54 rows=13,746,754 width=24) (actual time=0.022..3,536.636 rows=13,749,246 loops=1)

10.          

SubPlan (for Hash Join)

11. 0.000 27,230.504 ↑ 1.0 1 1,945,036

Result (cost=8.58..8.59 rows=1 width=4) (actual time=0.014..0.014 rows=1 loops=1,945,036)

12.          

Initplan (for Result)

13. 1,945.036 27,230.504 ↑ 1.0 1 1,945,036

Limit (cost=0.56..8.58 rows=1 width=4) (actual time=0.013..0.014 rows=1 loops=1,945,036)

14. 25,285.468 25,285.468 ↑ 1.0 1 1,945,036

Index Only Scan Backward using biit_pkey on billable_item (cost=0.56..8.58 rows=1 width=4) (actual time=0.013..0.013 rows=1 loops=1,945,036)

  • Index Cond: ((order_id = x2.order_id) AND (order_item_id = x2.order_item_id) AND (version IS NOT NULL))
  • Heap Fetches: 16146
15. 20,422.878 20,422.878 ↑ 1.0 1 972,518

Index Scan using plfe_pkey on playround_fee_debit_request x3 (cost=0.57..7.29 rows=1 width=20) (actual time=0.020..0.021 rows=1 loops=972,518)

  • Index Cond: (debreq_event_id = x2.payment_event_id)
16. 32,093.094 32,093.094 ↓ 0.0 0 972,518

Materialize (cost=0.50..46,629.64 rows=1 width=20) (actual time=0.033..0.033 rows=0 loops=972,518)

  • -> Index Only Scan using pending_billed_item_event_pkey on pending_billed_item_event (cost=0.50..46629.64 rows=1 width=20) (actual time=31681.172..31681.172rows=0 loops=1)
  • Heap Fetches: 5801495
Planning time : 1.208 ms
Execution time : 95,165.723 ms