explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7goH : Optimization for: plan #PytZ

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 1,085.647 78,611.485 ↑ 7.4 3,832,432 1

Merge Left Join (cost=2,745,707.39..6,744,579.09 rows=28,218,365 width=1,209) (actual time=8,129.969..78,611.485 rows=3,832,432 loops=1)

  • Merge Cond: (s.id = t.master_id)
2. 2,453.036 69,562.898 ↑ 2.7 3,832,404 1

Merge Left Join (cost=1,001.60..3,537,438.92 rows=10,383,232 width=1,085) (actual time=36.441..69,562.898 rows=3,832,404 loops=1)

  • Merge Cond: (s.id = tt.slave_id)
3. 23,055.900 23,055.900 ↓ 1.0 3,832,125 1

Index Scan using objects_part016_id_object_type_id_idx on objects_part016 s (cost=0.56..1,235,578.69 rows=3,820,615 width=961) (actual time=0.757..23,055.900 rows=3,832,125 loops=1)

  • Index Cond: (object_type_id = 682)
4. 648.474 44,053.962 ↑ 2.5 4,175,448 1

Materialize (cost=1,001.05..3,456,823.39 rows=10,383,232 width=124) (actual time=8.184..44,053.962 rows=4,175,448 loops=1)

5. 0.000 43,405.488 ↑ 2.5 4,175,448 1

Gather Merge (cost=1,001.05..3,430,865.31 rows=10,383,232 width=124) (actual time=8.178..43,405.488 rows=4,175,448 loops=1)

  • Workers Planned: 4
  • Workers Launched: 2
6. 150.456 43,890.278 ↑ 1.9 1,391,816 3 / 3

Nested Loop Semi Join (cost=0.99..2,193,122.22 rows=2,595,808 width=124) (actual time=1.283..43,890.278 rows=1,391,816 loops=3)

7. 2,210.686 2,210.686 ↓ 1.3 3,460,761 3 / 3

Parallel Index Scan using c_obj_object_slave_id_idx on c_obj_object tt (cost=0.43..566,759.54 rows=2,595,808 width=124) (actual time=0.024..2,210.686 rows=3,460,761 loops=3)

8. 41,529.136 41,529.136 ↓ 0.0 0 10,382,284 / 3

Index Only Scan using objects_part025_id_object_type_id_idx on objects_part025 objects_part025_1 (cost=0.56..0.62 rows=1 width=8) (actual time=0.012..0.012 rows=0 loops=10,382,284)

  • Index Cond: ((id = tt.master_id) AND (object_type_id = 681))
  • Heap Fetches: 9,025
9. 216.187 7,962.940 ↑ 4.7 2,197,864 1

Materialize (cost=2,744,705.78..2,796,621.94 rows=10,383,232 width=124) (actual time=7,466.893..7,962.940 rows=2,197,864 loops=1)

10. 938.700 7,746.753 ↑ 4.7 2,197,863 1

Sort (cost=2,744,705.78..2,770,663.86 rows=10,383,232 width=124) (actual time=7,466.885..7,746.753 rows=2,197,863 loops=1)

  • Sort Key: t.master_id
  • Sort Method: external merge Disk: 112,712kB
11. 5,317.984 6,808.053 ↑ 4.7 2,197,863 1

Hash Semi Join (cost=109,947.99..860,353.79 rows=10,383,232 width=124) (actual time=798.615..6,808.053 rows=2,197,863 loops=1)

  • Hash Cond: (t.slave_id = objects_part025.id)
12. 696.753 696.753 ↑ 1.0 10,382,284 1

Seq Scan on c_obj_object t (cost=0.00..201,187.32 rows=10,383,232 width=124) (actual time=0.006..696.753 rows=10,382,284 loops=1)

13. 321.463 793.316 ↑ 1.0 2,087,053 1

Hash (cost=75,701.90..75,701.90 rows=2,087,367 width=8) (actual time=793.315..793.316 rows=2,087,053 loops=1)

  • Buckets: 1,048,576 Batches: 4 Memory Usage: 28,592kB
14. 471.853 471.853 ↑ 1.0 2,087,053 1

Seq Scan on objects_part025 (cost=0.00..75,701.90 rows=2,087,367 width=8) (actual time=0.066..471.853 rows=2,087,053 loops=1)

  • Filter: (object_type_id = 681)
  • Rows Removed by Filter: 14,922
Planning time : 2.391 ms
Execution time : 78,863.949 ms