explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TGuh

Settings
# exclusive inclusive rows x rows loops node
1. 0.014 1,855.875 ↑ 859.0 1 1

HashAggregate (cost=309,239.52..309,248.11 rows=859 width=8) (actual time=1,855.871..1,855.875 rows=1 loops=1)

  • Group Key: (count(edl.entity_id))
2.          

CTE user_data_filter

3. 124.887 124.887 ↓ 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.036..124.887 rows=332,543 loops=1)

  • Index Cond: ((deleted = false) AND (user_id = 1,979))
  • Filter: (NOT deleted)
4. 0.004 1,855.861 ↑ 859.0 1 1

Append (cost=76,747.07..282,903.52 rows=859 width=8) (actual time=966.366..1,855.861 rows=1 loops=1)

5. 8.240 966.365 ↑ 1.0 1 1

Aggregate (cost=76,747.07..76,747.08 rows=1 width=8) (actual time=966.364..966.365 rows=1 loops=1)

6. 78.735 958.125 ↓ 3.4 56,609 1

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

  • Merge Cond: ((edl.ancestor_id = da.entity_id) AND (edl.ancestor_type_id = da.entity_type_id))
7. 184.251 304.130 ↓ 1.0 282,504 1

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

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

  • Index Cond: (entity_type_id = 12)
  • Filter: (NOT deleted)
9. 263.061 575.260 ↑ 1.5 111,091 1

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

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

  • Filter: self_access
11. 0.002 889.492 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=202,396.72..206,152.14 rows=858 width=8) (actual time=889.492..889.492 rows=0 loops=1)

12. 0.006 889.490 ↓ 0.0 0 1

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

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

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

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

  • Workers Planned: 3
  • Workers Launched: 3
15. 129.427 497.022 ↑ 1.6 56,504 4 / 4

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

16. 85.091 85.091 ↑ 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=2.533..85.091 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.004..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.346 ↓ 0.0 0 1

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

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

  • Filter: (NOT self_access)
  • Rows Removed by Filter: 332,543
Planning time : 0.875 ms
Execution time : 1,861.480 ms