explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2oUh

Settings
# exclusive inclusive rows x rows loops node
1. 993.829 13,238.015 ↑ 1.8 1,286,969 1

Hash Join (cost=1,146,677.65..1,265,980.90 rows=2,270,893 width=67) (actual time=12,120.696..13,238.015 rows=1,286,969 loops=1)

  • Hash Cond: (instance_ids.id = imi.id)
2.          

CTE instance_ids

3. 228.235 1,331.918 ↑ 1.8 1,286,969 1

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

  • Merge Cond: (vii.id = imi_1."itemInstanceId")
4. 60.319 60.319 ↑ 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.030..60.319 rows=91,512 loops=1)

  • Heap Fetches: 91512
5. 150.035 1,043.364 ↑ 1.0 1,505,541 1

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

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

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

7.          

CTE last_changes

8. 226.643 10,105.513 ↓ 2.3 356,351 1

GroupAggregate (cost=323,977.37..865,548.79 rows=151,823 width=24) (actual time=3,054.530..10,105.513 rows=356,351 loops=1)

  • Group Key: imih.id
9. 439.427 9,878.870 ↑ 16.1 1,229,439 1

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

  • Merge Cond: (imih.id = instance_ids_1.id)
10. 5,491.570 5,491.570 ↑ 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.044..5,491.570 rows=1,321,345 loops=1)

  • Heap Fetches: 1321345
11. 152.436 3,947.873 ↑ 1.1 2,160,046 1

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

12. 2,142.521 3,795.437 ↑ 1.8 1,286,958 1

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

  • Sort Key: instance_ids_1.id
  • Sort Method: external merge Disk: 32640kB
13. 1,652.916 1,652.916 ↑ 1.8 1,286,969 1

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

14. 134.778 134.778 ↑ 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.149..134.778 rows=1,286,969 loops=1)

15. 397.632 12,109.408 ↑ 1.0 1,505,541 1

Hash (cost=90,615.48..90,615.48 rows=1,505,541 width=67) (actual time=12,109.408..12,109.408 rows=1,505,541 loops=1)

  • Buckets: 65536 Batches: 64 Memory Usage: 2671kB
16. 855.076 11,711.776 ↑ 1.0 1,505,541 1

Hash Right Join (cost=67,348.67..90,615.48 rows=1,505,541 width=67) (actual time=3,681.131..11,711.776 rows=1,505,541 loops=1)

  • Hash Cond: (li.id = imi.id)
17. 10,242.010 10,242.010 ↓ 2.3 356,351 1

CTE Scan on last_changes li (cost=0.00..3,036.46 rows=151,823 width=24) (actual time=3,054.532..10,242.010 rows=356,351 loops=1)

18. 396.465 614.690 ↑ 1.0 1,505,541 1

Hash (cost=32,356.41..32,356.41 rows=1,505,541 width=59) (actual time=614.690..614.690 rows=1,505,541 loops=1)

  • Buckets: 65536 Batches: 64 Memory Usage: 2600kB
19. 218.225 218.225 ↑ 1.0 1,505,541 1

Seq Scan on "ItemModuleInstance" imi (cost=0.00..32,356.41 rows=1,505,541 width=59) (actual time=0.027..218.225 rows=1,505,541 loops=1)

Planning time : 0.393 ms
Execution time : 13,291.727 ms