explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gSBd

Settings
# exclusive inclusive rows x rows loops node
1. 2,108.910 57,298.995 ↓ 13,225.0 13,225 1

Nested Loop Left Join (cost=11,982.49..36,221.72 rows=1 width=291) (actual time=77.793..57,298.995 rows=13,225 loops=1)

  • Join Filter: ((coir.contract_id = cs.contract_id) AND (cr.import_invoice_row_id = imir.id))
  • Rows Removed by Join Filter: 29450060
2. 17.600 398.910 ↓ 13,225.0 13,225 1

Nested Loop (cost=1,133.91..24,652.11 rows=1 width=227) (actual time=9.266..398.910 rows=13,225 loops=1)

3. 98.064 328.366 ↓ 6,618.0 13,236 1

Nested Loop (cost=1,133.62..24,651.20 rows=2 width=223) (actual time=9.147..328.366 rows=13,236 loops=1)

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

Hash Join (cost=1,133.35..24,628.53 rows=74 width=206) (actual time=9.129..120.995 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. 22.229 23.525 ↓ 1.5 20,607 1

Bitmap Heap Scan on import_invoice_row imir (cost=232.06..23,589.79 rows=13,670 width=166) (actual time=1.362..23.525 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.296 1.296 ↓ 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.296..1.296 rows=20,624 loops=1)

7. 1.142 7.728 ↑ 1.0 3,757 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 386kB
8. 1.248 6.586 ↑ 1.0 3,757 1

Hash Join (cost=266.10..844.94 rows=3,757 width=60) (actual time=2.304..6.586 rows=3,757 loops=1)

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

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

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

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

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

Seq Scan on property p (cost=0.00..226.60 rows=3,160 width=35) (actual time=0.010..1.750 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=29) (actual time=0.001..0.001 rows=1 loops=109,307)

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

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

  • Index Cond: (id = imir.import_invoice_id)
  • Filter: ((status_type)::text = 'APPROVED'::text)
  • Rows Removed by Filter: 0
14. 46,485.875 54,791.175 ↑ 6.2 2,227 13,225

GroupAggregate (cost=10,848.59..11,226.24 rows=13,733 width=20) (actual time=0.006..4.143 rows=2,227 loops=13,225)

  • Group Key: cs.contract_id, cr.import_invoice_row_id
15. 8,244.220 8,305.300 ↑ 1.1 13,035 13,225

Sort (cost=10,848.59..10,882.92 rows=13,733 width=20) (actual time=0.005..0.628 rows=13,035 loops=13,225)

  • Sort Key: cs.contract_id, cr.import_invoice_row_id
  • Sort Method: quicksort Memory: 1020kB
16. 3.422 61.080 ↑ 1.1 13,035 1

Hash Join (cost=771.52..9,904.76 rows=13,733 width=20) (actual time=7.211..61.080 rows=13,035 loops=1)

  • Hash Cond: (cr.cost_sheet_id = cs.id)
17. 50.507 50.507 ↑ 1.1 13,035 1

Seq Scan on cost_row cr (cost=0.00..8,944.41 rows=13,733 width=20) (actual time=0.012..50.507 rows=13,035 loops=1)

  • Filter: ((NOT is_archived) AND ((status_type)::text <> 'DELETED'::text))
  • Rows Removed by Filter: 114838
18. 2.678 7.151 ↑ 1.0 20,512 1

Hash (cost=515.12..515.12 rows=20,512 width=8) (actual time=7.151..7.151 rows=20,512 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1058kB
19. 4.473 4.473 ↑ 1.0 20,512 1

Seq Scan on cost_sheet cs (cost=0.00..515.12 rows=20,512 width=8) (actual time=0.009..4.473 rows=20,512 loops=1)

Planning time : 1.894 ms
Execution time : 57,301.476 ms