explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DAA1

Settings
# exclusive inclusive rows x rows loops node
1. 1.229 9,324.342 ↑ 1.0 100 1

Limit (cost=0.00..748,784.77 rows=100 width=37) (actual time=107.232..9,324.342 rows=100 loops=1)

2. 11.613 9,323.113 ↑ 1.1 100 1

Seq Scan on permission pms (cost=0.00..838,638.95 rows=112 width=37) (actual time=107.217..9,323.113 rows=100 loops=1)

3.          

SubPlan (for Seq Scan)

4. 3.700 5.800 ↑ 1.0 1 100

Aggregate (cost=1.90..1.91 rows=1 width=74) (actual time=0.051..0.058 rows=1 loops=100)

5. 2.100 2.100 ↑ 1.0 1 100

Seq Scan on domain d (cost=0.00..1.89 rows=1 width=74) (actual time=0.015..0.021 rows=1 loops=100)

  • Filter: (permission_id = pms.id)
  • Rows Removed by Filter: 70
6. 5.000 19.200 ↑ 1.0 1 100

Aggregate (cost=21.08..21.09 rows=1 width=60) (actual time=0.186..0.192 rows=1 loops=100)

7. 6.544 14.200 ↑ 1.0 2 100

Nested Loop (cost=0.15..21.06 rows=2 width=60) (actual time=0.053..0.142 rows=2 loops=100)

8. 4.200 4.200 ↑ 1.0 2 100

Seq Scan on granted_object_type got (cost=0.00..4.70 rows=2 width=4) (actual time=0.018..0.042 rows=2 loops=100)

  • Filter: (permission_id = pms.id)
  • Rows Removed by Filter: 214
9. 3.456 3.456 ↑ 1.0 1 192

Index Scan using object_type_pkey on object_type ot (cost=0.15..8.17 rows=1 width=64) (actual time=0.011..0.018 rows=1 loops=192)

  • Index Cond: (id = got.object_type_id)
10. 87.900 2,182.200 ↑ 1.0 1 100

Aggregate (cost=1,260.87..1,260.88 rows=1 width=46) (actual time=21.816..21.822 rows=1 loops=100)

11. 99.137 2,094.300 ↑ 2.6 47 100

Nested Loop Left Join (cost=0.00..1,259.01 rows=124 width=46) (actual time=6.145..20.943 rows=47 loops=100)

  • Join Filter: ((csc_2.country_set_id = country_set.id) AND (csc_2.country_id = c.id))
  • Rows Removed by Join Filter: 10
12. 724.860 1,952.800 ↑ 2.6 47 100

Nested Loop (cost=0.00..1,247.53 rows=124 width=53) (actual time=6.073..19.528 rows=47 loops=100)

  • Join Filter: CASE country_set.include_all WHEN CASE_TEST_EXPR THEN (NOT (SubPlan 3)) ELSE (SubPlan 4) END
  • Rows Removed by Join Filter: 202
13. 2.300 2.300 ↑ 1.0 1 100

Seq Scan on country_set (cost=0.00..2.40 rows=1 width=5) (actual time=0.012..0.023 rows=1 loops=100)

  • Filter: (pms.granted_country_set_id = id)
  • Rows Removed by Filter: 111
14. 164.900 164.900 ↑ 1.0 249 100

Seq Scan on country c (cost=0.00..4.49 rows=249 width=49) (actual time=0.010..1.649 rows=249 loops=100)

15.          

SubPlan (for Nested Loop)

16. 80.676 80.676 ↓ 0.0 0 4,482

Seq Scan on country_set_country csc (cost=0.00..4.96 rows=1 width=4) (actual time=0.018..0.018 rows=0 loops=4,482)

  • Filter: ((NOT included) AND (country_set_id = country_set.id))
  • Rows Removed by Filter: 237
17. 980.064 980.064 ↑ 1.0 3 20,418

Seq Scan on country_set_country csc_1 (cost=0.00..4.96 rows=3 width=4) (actual time=0.019..0.048 rows=3 loops=20,418)

  • Filter: (included AND (country_set_id = country_set.id))
  • Rows Removed by Filter: 233
18. 37.863 42.363 ↓ 0.0 0 4,707

Materialize (cost=0.00..4.98 rows=3 width=9) (actual time=0.007..0.009 rows=0 loops=4,707)

19. 4.500 4.500 ↑ 1.5 2 100

Seq Scan on country_set_country csc_2 (cost=0.00..4.96 rows=3 width=9) (actual time=0.020..0.045 rows=2 loops=100)

  • Filter: (country_set_id = pms.granted_country_set_id)
  • Rows Removed by Filter: 235
20. 302.700 1,671.300 ↑ 1.0 1 100

Aggregate (cost=3,303.04..3,303.05 rows=1 width=45) (actual time=16.706..16.713 rows=1 loops=100)

21. 318.112 1,368.600 ↓ 1.9 167 100

Nested Loop Left Join (cost=0.43..3,301.73 rows=87 width=45) (actual time=0.259..13.686 rows=167 loops=100)

  • Join Filter: (lsl_2.language_set_id = language_set.id)
22. 381.392 916.600 ↓ 1.9 167 100

Nested Loop (cost=0.28..3,261.63 rows=87 width=52) (actual time=0.229..9.166 rows=167 loops=100)

  • Join Filter: CASE language_set.include_all WHEN CASE_TEST_EXPR THEN (NOT (SubPlan 6)) ELSE (SubPlan 7) END
  • Rows Removed by Join Filter: 7
23. 2.500 2.500 ↑ 1.0 1 100

Seq Scan on language_set (cost=0.00..2.40 rows=1 width=5) (actual time=0.014..0.025 rows=1 loops=100)

  • Filter: (pms.granted_language_set_id = id)
  • Rows Removed by Filter: 111
24. 133.900 133.900 ↑ 1.0 174 100

Index Scan using language_iso_639_1_idx on language l (cost=0.28..21.22 rows=174 width=48) (actual time=0.015..1.339 rows=174 loops=100)

  • Index Cond: (iso_639_1 IS NOT NULL)
25.          

SubPlan (for Nested Loop)

26. 217.152 350.784 ↓ 0.0 0 16,704

Bitmap Heap Scan on language_set_language lsl (cost=4.21..14.37 rows=4 width=4) (actual time=0.021..0.021 rows=0 loops=16,704)

  • Recheck Cond: (language_set_id = language_set.id)
  • Filter: (NOT included)
27. 133.632 133.632 ↓ 0.0 0 16,704

Bitmap Index Scan on language_set_language_language_set_id_language_id_idx (cost=0.00..4.21 rows=8 width=0) (actual time=0.008..0.008 rows=0 loops=16,704)

  • Index Cond: (language_set_id = language_set.id)
28. 42.456 48.024 ↓ 2.0 8 696

Bitmap Heap Scan on language_set_language lsl_1 (cost=4.21..14.37 rows=4 width=4) (actual time=0.022..0.069 rows=8 loops=696)

  • Recheck Cond: (language_set_id = language_set.id)
  • Filter: included
  • Heap Blocks: exact=696
29. 5.568 5.568 ↑ 1.0 8 696

Bitmap Index Scan on language_set_language_language_set_id_language_id_idx (cost=0.00..4.21 rows=8 width=0) (actual time=0.008..0.008 rows=8 loops=696)

  • Index Cond: (language_set_id = language_set.id)
30. 133.888 133.888 ↓ 0.0 0 16,736

Index Scan using language_set_language_language_set_id_language_id_idx on language_set_language lsl_2 (cost=0.15..0.45 rows=1 width=9) (actual time=0.008..0.008 rows=0 loops=16,736)

  • Index Cond: ((language_set_id = pms.granted_language_set_id) AND (language_id = l.id))
31. 559.500 2,718.500 ↑ 1.0 1 100

Aggregate (cost=1,450.44..1,450.45 rows=1 width=64) (actual time=27.179..27.185 rows=1 loops=100)

32. 1,026.367 2,159.000 ↓ 1.7 300 100

Nested Loop (cost=0.00..1,448.71 rows=172 width=64) (actual time=0.702..21.590 rows=300 loops=100)

  • Join Filter: CASE provider_set.include_all WHEN CASE_TEST_EXPR THEN (NOT (SubPlan 9)) ELSE (SubPlan 10) END
  • Rows Removed by Join Filter: 43
33. 2.500 2.500 ↑ 1.0 1 100

Seq Scan on provider_set (cost=0.00..2.40 rows=1 width=5) (actual time=0.014..0.025 rows=1 loops=100)

  • Filter: (pms.granted_provider_set_id = id)
  • Rows Removed by Filter: 111
34. 227.700 227.700 ↑ 1.0 343 100

Seq Scan on provider p (cost=0.00..7.43 rows=343 width=68) (actual time=0.010..2.277 rows=343 loops=100)

35.          

SubPlan (for Nested Loop)

36. 447.615 447.615 ↓ 0.0 0 29,841

Seq Scan on provider_set_provider psp (cost=0.00..4.16 rows=1 width=4) (actual time=0.015..0.015 rows=0 loops=29,841)

  • Filter: ((NOT included) AND (provider_set_id = provider_set.id))
  • Rows Removed by Filter: 173
37. 454.818 454.818 ↑ 1.1 12 4,459

Seq Scan on provider_set_provider psp_1 (cost=0.00..4.16 rows=13 width=4) (actual time=0.014..0.102 rows=12 loops=4,459)

  • Filter: (included AND (provider_set_id = provider_set.id))
  • Rows Removed by Filter: 157
38. 558.400 2,714.500 ↑ 1.0 1 100

Aggregate (cost=1,450.44..1,450.45 rows=1 width=64) (actual time=27.140..27.145 rows=1 loops=100)

39. 1,021.608 2,156.100 ↓ 1.7 300 100

Nested Loop (cost=0.00..1,448.71 rows=172 width=64) (actual time=0.699..21.561 rows=300 loops=100)

  • Join Filter: CASE provider_set_1.include_all WHEN CASE_TEST_EXPR THEN (NOT (SubPlan 12)) ELSE (SubPlan 13) END
  • Rows Removed by Join Filter: 43
40. 2.300 2.300 ↑ 1.0 1 100

Seq Scan on provider_set provider_set_1 (cost=0.00..2.40 rows=1 width=5) (actual time=0.012..0.023 rows=1 loops=100)

  • Filter: (pms.affiliated_provider_set_id = id)
  • Rows Removed by Filter: 111
41. 225.300 225.300 ↑ 1.0 343 100

Seq Scan on provider p_1 (cost=0.00..7.43 rows=343 width=68) (actual time=0.009..2.253 rows=343 loops=100)

42.          

SubPlan (for Nested Loop)

43. 447.615 447.615 ↓ 0.0 0 29,841

Seq Scan on provider_set_provider psp_2 (cost=0.00..4.16 rows=1 width=4) (actual time=0.015..0.015 rows=0 loops=29,841)

  • Filter: ((NOT included) AND (provider_set_id = provider_set_1.id))
  • Rows Removed by Filter: 173
44. 459.277 459.277 ↑ 1.1 12 4,459

Seq Scan on provider_set_provider psp_3 (cost=0.00..4.16 rows=13 width=4) (actual time=0.014..0.103 rows=12 loops=4,459)

  • Filter: (included AND (provider_set_id = provider_set_1.id))
  • Rows Removed by Filter: 157
Planning time : 1.166 ms
Execution time : 9,325.344 ms