explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mkm7

Settings
# exclusive inclusive rows x rows loops node
1. 7.440 320.769 ↓ 4,408.3 13,225 1

Hash Left Join (cost=11,438.85..34,992.81 rows=3 width=2,820) (actual time=33.654..320.769 rows=13,225 loops=1)

  • Hash Cond: ((coir.contract_id = cs.contract_id) AND (imir.id = cr.import_invoice_row_id))
2. 12.558 295.852 ↓ 4,408.3 13,225 1

Nested Loop (cost=1,133.91..24,678.45 rows=3 width=2,748) (actual time=16.064..295.852 rows=13,225 loops=1)

3. 41.454 270.058 ↓ 3,309.0 13,236 1

Nested Loop (cost=1,133.62..24,676.62 rows=4 width=2,376) (actual time=15.683..270.058 rows=13,236 loops=1)

  • Join Filter: (coir.item_id = i.division_item_id)
  • Rows Removed by Join Filter: 96071
4. 91.751 119.297 ↓ 696.2 109,307 1

Hash Join (cost=1,133.35..24,628.53 rows=157 width=1,207) (actual time=15.651..119.297 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. 12.039 13.488 ↓ 1.5 20,908 1

Bitmap Heap Scan on import_invoice_row imir (cost=232.06..23,589.79 rows=13,670 width=273) (actual time=1.530..13.488 rows=20,908 loops=1)

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

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

7. 4.182 14.058 ↑ 1.0 3,757 1

Hash (cost=844.94..844.94 rows=3,757 width=934) (actual time=14.058..14.058 rows=3,757 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 1661kB
8. 2.412 9.876 ↑ 1.0 3,757 1

Hash Join (cost=266.10..844.94 rows=3,757 width=934) (actual time=3.196..9.876 rows=3,757 loops=1)

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

Seq Scan on contract_item_rate coir (cost=0.00..527.18 rows=3,757 width=80) (actual time=0.013..4.317 rows=3,757 loops=1)

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

Hash (cost=226.60..226.60 rows=3,160 width=854) (actual time=3.147..3.147 rows=3,173 loops=1)

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

Seq Scan on property p (cost=0.00..226.60 rows=3,160 width=854) (actual time=0.012..0.992 rows=3,173 loops=1)

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=1,169) (actual time=0.001..0.001 rows=1 loops=109,307)

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

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

  • Index Cond: (id = imir.import_invoice_id)
  • Filter: ((status_type)::text = 'APPROVED'::text)
  • Rows Removed by Filter: 0
14. 0.536 17.477 ↑ 6.1 2,057 1

Hash (cost=10,117.17..10,117.17 rows=12,518 width=72) (actual time=17.477..17.477 rows=2,057 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 227kB
15. 1.058 16.941 ↑ 6.1 2,057 1

GroupAggregate (cost=9,647.75..9,991.99 rows=12,518 width=20) (actual time=15.796..16.941 rows=2,057 loops=1)

  • Group Key: cs.contract_id, cr.import_invoice_row_id
16. 1.214 15.883 ↑ 6.0 2,087 1

Sort (cost=9,647.75..9,679.04 rows=12,518 width=20) (actual time=15.775..15.883 rows=2,087 loops=1)

  • Sort Key: cs.contract_id, cr.import_invoice_row_id
  • Sort Method: quicksort Memory: 219kB
17. 0.726 14.669 ↑ 6.0 2,087 1

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

  • Hash Cond: (cr.cost_sheet_id = cs.id)
18. 4.070 5.026 ↑ 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=1.001..5.026 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: 11040
  • Heap Blocks: exact=324
19. 0.956 0.956 ↑ 1.0 13,128 1

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

20. 3.286 8.917 ↓ 1.0 20,501 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1057kB
21. 5.631 5.631 ↓ 1.0 20,501 1

Seq Scan on cost_sheet cs (cost=0.00..515.12 rows=20,491 width=8) (actual time=0.014..5.631 rows=20,501 loops=1)

  • Filter: (contract_id IS NOT NULL)
  • Rows Removed by Filter: 26
Planning time : 6.142 ms
Execution time : 322.191 ms