explain.depesz.com

PostgreSQL's explain analyze made readable

Result: M2PT : ConduitDisbursement without refund_id_partial index

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 105.564 ↓ 0.0 0 1

GroupAggregate (cost=0.42..2,103,276.92 rows=200 width=76) (actual time=105.564..105.564 rows=0 loops=1)

  • Group Key: "*SELECT* 1".entity_id
2. 0.003 105.562 ↓ 0.0 0 1

Append (cost=0.42..2,103,047.13 rows=5,062 width=40) (actual time=105.562..105.562 rows=0 loops=1)

3. 0.002 0.010 ↓ 0.0 0 1

Subquery Scan on *SELECT* 1 (cost=0.42..6,995.98 rows=1,973 width=40) (actual time=0.010..0.010 rows=0 loops=1)

4. 0.008 0.008 ↓ 0.0 0 1

Index Scan using index_extended_lineitems_on_undisbursed on extended_lineitems lineitems (cost=0.42..6,976.25 rows=1,973 width=48) (actual time=0.008..0.008 rows=0 loops=1)

  • Index Cond: (entity_id = 77516)
5. 0.001 105.509 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=5,154.65..2,096,045.84 rows=3,088 width=40) (actual time=105.509..105.509 rows=0 loops=1)

6. 12.284 105.508 ↓ 0.0 0 1

Hash Join (cost=5,154.65..2,096,014.96 rows=3,088 width=48) (actual time=105.508..105.508 rows=0 loops=1)

  • Hash Cond: (lineitems_1.entity_id = entities.id)
  • Join Filter: (COALESCE(entities.beneficiary_id, lineitems_1.entity_id) = 77516)
  • Rows Removed by Join Filter: 20319
7. 29.874 33.575 ↑ 30.4 20,319 1

Bitmap Heap Scan on extended_lineitems lineitems_1 (cost=488.30..2,081,295.85 rows=617,682 width=12) (actual time=5.249..33.575 rows=20,319 loops=1)

  • Recheck Cond: ((disbursement_id IS NOT NULL) AND (refund_id IS NOT NULL) AND (recovery_id IS NULL))
  • Filter: ((payment_id IS NOT NULL) AND (paid_to_conduit_id IS NOT NULL))
  • Heap Blocks: exact=10319
8. 3.701 3.701 ↑ 43.3 20,319 1

Bitmap Index Scan on index_extended_lineitems_on_unrecovered (cost=0.00..333.88 rows=879,826 width=0) (actual time=3.701..3.701 rows=20,319 loops=1)

9. 23.663 59.649 ↑ 1.0 46,993 1

Hash (cost=4,078.93..4,078.93 rows=46,993 width=8) (actual time=59.649..59.649 rows=46,993 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2167kB
10. 35.986 35.986 ↑ 1.0 46,993 1

Seq Scan on entities (cost=0.00..4,078.93 rows=46,993 width=8) (actual time=0.007..35.986 rows=46,993 loops=1)

11. 0.040 0.040 ↓ 0.0 0 1

Seq Scan on entity_debts (cost=0.00..5.30 rows=1 width=40) (actual time=0.040..0.040 rows=0 loops=1)

  • Filter: (entity_id = 77516)
  • Rows Removed by Filter: 264