explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mDOw

Settings
# exclusive inclusive rows x rows loops node
1. 0.012 863,391.426 ↓ 1.4 35 1

Unique (cost=8,888.29..8,888.61 rows=25 width=19) (actual time=863,384.591..863,391.426 rows=35 loops=1)

2. 3.889 863,391.414 ↓ 1.4 35 1

Group (cost=8,888.29..8,888.48 rows=25 width=19) (actual time=863,384.589..863,391.414 rows=35 loops=1)

  • Group Key: td.designator_id, dsg.activity_id
3. 22.763 863,387.525 ↓ 1,444.4 36,109 1

Sort (cost=8,888.29..8,888.36 rows=25 width=15) (actual time=863,384.588..863,387.525 rows=36,109 loops=1)

  • Sort Key: td.designator_id, dsg.activity_id
  • Sort Method: quicksort Memory: 3,654kB
4. 231.958 863,364.762 ↓ 1,444.4 36,109 1

Nested Loop (cost=13.06..8,887.71 rows=25 width=15) (actual time=6,800.507..863,364.762 rows=36,109 loops=1)

  • Join Filter: (loc.wo_loc_group_id = wtl.loc_group_id)
  • Rows Removed by Join Filter: 2,202,649
5. 5.570 5.570 ↑ 16.0 62 1

Function Scan on dblink wtl (cost=0.00..10.00 rows=995 width=4) (actual time=5.550..5.570 rows=62 loops=1)

  • Filter: (regional IS NOT NULL)
6. 177.446 863,127.234 ↓ 7,221.8 36,109 62

Materialize (cost=13.05..8,803.10 rows=5 width=19) (actual time=13.924..13,921.407 rows=36,109 loops=62)

7. 290.156 862,949.788 ↓ 7,221.8 36,109 1

Nested Loop Left Join (cost=13.05..8,803.07 rows=5 width=19) (actual time=863.306..862,949.788 rows=36,109 loops=1)

  • Join Filter: ((dsg.designator_id)::text = (td.designator_id)::text)
  • Rows Removed by Join Filter: 1,877,668
8. 80.569 862,515.196 ↓ 36,109.0 36,109 1

Nested Loop (cost=13.05..8,780.57 rows=1 width=15) (actual time=859.913..862,515.196 rows=36,109 loops=1)

9. 75.889 1,210.537 ↓ 27,701.0 27,701 1

Hash Join (cost=12.63..37.66 rows=1 width=36) (actual time=841.993..1,210.537 rows=27,701 loops=1)

  • Hash Cond: ((loc.user_id)::text = (nama.user_id)::text)
10. 1,127.343 1,127.343 ↓ 5,742.4 28,712 1

Function Scan on dblink loc (cost=0.00..25.00 rows=5 width=68) (actual time=834.672..1,127.343 rows=28,712 loops=1)

  • Filter: ((wo_loc_group_id IS NOT NULL) AND ((user_loc_id <> 0) OR (user_loc_id = 0)) AND ((end_dtm)::date >= '2020-07-26'::date) AND ((end_dtm)::date <= '2020-08-25'::date))
  • Rows Removed by Filter: 317,130
11. 0.237 7.305 ↓ 152.2 1,522 1

Hash (cost=12.50..12.50 rows=10 width=32) (actual time=7.305..7.305 rows=1,522 loops=1)

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 77kB
12. 7.068 7.068 ↓ 152.2 1,522 1

Function Scan on dblink nama (cost=0.00..12.50 rows=10 width=32) (actual time=6.725..7.068 rows=1,522 loops=1)

  • Filter: (role_id = ANY ('{15,20}'::integer[]))
  • Rows Removed by Filter: 525
13. 861,224.090 861,224.090 ↑ 2.0 1 27,701

Index Only Scan using "Idx_designator_details" on t_designator_details td (cost=0.42..8,742.89 rows=2 width=27) (actual time=15.759..31.090 rows=1 loops=27,701)

  • Index Cond: (wo_detail_id = (loc.wo_detail_id)::text)
  • Heap Fetches: 7,914
14. 144.436 144.436 ↑ 18.9 53 36,109

Function Scan on dblink dsg (cost=0.00..10.00 rows=1,000 width=36) (actual time=0.000..0.004 rows=53 loops=36,109)

Planning time : 0.884 ms
Execution time : 863,414.076 ms