explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ddDX

Settings
# exclusive inclusive rows x rows loops node
1. 0.034 62.778 ↑ 1.0 100 1

Limit (cost=434.02..1,218.28 rows=100 width=423) (actual time=3.025..62.778 rows=100 loops=1)

2.          

CTE t_grouping_category

3. 0.181 6.555 ↓ 1.9 786 1

Subquery Scan on grouping_view (cost=410.12..431.24 rows=421 width=72) (actual time=0.529..6.555 rows=786 loops=1)

4. 0.209 6.374 ↓ 1.9 786 1

Nested Loop (cost=410.12..427.03 rows=421 width=149) (actual time=0.529..6.374 rows=786 loops=1)

5.          

CTE tmp_grouping_name

6. 0.028 0.028 ↑ 1.0 3 1

Values Scan on "*VALUES*" (cost=0.00..0.06 rows=3 width=64) (actual time=0.015..0.028 rows=3 loops=1)

7. 0.037 0.037 ↑ 1.0 1 1

CTE Scan on tmp_grouping_name tmp (cost=0.00..0.07 rows=1 width=32) (actual time=0.021..0.037 rows=1 loops=1)

  • Filter: (grouping_type = 'CATEGORY'::text)
  • Rows Removed by Filter: 2
8. 6.128 6.128 ↓ 1.9 786 1

CTE Scan on tmp_data_grouping tmp_1 (cost=410.06..418.48 rows=421 width=116) (actual time=0.507..6.128 rows=786 loops=1)

9.          

CTE tmp_data_grouping

10. 0.966 4.863 ↓ 1.9 786 1

Recursive Union (cost=47.39..410.06 rows=421 width=116) (actual time=0.505..4.863 rows=786 loops=1)

11. 0.000 0.054 ↑ 1.0 1 1

Index Scan using grouping_pkey on "grouping" gr (cost=47.39..49.62 rows=1 width=116) (actual time=0.053..0.054 rows=1 loops=1)

  • Index Cond: (grouping_id = $3)
  • Filter: (parent_grouping_id = 1)
12.          

Initplan (for Index Scan)

13. 0.006 0.446 ↑ 2.0 1 1

Nested Loop (cost=0.00..47.11 rows=2 width=8) (actual time=0.181..0.446 rows=1 loops=1)

  • Join Filter: (lg.locale_id = l.locale_id)
14. 0.030 0.030 ↑ 1.0 1 1

Seq Scan on locale l (cost=0.00..1.01 rows=1 width=8) (actual time=0.029..0.030 rows=1 loops=1)

  • Filter: (language_code = 'en'::bpchar)
15. 0.410 0.410 ↑ 2.0 1 1

Seq Scan on localized_grouping lg (cost=0.00..46.08 rows=2 width=16) (actual time=0.146..0.410 rows=1 loops=1)

  • Filter: ((name)::text = $2)
  • Rows Removed by Filter: 2070
16. 1.519 3.843 ↓ 2.1 87 9

Nested Loop (cost=0.56..35.20 rows=42 width=116) (actual time=0.037..0.427 rows=87 loops=9)

17. 0.645 1.539 ↓ 2.0 87 9

Nested Loop (cost=0.28..20.73 rows=44 width=84) (actual time=0.032..0.171 rows=87 loops=9)

18. 0.108 0.108 ↓ 8.7 87 9

WorkTable Scan on tmp_data_grouping tree (cost=0.00..0.20 rows=10 width=76) (actual time=0.000..0.012 rows=87 loops=9)

19. 0.786 0.786 ↑ 4.0 1 786

Index Scan using idx_grouping_parent_grouping_id on "grouping" gr_1 (cost=0.28..2.01 rows=4 width=16) (actual time=0.001..0.001 rows=1 loops=786)

  • Index Cond: (parent_grouping_id = tree.grouping_id)
20. 0.785 0.785 ↑ 1.0 1 785

Index Scan using idx_localized_grouping_grouping_id on localized_grouping lg_1 (cost=0.28..0.31 rows=1 width=27) (actual time=0.001..0.001 rows=1 loops=785)

  • Index Cond: (grouping_id = gr_1.grouping_id)
21. 10.934 62.744 ↑ 364,458.3 100 1

Nested Loop Left Join (cost=2.79..285,827,773.73 rows=36,445,831 width=423) (actual time=3.022..62.744 rows=100 loops=1)

22. 0.213 2.010 ↑ 140,209.5 100 1

Nested Loop Left Join (cost=1.77..143,799,396.96 rows=14,020,946 width=517) (actual time=0.673..2.010 rows=100 loops=1)

23. 0.797 0.797 ↑ 140,209.5 100 1

Seq Scan on sns_account sa (cost=0.00..3,327,042.46 rows=14,020,946 width=453) (actual time=0.103..0.797 rows=100 loops=1)

24. 0.369 1.000 ↓ 0.0 0 100

Hash Join (cost=1.77..11.78 rows=1 width=68) (actual time=0.010..0.010 rows=0 loops=100)

  • Hash Cond: (tgce.grouping_id = named_entity_grouping.grouping_id)
25. 0.531 0.531 ↑ 421.0 1 1

CTE Scan on t_grouping_category tgce (cost=0.00..8.42 rows=421 width=72) (actual time=0.531..0.531 rows=1 loops=1)

26. 0.000 0.100 ↓ 0.0 0 100

Hash (cost=1.75..1.75 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=100)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
27. 0.100 0.100 ↓ 0.0 0 100

Nested Loop (cost=0.71..1.75 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=100)

28. 0.000 0.000 ↓ 0.0 0 100

Index Only Scan using named_entity_pkey on named_entity (cost=0.42..0.71 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=100)

  • Index Cond: (named_entity_id = sa.named_entity_id)
  • Heap Fetches: 0
29. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_named_entity_grouping_named_entity_id on named_entity_grouping (cost=0.29..1.03 rows=2 width=12) (never executed)

  • Index Cond: (named_entity_id = named_entity.named_entity_id)
30. 10.592 49.800 ↑ 2.0 1 100

Hash Join (cost=1.02..11.04 rows=2 width=72) (actual time=0.258..0.498 rows=1 loops=100)

  • Hash Cond: (tgc.grouping_id = sag.grouping_id)
31. 19.008 19.008 ↓ 1.9 779 99

CTE Scan on t_grouping_category tgc (cost=0.00..8.42 rows=421 width=72) (actual time=0.000..0.192 rows=779 loops=99)

32. 0.100 20.200 ↑ 1.0 1 100

Hash (cost=1.01..1.01 rows=1 width=16) (actual time=0.202..0.202 rows=1 loops=100)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
33. 20.100 20.100 ↑ 1.0 1 100

Index Only Scan using uni_sns_account_grouping_sns_account_id_grouping_id on sns_account_grouping sag (cost=0.56..1.01 rows=1 width=16) (actual time=0.200..0.201 rows=1 loops=100)

  • Index Cond: (sns_account_id = sa.sns_account_id)
  • Heap Fetches: 97