explain.depesz.com

PostgreSQL's explain analyze made readable

Result: s9Qc

Settings
# exclusive inclusive rows x rows loops node
1. 0.029 63.325 ↑ 1.0 100 1

Limit (cost=823.00..2,025.34 rows=100 width=423) (actual time=3.066..63.325 rows=100 loops=1)

2. 3.703 63.296 ↑ 364,458.3 100 1

Merge Left Join (cost=823.00..438,201,029.05 rows=36,445,831 width=423) (actual time=3.065..63.296 rows=100 loops=1)

  • Merge Cond: (sa.sns_account_id = sag.sns_account_id)
3. 0.238 33.938 ↑ 140,209.5 100 1

Nested Loop Left Join (cost=412.32..324,010,370.56 rows=14,020,946 width=517) (actual time=0.039..33.938 rows=100 loops=1)

4. 0.900 0.900 ↑ 140,209.5 100 1

Index Scan using idx_sns_account_sns_account_id_5 on sns_account sa (cost=0.43..5,506,644.10 rows=14,020,946 width=453) (actual time=0.012..0.900 rows=100 loops=1)

5. 0.952 32.800 ↓ 0.0 0 100

Hash Join (cost=411.89..434.59 rows=1 width=68) (actual time=0.175..0.328 rows=0 loops=100)

  • Hash Cond: (tmp_1.grouping_id = neg.grouping_id)
6. 1.314 31.248 ↓ 1.7 714 6

Nested Loop (cost=410.12..427.03 rows=421 width=149) (actual time=0.348..5.208 rows=714 loops=6)

7.          

CTE tmp_grouping_name

8. 0.020 0.020 ↑ 1.0 3 1

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

9. 0.048 0.048 ↑ 1.0 1 6

CTE Scan on tmp_grouping_name tmp (cost=0.00..0.07 rows=1 width=32) (actual time=0.004..0.008 rows=1 loops=6)

  • Filter: (grouping_type = 'CATEGORY'::text)
  • Rows Removed by Filter: 2
10. 29.886 29.886 ↓ 1.7 714 6

CTE Scan on tmp_data_grouping tmp_1 (cost=410.06..418.48 rows=421 width=116) (actual time=0.307..4.981 rows=714 loops=6)

11.          

CTE tmp_data_grouping

12. 4.442 25.062 ↓ 1.7 714 6

Recursive Union (cost=47.39..410.06 rows=421 width=116) (actual time=0.303..4.177 rows=714 loops=6)

13. 0.000 0.138 ↑ 1.0 1 6

Index Scan using grouping_pkey on "grouping" gr_2 (cost=47.39..49.62 rows=1 width=116) (actual time=0.022..0.023 rows=1 loops=6)

  • Index Cond: (grouping_id = $10)
  • Filter: (parent_grouping_id = 1)
14.          

Initplan (for Index Scan)

15. 0.024 1.602 ↑ 2.0 1 6

Nested Loop (cost=0.00..47.11 rows=2 width=8) (actual time=0.106..0.267 rows=1 loops=6)

  • Join Filter: (lg_2.locale_id = l_1.locale_id)
16. 0.042 0.042 ↑ 1.0 1 6

Seq Scan on locale l_1 (cost=0.00..1.01 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=6)

  • Filter: (language_code = 'en'::bpchar)
17. 1.536 1.536 ↑ 2.0 1 6

Seq Scan on localized_grouping lg_2 (cost=0.00..46.08 rows=2 width=16) (actual time=0.095..0.256 rows=1 loops=6)

  • Filter: ((name)::text = $9)
  • Rows Removed by Filter: 2070
18. 8.362 20.482 ↓ 2.1 87 49

Nested Loop (cost=0.56..35.20 rows=42 width=116) (actual time=0.033..0.418 rows=87 loops=49)

19. 3.278 7.840 ↓ 2.0 87 49

Nested Loop (cost=0.28..20.73 rows=44 width=84) (actual time=0.029..0.160 rows=87 loops=49)

20. 0.539 0.539 ↓ 8.2 82 49

WorkTable Scan on tmp_data_grouping tree_1 (cost=0.00..0.20 rows=10 width=76) (actual time=0.000..0.011 rows=82 loops=49)

21. 4.023 4.023 ↑ 4.0 1 4,023

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

  • Index Cond: (parent_grouping_id = tree_1.grouping_id)
22. 4.280 4.280 ↑ 1.0 1 4,280

Index Scan using idx_localized_grouping_grouping_id on localized_grouping lg_3 (cost=0.28..0.31 rows=1 width=27) (actual time=0.001..0.001 rows=1 loops=4,280)

  • Index Cond: (grouping_id = gr_3.grouping_id)
23. 0.100 0.600 ↓ 0.0 0 100

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
24. 0.029 0.500 ↓ 0.0 0 100

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

25. 0.300 0.300 ↓ 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.003..0.003 rows=0 loops=100)

  • Index Cond: (named_entity_id = sa.named_entity_id)
  • Heap Fetches: 0
26. 0.171 0.171 ↑ 2.0 1 9

Index Scan using idx_named_entity_grouping_named_entity_id on named_entity_grouping neg (cost=0.29..1.03 rows=2 width=12) (actual time=0.015..0.019 rows=1 loops=9)

  • Index Cond: (named_entity_id = named_entity.named_entity_id)
27. 0.082 25.655 ↑ 387,721.6 94 1

Materialize (cost=410.68..112,493,746.55 rows=36,445,831 width=72) (actual time=3.000..25.655 rows=94 loops=1)

28. 11.000 25.573 ↑ 387,721.6 94 1

Nested Loop (cost=410.68..112,402,631.97 rows=36,445,831 width=72) (actual time=2.993..25.573 rows=94 loops=1)

  • Join Filter: (sag.grouping_id = tgc.grouping_id)
  • Rows Removed by Join Filter: 73354
29. 0.191 0.191 ↑ 184,190.8 94 1

Index Only Scan using uni_sns_account_grouping_sns_account_id_grouping_id on sns_account_grouping sag (cost=0.56..3,064,706.47 rows=17,313,934 width=16) (actual time=0.054..0.191 rows=94 loops=1)

  • Heap Fetches: 94
30. 7.842 14.382 ↓ 1.9 781 94

Materialize (cost=410.12..433.34 rows=421 width=72) (actual time=0.006..0.153 rows=781 loops=94)

31. 0.169 6.540 ↓ 1.9 786 1

Subquery Scan on tgc (cost=410.12..431.24 rows=421 width=72) (actual time=0.549..6.540 rows=786 loops=1)

32. 0.200 6.371 ↓ 1.9 786 1

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

33.          

CTE tmp_grouping_name

34. 0.028 0.028 ↑ 1.0 3 1

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

35. 0.035 0.035 ↑ 1.0 1 1

CTE Scan on tmp_grouping_name tmp_2 (cost=0.00..0.07 rows=1 width=32) (actual time=0.022..0.035 rows=1 loops=1)

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

CTE Scan on tmp_data_grouping tmp_3 (cost=410.06..418.48 rows=421 width=116) (actual time=0.525..6.136 rows=786 loops=1)

37.          

CTE tmp_data_grouping

38. 1.133 5.044 ↓ 1.9 786 1

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

39. 0.000 0.050 ↑ 1.0 1 1

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

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

Initplan (for Index Scan)

41. 0.005 0.470 ↑ 2.0 1 1

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

  • Join Filter: (lg.locale_id = l.locale_id)
42. 0.027 0.027 ↑ 1.0 1 1

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

  • Filter: (language_code = 'en'::bpchar)
43. 0.438 0.438 ↑ 2.0 1 1

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

  • Filter: ((name)::text = $2)
  • Rows Removed by Filter: 2070
44. 1.528 3.861 ↓ 2.1 87 9

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

45. 0.672 1.548 ↓ 2.0 87 9

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

46. 0.090 0.090 ↓ 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.010 rows=87 loops=9)

47. 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)
48. 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)