explain.depesz.com

PostgreSQL's explain analyze made readable

Result: g2Vo

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

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

2.          

Initplan (for Aggregate)

3. 37.902 1,845.490 ↓ 3.2 56,336 1

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

  • Group Key: edl.entity_id
4.          

CTE user_data_filter

5. 130.642 130.642 ↓ 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.041..130.642 rows=332,543 loops=1)

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

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

7. 85.053 1,117.509 ↓ 3.4 56,609 1

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

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

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

  • Sort Key: edl.ancestor_id, edl.ancestor_type_id
  • Sort Method: external merge Disk: 6,088kB
9. 157.928 157.928 ↓ 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.047..157.928 rows=282,504 loops=1)

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

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

  • Sort Key: da.entity_id, da.entity_type_id
  • Sort Method: external sort Disk: 7,168kB
11. 343.296 343.296 ↓ 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.046..343.296 rows=332,543 loops=1)

  • Filter: self_access
12. 0.008 676.589 ↓ 0.0 0 1

Merge Join (cost=202,396.72..206,141.41 rows=858 width=4) (actual time=676.589..676.589 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. 163.877 645.530 ↑ 283,621.0 1 1

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

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

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

  • Workers Planned: 3
  • Workers Launched: 3
15. 54.935 385.517 ↑ 1.6 56,504 4 / 4

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

16. 48.078 48.078 ↑ 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.046..48.078 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.011 31.051 ↓ 0.0 0 1

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

  • Sort Key: da_1.entity_id, da_1.entity_type_id
  • Sort Method: quicksort Memory: 25kB
19. 31.040 31.040 ↓ 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=31.040..31.040 rows=0 loops=1)

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

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

21. 143.632 2,776.644 ↓ 67,570.8 405,425 1

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

  • Hash Cond: (cd.type = lcpt.id)
22. 2,632.977 2,632.977 ↓ 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,867.481..2,632.977 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.006 0.035 ↓ 3.0 3 1

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

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

Seq Scan on link_client_performance_type lcpt (cost=0.00..1.30 rows=1 width=8) (actual time=0.027..0.029 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 : 3.788 ms
Execution time : 3,573.750 ms