explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ix7c

Settings
# exclusive inclusive rows x rows loops node
1. 1,894.593 16,408.596 ↓ 12,128.0 12,128 1

Nested Loop Left Join (cost=10,678.73..34,948.02 rows=1 width=85) (actual time=24.449..16,408.596 rows=12,128 loops=1)

  • Join Filter: ((coir.contract_id = cs.contract_id) AND (cr.import_invoice_row_id = imir.id))
  • Rows Removed by Join Filter: 27201810
  • Filter: (round((imir.item_sum_before_vat - COALESCE((sum(cr.monetary_amount)), '0'::numeric)), 3) <> '0'::numeric)
  • Rows Removed by Filter: 1097
2. 10.100 283.903 ↓ 13,225.0 13,225 1

Nested Loop (cost=1,030.99..24,549.19 rows=1 width=13) (actual time=9.032..283.903 rows=13,225 loops=1)

3. 55.827 247.331 ↓ 6,618.0 13,236 1

Nested Loop (cost=1,030.70..24,548.28 rows=2 width=17) (actual time=8.889..247.331 rows=13,236 loops=1)

  • Join Filter: (coir.item_id = i.division_item_id)
  • Rows Removed by Join Filter: 96071
4. 59.186 82.197 ↓ 1,477.1 109,307 1

Hash Join (cost=1,030.43..24,525.61 rows=74 width=25) (actual time=8.873..82.197 rows=109,307 loops=1)

  • Hash Cond: ((imir.property_id = p.id) AND (date_trunc('month'::text, imir.period_start) = date_trunc('month'::text, coir.valid_from)))
5. 14.228 15.521 ↓ 1.5 20,607 1

Bitmap Heap Scan on import_invoice_row imir (cost=232.06..23,589.79 rows=13,670 width=29) (actual time=1.359..15.521 rows=20,607 loops=1)

  • Recheck Cond: (((cost_owner_type)::text = 'CONTRACT_DIVISIBLE'::text) AND (NOT is_archived) AND ((status_type)::text = 'ACTIVE'::text))
  • Heap Blocks: exact=610
6. 1.293 1.293 ↓ 1.5 20,624 1

Bitmap Index Scan on imir_billable_divisible_row_idx (cost=0.00..228.64 rows=13,670 width=0) (actual time=1.293..1.293 rows=20,624 loops=1)

7. 1.061 7.490 ↑ 1.0 3,757 1

Hash (cost=742.02..742.02 rows=3,757 width=24) (actual time=7.490..7.490 rows=3,757 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 238kB
8. 1.196 6.429 ↑ 1.0 3,757 1

Hash Join (cost=163.18..742.02 rows=3,757 width=24) (actual time=1.407..6.429 rows=3,757 loops=1)

  • Hash Cond: (coir.property_id = p.id)
9. 3.856 3.856 ↑ 1.0 3,757 1

Seq Scan on contract_item_rate coir (cost=0.00..527.18 rows=3,757 width=20) (actual time=0.011..3.856 rows=3,757 loops=1)

  • Filter: (contract_id IS NOT NULL)
  • Rows Removed by Filter: 461
10. 0.412 1.377 ↓ 1.0 3,173 1

Hash (cost=123.68..123.68 rows=3,160 width=4) (actual time=1.377..1.377 rows=3,173 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 144kB
11. 0.965 0.965 ↓ 1.0 3,173 1

Index Only Scan using property_pkey on property p (cost=0.28..123.68 rows=3,160 width=4) (actual time=0.015..0.965 rows=3,173 loops=1)

  • Heap Fetches: 1111
12. 109.307 109.307 ↑ 1.0 1 109,307

Index Scan using item_pkey on item i (cost=0.27..0.29 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=109,307)

  • Index Cond: (id = imir.item_id)
13. 26.472 26.472 ↑ 1.0 1 13,236

Index Scan using import_invoice_pkey on import_invoice imin (cost=0.29..0.45 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=13,236)

  • Index Cond: (id = imir.import_invoice_id)
  • Filter: ((status_type)::text = 'APPROVED'::text)
  • Rows Removed by Filter: 0
14. 12,960.500 14,230.100 ↑ 6.1 2,057 13,225

GroupAggregate (cost=9,647.75..9,991.99 rows=12,518 width=20) (actual time=0.002..1.076 rows=2,057 loops=13,225)

  • Group Key: cs.contract_id, cr.import_invoice_row_id
15. 1,256.268 1,269.600 ↑ 6.0 2,087 13,225

Sort (cost=9,647.75..9,679.04 rows=12,518 width=20) (actual time=0.001..0.096 rows=2,087 loops=13,225)

  • Sort Key: cs.contract_id, cr.import_invoice_row_id
  • Sort Method: quicksort Memory: 219kB
16. 0.637 13.332 ↑ 6.0 2,087 1

Hash Join (cost=991.85..8,795.79 rows=12,518 width=20) (actual time=8.998..13.332 rows=2,087 loops=1)

  • Hash Cond: (cr.cost_sheet_id = cs.id)
17. 3.817 4.627 ↑ 6.0 2,088 1

Bitmap Heap Scan on cost_row cr (cost=220.59..7,852.36 rows=12,531 width=20) (actual time=0.841..4.627 rows=2,088 loops=1)

  • Recheck Cond: ((NOT is_archived) AND ((status_type)::text <> 'DELETED'::text))
  • Filter: (import_invoice_row_id IS NOT NULL)
  • Rows Removed by Filter: 10948
  • Heap Blocks: exact=324
18. 0.810 0.810 ↑ 1.1 13,036 1

Bitmap Index Scan on coro_archived_idx (cost=0.00..217.46 rows=13,733 width=0) (actual time=0.810..0.810 rows=13,036 loops=1)

19. 2.719 8.068 ↑ 1.0 20,487 1

Hash (cost=515.12..515.12 rows=20,491 width=8) (actual time=8.068..8.068 rows=20,487 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1057kB
20. 5.349 5.349 ↑ 1.0 20,487 1

Seq Scan on cost_sheet cs (cost=0.00..515.12 rows=20,491 width=8) (actual time=0.012..5.349 rows=20,487 loops=1)

  • Filter: (contract_id IS NOT NULL)
  • Rows Removed by Filter: 26
Planning time : 2.058 ms
Execution time : 16,409.884 ms