explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SGR2

Settings
# exclusive inclusive rows x rows loops node
1. 45.734 12,417.720 ↑ 915.5 575 1

Gather (cost=36,342.10..404,719.26 rows=526,419 width=16) (actual time=4,398.179..12,417.720 rows=575 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
2. 57.460 12,371.986 ↑ 1,142.4 192 3

Hash Left Join (cost=35,342.10..351,077.36 rows=219,341 width=16) (actual time=4,394.356..12,371.986 rows=192 loops=3)

  • Hash Cond: (b.observed_item_id_mapping_id = c.id)
  • Filter: ((c.* IS NULL) OR (yo.composite_key_id <> c.composite_key_id))
  • Rows Removed by Filter: 26478
3. 345.675 8,897.043 ↑ 8.2 26,670 3

Nested Loop Left Join (cost=334.56..315,494.05 rows=219,341 width=16) (actual time=27.287..8,897.043 rows=26,670 loops=3)

4. 4,479.112 8,551.360 ↑ 8.2 26,670 3

Hash Join (cost=334.00..171,518.57 rows=219,341 width=16) (actual time=27.231..8,551.360 rows=26,670 loops=3)

  • Hash Cond: ((a.alias = yo.key) AND (a.value = yo.val))
5. 4,045.587 4,045.587 ↑ 1.2 3,376,419 3

Parallel Seq Scan on syn_observation_observed_item_id_ref a (cost=0.00..137,337.23 rows=4,220,523 width=35) (actual time=0.009..4,045.587 rows=3,376,419 loops=3)

6. 13.872 26.661 ↑ 1.0 10,000 3

Hash (cost=184.00..184.00 rows=10,000 width=35) (actual time=26.659..26.661 rows=10,000 loops=3)

  • Buckets: 16384 Batches: 1 Memory Usage: 784kB
7. 12.789 12.789 ↑ 1.0 10,000 3

Seq Scan on composite_key_components yo (cost=0.00..184.00 rows=10,000 width=35) (actual time=0.021..12.789 rows=10,000 loops=3)

8. 0.008 0.008 ↑ 1.0 1 80,010

Index Only Scan using nk_mapping_syn_observation_unq_idx on nk_mapping_syn_observation b (cost=0.56..0.65 rows=1 width=16) (actual time=0.007..0.008 rows=1 loops=80,010)

  • Index Cond: (composite_key_id = a.composite_key_id)
  • Heap Fetches: 26731
9. 1,737.138 3,417.483 ↑ 1.0 1,212,513 3

Hash (cost=19,851.13..19,851.13 rows=1,212,513 width=72) (actual time=3,417.482..3,417.483 rows=1,212,513 loops=3)

  • Buckets: 2097152 Batches: 1 Memory Usage: 126505kB
10. 1,680.345 1,680.345 ↑ 1.0 1,212,513 3

Seq Scan on nk_mapping_syn_compound c (cost=0.00..19,851.13 rows=1,212,513 width=72) (actual time=0.045..1,680.345 rows=1,212,513 loops=3)

Planning time : 0.705 ms
Execution time : 12,425.207 ms