explain.depesz.com

PostgreSQL's explain analyze made readable

Result: inAd

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 3,312.181 ↑ 1.0 1 1

Aggregate (cost=309,771.39..309,771.40 rows=1 width=8) (actual time=3,312.180..3,312.181 rows=1 loops=1)

2.          

Initplan (for Aggregate)

3. 37.451 1,708.544 ↓ 3.2 56,336 1

HashAggregate (cost=309,487.96..309,663.61 rows=17,565 width=4) (actual time=1,692.955..1,708.544 rows=56,336 loops=1)

  • Group Key: edl.entity_id
4.          

CTE user_data_filter

5. 129.674 129.674 ↓ 1.0 332,543 1

Index Scan using idx_user_data_access_user_id_deleted on user_data_access (cost=0.43..26,333.86 rows=325,988 width=23) (actual time=0.032..129.674 rows=332,543 loops=1)

  • Index Cond: ((deleted = false) AND (user_id = 1,979))
  • Filter: (NOT deleted)
6. 13.504 1,671.093 ↓ 3.2 56,609 1

Append (cost=73,291.83..283,110.19 rows=17,565 width=4) (actual time=853.585..1,671.093 rows=56,609 loops=1)

7. 80.217 1,010.387 ↓ 3.4 56,609 1

Merge Join (cost=73,291.83..76,705.30 rows=16,707 width=4) (actual time=853.584..1,010.387 rows=56,609 loops=1)

  • Merge Cond: ((edl.ancestor_id = da.entity_id) AND (edl.ancestor_type_id = da.entity_type_id))
8. 206.667 336.148 ↓ 1.0 282,504 1

Sort (cost=52,661.31..53,335.95 rows=269,859 width=12) (actual time=279.857..336.148 rows=282,504 loops=1)

  • Sort Key: edl.ancestor_id, edl.ancestor_type_id
  • Sort Method: external merge Disk: 6,088kB
9. 129.481 129.481 ↓ 1.0 282,504 1

Index Scan using idx_entity_data_link_entity_type_id on entity_data_link edl (cost=0.44..25,615.63 rows=269,859 width=12) (actual time=0.026..129.481 rows=282,504 loops=1)

  • Index Cond: (entity_type_id = 12)
  • Filter: (NOT deleted)
10. 276.697 594.022 ↑ 1.5 111,091 1

Sort (cost=20,630.52..21,038.01 rows=162,994 width=8) (actual time=573.626..594.022 rows=111,091 loops=1)

  • Sort Key: da.entity_id, da.entity_type_id
  • Sort Method: external sort Disk: 7,168kB
11. 317.325 317.325 ↓ 2.0 332,543 1

CTE Scan on user_data_filter da (cost=0.00..6,519.76 rows=162,994 width=8) (actual time=0.038..317.325 rows=332,543 loops=1)

  • Filter: self_access
12. 0.006 647.202 ↓ 0.0 0 1

Merge Join (cost=202,396.72..206,141.41 rows=858 width=4) (actual time=647.202..647.202 rows=0 loops=1)

  • Merge Cond: ((edl_1.ancestor_id = da_1.entity_id) AND (edl_1.ancestor_type_id = da_1.entity_type_id))
  • Join Filter: (edc.ancestor_type_id = ANY (da_1.access_type_ids))
13. 148.429 617.576 ↑ 283,621.0 1 1

Sort (cost=181,766.20..182,475.25 rows=283,621 width=16) (actual time=617.576..617.576 rows=1 loops=1)

  • Sort Key: edl_1.ancestor_id, edl_1.ancestor_type_id
  • Sort Method: external merge Disk: 5,760kB
14. 82.286 469.147 ↑ 1.3 226,016 1

Gather (cost=1,000.88..153,239.95 rows=283,621 width=16) (actual time=0.658..469.147 rows=226,016 loops=1)

  • Workers Planned: 3
  • Workers Launched: 3
15. 48.637 386.861 ↑ 1.6 56,504 4 / 4

Nested Loop (cost=0.88..123,877.85 rows=91,491 width=16) (actual time=0.087..386.861 rows=56,504 loops=4)

16. 55.720 55.720 ↑ 1.2 70,626 4 / 4

Parallel Index Scan using idx_entity_data_link_entity_type_id on entity_data_link edc (cost=0.44..23,787.56 rows=87,051 width=12) (actual time=0.037..55.720 rows=70,626 loops=4)

  • Index Cond: (entity_type_id = 12)
  • Filter: (NOT deleted)
17. 282.504 282.504 ↑ 1.0 1 282,504 / 4

Index Scan using idx_entity_data_link_entity_type_id_entity_id on entity_data_link edl_1 (cost=0.44..1.14 rows=1 width=16) (actual time=0.003..0.004 rows=1 loops=282,504)

  • Index Cond: ((entity_type_id = edc.ancestor_type_id) AND (entity_id = edc.ancestor_id))
  • Filter: (parent AND (NOT deleted))
  • Rows Removed by Filter: 2
18. 0.010 29.620 ↓ 0.0 0 1

Sort (cost=20,630.52..21,038.01 rows=162,994 width=40) (actual time=29.620..29.620 rows=0 loops=1)

  • Sort Key: da_1.entity_id, da_1.entity_type_id
  • Sort Method: quicksort Memory: 25kB
19. 29.610 29.610 ↓ 0.0 0 1

CTE Scan on user_data_filter da_1 (cost=0.00..6,519.76 rows=162,994 width=40) (actual time=29.610..29.610 rows=0 loops=1)

  • Filter: (NOT self_access)
  • Rows Removed by Filter: 332,543
20. 0.000 3,311.610 ↓ 3,765.0 3,765 1

Nested Loop (cost=2.02..107.78 rows=1 width=0) (actual time=2,372.883..3,311.610 rows=3,765 loops=1)

21. 141.695 2,557.333 ↓ 67,570.8 405,425 1

Hash Join (cost=1.74..93.68 rows=6 width=4) (actual time=1,729.818..2,557.333 rows=405,425 loops=1)

  • Hash Cond: (cd.type = lcpt.id)
22. 2,415.619 2,415.619 ↓ 3,048.3 405,425 1

Index Scan using child_dno_client_id_dno_id_idx on child_dno cd (cost=0.42..91.95 rows=133 width=20) (actual time=1,729.785..2,415.619 rows=405,425 loops=1)

  • Index Cond: ((client_id = 1,007) AND (dnoid = ANY ($4)))
  • Filter: ((NOT deleted) AND (status_id = ANY ('{2462,2490,2491,2492,2,1,4,21}'::integer[])))
  • Rows Removed by Filter: 6
23. 0.005 0.019 ↓ 3.0 3 1

Hash (cost=1.30..1.30 rows=1 width=8) (actual time=0.019..0.019 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
24. 0.014 0.014 ↓ 3.0 3 1

Seq Scan on link_client_performance_type lcpt (cost=0.00..1.30 rows=1 width=8) (actual time=0.012..0.014 rows=3 loops=1)

  • Filter: (client_id = 1,007)
  • Rows Removed by Filter: 21
25. 810.850 810.850 ↓ 0.0 0 405,425

Index Scan using dno_deleted_ix on dno (cost=0.29..2.35 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=405,425)

  • Index Cond: (id = cd.dnoid)
  • Filter: (status_id = ANY ('{2460,2461,2463,2464,2465,2466,2467,2468,2,1,5,6}'::integer[]))
  • Rows Removed by Filter: 1
Planning time : 2.479 ms
Execution time : 3,317.829 ms