explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ft20

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 2,833.002 ↑ 12,394,929.7 6 1

GroupAggregate (cost=289,763,836.29..291,623,075.74 rows=74,369,578 width=126) (actual time=2,832.999..2,833.002 rows=6 loops=1)

  • Group Key: from_instance.name, rel.id, to_instance.name
2. 0.023 2,832.994 ↑ 7,436,957.8 10 1

Sort (cost=289,763,836.29..289,949,760.24 rows=74,369,578 width=137) (actual time=2,832.993..2,832.994 rows=10 loops=1)

  • Sort Key: from_instance.name, rel.id, to_instance.name
  • Sort Method: quicksort Memory: 25kB
3. 606.028 2,832.971 ↑ 7,436,957.8 10 1

Nested Loop Left Join (cost=1.27..264,026,520.86 rows=74,369,578 width=137) (actual time=347.116..2,832.971 rows=10 loops=1)

  • Join Filter: (SubPlan 1)
  • Rows Removed by Join Filter: 1932657
4. 0.008 0.109 ↑ 73.5 6 1

Nested Loop Left Join (cost=1.27..7,483.79 rows=441 width=613) (actual time=0.017..0.109 rows=6 loops=1)

5. 0.015 0.089 ↑ 73.5 6 1

Nested Loop Left Join (cost=0.84..3,905.75 rows=441 width=578) (actual time=0.014..0.089 rows=6 loops=1)

6. 0.044 0.044 ↑ 73.5 6 1

Index Scan using idx_boi_rel_boc_rel_type_id on boi_rel rel (cost=0.42..327.71 rows=441 width=543) (actual time=0.008..0.044 rows=6 loops=1)

  • Index Cond: (boc_rel_type_id = 176)
  • Filter: (NOT is_deleted)
  • Rows Removed by Filter: 23
7. 0.030 0.030 ↑ 1.0 1 6

Index Scan using bo_instance_pkey on bo_instance from_instance (cost=0.42..8.11 rows=1 width=51) (actual time=0.005..0.005 rows=1 loops=6)

  • Index Cond: (id = rel.from_bo_instance_id)
8. 0.012 0.012 ↑ 1.0 1 6

Index Scan using bo_instance_pkey on bo_instance to_instance (cost=0.42..8.11 rows=1 width=51) (actual time=0.002..0.002 rows=1 loops=6)

  • Index Cond: (id = rel.to_bo_instance_id)
9. 187.902 294.168 ↑ 1.0 322,111 6

Materialize (cost=0.00..45,908.15 rows=337,277 width=51) (actual time=0.005..49.028 rows=322,111 loops=6)

10. 106.266 106.266 ↑ 1.0 322,111 1

Seq Scan on bo_instance top (cost=0.00..40,927.77 rows=337,277 width=51) (actual time=0.005..106.266 rows=322,111 loops=1)

11.          

SubPlan (for Nested Loop Left Join)

12. 1,932.666 1,932.666 ↓ 0.0 0 1,932,666

Function Scan on jsonb_array_elements_text el (cost=0.00..1.75 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=1,932,666)

  • Filter: ((value)::bigint = top.id)
  • Rows Removed by Filter: 1
Planning time : 0.435 ms
Execution time : 2,835.658 ms