explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gkiP

Settings
# exclusive inclusive rows x rows loops node
1. 200.759 9,445.815 ↓ 2.3 356,351 1

GroupAggregate (cost=478,027.49..1,019,598.91 rows=151,823 width=24) (actual time=2,972.243..9,445.815 rows=356,351 loops=1)

  • Group Key: imih.id
2.          

CTE instance_ids

3. 228.629 1,271.909 ↑ 1.8 1,286,969 1

Merge Join (cost=0.84..154,050.12 rows=2,270,893 width=16) (actual time=0.068..1,271.909 rows=1,286,969 loops=1)

  • Merge Cond: (vii.id = imi."itemInstanceId")
4. 60.636 60.636 ↑ 1.0 91,512 1

Index Only Scan using bla on "VisibleItemInstances" vii (cost=0.42..4,773.10 rows=91,512 width=16) (actual time=0.009..60.636 rows=91,512 loops=1)

  • Heap Fetches: 91512
5. 137.656 982.644 ↑ 1.0 1,505,541 1

Materialize (cost=0.43..118,748.70 rows=1,505,541 width=32) (actual time=0.057..982.644 rows=1,505,541 loops=1)

6. 844.988 844.988 ↑ 1.0 1,505,541 1

Index Scan using "ItemModuleInstance_itemInstanceId_ix" on "ItemModuleInstance" imi (cost=0.43..114,984.85 rows=1,505,541 width=32) (actual time=0.055..844.988 rows=1,505,541 loops=1)

7. 384.011 9,245.056 ↑ 16.1 1,229,439 1

Merge Join (cost=323,977.37..765,210.45 rows=19,764,022 width=24) (actual time=2,972.205..9,245.056 rows=1,229,439 loops=1)

  • Merge Cond: (imih.id = instance_ids.id)
8. 5,077.513 5,077.513 ↑ 1.0 1,321,345 1

Index Only Scan using id_modified on "ItemModuleInstanceHistory" imih (cost=0.43..135,792.58 rows=1,321,345 width=24) (actual time=0.053..5,077.513 rows=1,321,345 loops=1)

  • Heap Fetches: 1321345
9. 134.230 3,783.532 ↑ 1.1 2,160,046 1

Materialize (cost=323,976.94..335,331.40 rows=2,270,893 width=16) (actual time=2,972.115..3,783.532 rows=2,160,046 loops=1)

10. 2,056.523 3,649.302 ↑ 1.8 1,286,958 1

Sort (cost=323,976.94..329,654.17 rows=2,270,893 width=16) (actual time=2,972.108..3,649.302 rows=1,286,958 loops=1)

  • Sort Key: instance_ids.id
  • Sort Method: external merge Disk: 32640kB
11. 1,592.779 1,592.779 ↑ 1.8 1,286,969 1

CTE Scan on instance_ids (cost=0.00..45,417.86 rows=2,270,893 width=16) (actual time=0.069..1,592.779 rows=1,286,969 loops=1)

Planning time : 0.326 ms
Execution time : 9,469.775 ms