explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DaAQ

Settings
# exclusive inclusive rows x rows loops node
1. 667.981 25,452.636 ↓ 1.7 1,286,969 1

Hash Right Join (cost=164,650.81..178,888.03 rows=752,770 width=67) (actual time=24,196.465..25,452.636 rows=1,286,969 loops=1)

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

CTE instance_ids

3. 427.473 1,670.675 ↓ 1.7 1,286,969 1

Nested Loop (cost=1,643.46..19,794.94 rows=752,770 width=16) (actual time=24.456..1,670.675 rows=1,286,969 loops=1)

4. 48.459 53.546 ↓ 457.6 91,512 1

HashAggregate (cost=1,638.90..1,640.90 rows=200 width=16) (actual time=24.375..53.546 rows=91,512 loops=1)

  • Group Key: "VisibleItemInstances".id
5. 5.087 5.087 ↑ 1.0 91,512 1

Seq Scan on "VisibleItemInstances" (cost=0.00..1,410.12 rows=91,512 width=16) (actual time=0.008..5.087 rows=91,512 loops=1)

6. 823.608 1,189.656 ↑ 1.8 14 91,512

Bitmap Heap Scan on "ItemModuleInstance" (cost=4.56..90.52 rows=25 width=32) (actual time=0.009..0.013 rows=14 loops=91,512)

  • Recheck Cond: ("itemInstanceId" = "VisibleItemInstances".id)
  • Heap Blocks: exact=284367
7. 366.048 366.048 ↑ 1.8 14 91,512

Bitmap Index Scan on "ItemModuleInstance_itemInstanceId_ix" (cost=0.00..4.55 rows=25 width=0) (actual time=0.004..0.004 rows=14 loops=91,512)

  • Index Cond: ("itemInstanceId" = "VisibleItemInstances".id)
8.          

CTE last_changes

9. 140.264 13,605.011 ↓ 2.3 356,351 1

GroupAggregate (cost=102,266.73..108,744.92 rows=151,938 width=24) (actual time=13,102.001..13,605.011 rows=356,351 loops=1)

  • Group Key: "ItemModuleInstanceHistory".id
10. 2,095.270 13,464.747 ↓ 1.9 1,229,439 1

Sort (cost=102,266.73..103,919.67 rows=661,174 width=24) (actual time=13,101.995..13,464.747 rows=1,229,439 loops=1)

  • Sort Key: "ItemModuleInstanceHistory".id
  • Sort Method: external merge Disk: 40784kB
11. 0.000 11,369.477 ↓ 1.9 1,229,439 1

Nested Loop (cost=16,937.75..24,786.32 rows=661,174 width=24) (actual time=603.242..11,369.477 rows=1,229,439 loops=1)

12. 984.188 1,093.245 ↓ 6,434.8 1,286,969 1

HashAggregate (cost=16,937.33..16,939.33 rows=200 width=16) (actual time=603.164..1,093.245 rows=1,286,969 loops=1)

  • Group Key: instance_ids_1.id
13. 109.057 109.057 ↓ 1.7 1,286,969 1

CTE Scan on instance_ids instance_ids_1 (cost=0.00..15,055.40 rows=752,770 width=16) (actual time=0.017..109.057 rows=1,286,969 loops=1)

14. 10,295.752 10,295.752 ↑ 9.0 1 1,286,969

Index Only Scan using "ItemModuleInstanceHistory_pk" on "ItemModuleInstanceHistory" (cost=0.43..39.14 rows=9 width=24) (actual time=0.004..0.008 rows=1 loops=1,286,969)

  • Index Cond: (id = instance_ids_1.id)
  • Heap Fetches: 1229439
15. 13,692.069 13,692.069 ↓ 2.3 356,351 1

CTE Scan on last_changes li (cost=0.00..3,038.76 rows=151,938 width=24) (actual time=13,102.003..13,692.069 rows=356,351 loops=1)

16. 375.184 11,092.586 ↓ 1.7 1,286,969 1

Hash (cost=18,614.33..18,614.33 rows=752,770 width=59) (actual time=11,092.586..11,092.586 rows=1,286,969 loops=1)

  • Buckets: 65536 (originally 65536) Batches: 64 (originally 32) Memory Usage: 4070kB
17. 38.698 10,717.402 ↓ 1.7 1,286,969 1

Nested Loop (cost=16,937.75..18,614.33 rows=752,770 width=59) (actual time=2,477.384..10,717.402 rows=1,286,969 loops=1)

18. 1,040.956 2,956.890 ↓ 6,434.8 1,286,969 1

HashAggregate (cost=16,937.33..16,939.33 rows=200 width=16) (actual time=2,477.349..2,956.890 rows=1,286,969 loops=1)

  • Group Key: instance_ids.id
19. 1,915.934 1,915.934 ↓ 1.7 1,286,969 1

CTE Scan on instance_ids (cost=0.00..15,055.40 rows=752,770 width=16) (actual time=24.458..1,915.934 rows=1,286,969 loops=1)

20. 7,721.814 7,721.814 ↑ 1.0 1 1,286,969

Index Scan using "ItemModuleInstance_pk" on "ItemModuleInstance" imi (cost=0.43..8.37 rows=1 width=59) (actual time=0.006..0.006 rows=1 loops=1,286,969)

  • Index Cond: (id = instance_ids.id)
Planning time : 0.464 ms
Execution time : 25,524.536 ms