explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 35Jz

Settings
# exclusive inclusive rows x rows loops node
1. 13.644 13.644 ↑ 1.6 34 1

CTE Scan on rws (cost=2,759.98..2,914.96 rows=56 width=840) (actual time=0.772..13.644 rows=34 loops=1)

2.          

CTE gtab

3. 0.074 0.074 ↑ 1.0 1 1

Result (cost=0.00..0.26 rows=1 width=32) (actual time=0.073..0.074 rows=1 loops=1)

4.          

CTE rws

5. 0.008 13.070 ↑ 1.6 34 1

Limit (cost=1.38..2,759.72 rows=56 width=758) (actual time=0.757..13.070 rows=34 loops=1)

6. 0.007 13.062 ↑ 1.6 34 1

Append (cost=1.38..2,759.72 rows=56 width=758) (actual time=0.757..13.062 rows=34 loops=1)

7. 0.003 8.021 ↑ 1.8 17 1

Limit (cost=1.38..1,604.33 rows=31 width=559) (actual time=0.757..8.021 rows=17 loops=1)

8. 2.864 8.018 ↑ 1.8 17 1

Nested Loop (cost=1.38..1,604.33 rows=31 width=559) (actual time=0.756..8.018 rows=17 loops=1)

9. 0.017 5.120 ↑ 1.8 17 1

Nested Loop (cost=1.10..1,555.29 rows=31 width=717) (actual time=0.435..5.120 rows=17 loops=1)

10. 0.012 5.052 ↑ 1.8 17 1

Nested Loop Left Join (cost=0.83..1,546.00 rows=31 width=695) (actual time=0.425..5.052 rows=17 loops=1)

  • Join Filter: (d.impact_score = ei_lang.impact_score)
11. 0.010 5.040 ↑ 1.8 17 1

Nested Loop (cost=0.83..1,544.47 rows=31 width=477) (actual time=0.421..5.040 rows=17 loops=1)

  • Join Filter: (d.impact_score = ei.impact_score)
  • Rows Removed by Join Filter: 27
12. 0.731 5.013 ↑ 1.8 17 1

Nested Loop (cost=0.83..1,541.39 rows=31 width=389) (actual time=0.415..5.013 rows=17 loops=1)

13. 0.110 2.182 ↓ 1.3 1,050 1

Nested Loop (cost=0.41..450.60 rows=787 width=123) (actual time=0.202..2.182 rows=1,050 loops=1)

14. 0.075 0.075 ↑ 1.0 1 1

CTE Scan on gtab (cost=0.00..0.02 rows=1 width=32) (actual time=0.075..0.075 rows=1 loops=1)

15. 1.997 1.997 ↓ 13.3 1,050 1

Index Scan using sidx_entity_location_bng on entity e (cost=0.41..449.79 rows=79 width=123) (actual time=0.126..1.997 rows=1,050 loops=1)

  • Index Cond: (location_bng && gtab.bbox)
16. 2.100 2.100 ↓ 0.0 0 1,050

Index Scan using pk_entity_eton_agg on entity_eton_agg d (cost=0.42..1.38 rows=1 width=276) (actual time=0.002..0.002 rows=0 loops=1,050)

  • Index Cond: (entity_id = e.entity_id)
  • Filter: (active AND (publisher_swtype = 1) AND (impact_score = ANY ('{1,2,3,4}'::integer[])) AND (start_date <= to_timestamp('05/08/2020 23:59:59 23:59:59'::text, 'DD/MM/YYYY HH24:MI:SS'::text)) AND (end_date >= to_timestamp('05/08/2020 00:00:00 00:00:00'::text, 'DD/MM/YYYY HH24:MI:SS'::text)) AND (works_state = ANY ('{0,2,3,4,5,6,7,8}'::integer[])) AND (((publisher_orgref = 1,440) AND (COALESCE((e.permit_status)::integer, 0) <> ALL ('{1,2,14,15,24}'::integer[]))) OR (publisher_orgref <> 1440)))
  • Rows Removed by Filter: 0
17. 0.013 0.017 ↑ 1.7 3 17

Materialize (cost=0.00..1.07 rows=5 width=92) (actual time=0.000..0.001 rows=3 loops=17)

18. 0.004 0.004 ↑ 1.2 4 1

Seq Scan on eton_impact ei (cost=0.00..1.05 rows=5 width=92) (actual time=0.003..0.004 rows=4 loops=1)

19. 0.000 0.000 ↓ 0.0 0 17

Materialize (cost=0.00..1.07 rows=1 width=222) (actual time=0.000..0.000 rows=0 loops=17)

20. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on eton_impact_lang ei_lang (cost=0.00..1.06 rows=1 width=222) (actual time=0.002..0.002 rows=0 loops=1)

  • Filter: ((code)::text = 'en'::text)
  • Rows Removed by Filter: 5
21. 0.051 0.051 ↑ 1.0 1 17

Index Scan using pk_orgref on orgref publisher (cost=0.28..0.30 rows=1 width=26) (actual time=0.003..0.003 rows=1 loops=17)

  • Index Cond: (swa_org_ref = d.publisher_orgref)
22. 0.034 0.034 ↑ 1.0 1 17

Index Scan using pk_orgref on orgref resporg (cost=0.28..0.30 rows=1 width=26) (actual time=0.002..0.002 rows=1 loops=17)

  • Index Cond: (swa_org_ref = d.responsible_org_orgref)
23. 1.996 5.034 ↑ 1.5 17 1

Nested Loop Left Join (cost=1.25..1,154.83 rows=25 width=1,005) (actual time=0.392..5.034 rows=17 loops=1)

24. 0.025 2.987 ↑ 1.5 17 1

Nested Loop (cost=1.11..1,131.37 rows=25 width=745) (actual time=0.224..2.987 rows=17 loops=1)

25. 0.022 2.894 ↑ 1.5 17 1

Nested Loop Left Join (cost=0.97..1,127.36 rows=25 width=745) (actual time=0.213..2.894 rows=17 loops=1)

26. 0.027 2.855 ↑ 1.5 17 1

Nested Loop Left Join (cost=0.69..1,119.85 rows=25 width=723) (actual time=0.210..2.855 rows=17 loops=1)

27. 0.011 2.777 ↑ 1.5 17 1

Nested Loop Left Join (cost=0.41..1,112.34 rows=25 width=701) (actual time=0.203..2.777 rows=17 loops=1)

  • Join Filter: (d_1.impact_score = ei_lang_1.impact_score)
28. 0.013 2.766 ↑ 1.5 17 1

Nested Loop (cost=0.41..1,110.90 rows=25 width=483) (actual time=0.200..2.766 rows=17 loops=1)

  • Join Filter: (d_1.impact_score = ei_1.impact_score)
  • Rows Removed by Join Filter: 33
29. 0.009 2.736 ↑ 1.5 17 1

Nested Loop (cost=0.41..1,108.21 rows=25 width=395) (actual time=0.197..2.736 rows=17 loops=1)

30. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on gtab gtab_1 (cost=0.00..0.02 rows=1 width=32) (actual time=0.000..0.001 rows=1 loops=1)

31. 2.674 2.726 ↓ 5.7 17 1

Index Scan using sidx_entity_location_bng on entity d_1 (cost=0.41..1,108.16 rows=3 width=395) (actual time=0.194..2.726 rows=17 loops=1)

  • Index Cond: (location_bng && gtab_1.bbox)
  • Filter: (active AND (tm__entity_id_sw_xref IS NULL) AND (entity_type > 1) AND ((entitygroupid)::text = 'RW'::text) AND (impact_score = ANY ('{1,2,3,4}'::integer[])) AND (start_date <= to_timestamp('05/08/2020 23:59:59 23:59:59'::text, 'DD/MM/YYYY HH24:MI:SS'::text)) AND (end_date >= to_timestamp('05/08/2020 00:00:00 00:00:00'::text, 'DD/MM/YYYY HH24:MI:SS'::text)) AND (((publisher_orgref = 1,440) AND (COALESCE((permit_status)::integer, 0) <> ALL ('{1,2,14,15,24}'::integer[]))) OR (publisher_orgref <> 1440) OR (publisher_orgref IS NULL)) AND (((alternatives: SubPlan 2 or hashed SubPlan 3) AND (entity_type = 2)) OR (entity_type > 2)))
  • Rows Removed by Filter: 1,033
32.          

SubPlan (for Index Scan)

33. 0.000 0.000 ↓ 0.0 0

Index Only Scan using organisation_extended_funcs_pkey on organisation_extended_funcs f (cost=0.28..8.29 rows=1 width=0) (never executed)

  • Index Cond: ((extended_function_id = 14) AND (organisation_id = d_1.publisher_organisation_id))
  • Heap Fetches: 0
34. 0.031 0.052 ↓ 1.0 123 1

Bitmap Heap Scan on organisation_extended_funcs f_1 (cost=5.22..11.75 rows=122 width=4) (actual time=0.033..0.052 rows=123 loops=1)

  • Recheck Cond: (extended_function_id = 14)
  • Heap Blocks: exact=5
35. 0.021 0.021 ↓ 1.0 123 1

Bitmap Index Scan on organisation_extended_funcs_pkey (cost=0.00..5.19 rows=122 width=0) (actual time=0.021..0.021 rows=123 loops=1)

  • Index Cond: (extended_function_id = 14)
36. 0.015 0.017 ↑ 1.7 3 17

Materialize (cost=0.00..1.07 rows=5 width=92) (actual time=0.000..0.001 rows=3 loops=17)

37. 0.002 0.002 ↑ 1.2 4 1

Seq Scan on eton_impact ei_1 (cost=0.00..1.05 rows=5 width=92) (actual time=0.001..0.002 rows=4 loops=1)

38. 0.000 0.000 ↓ 0.0 0 17

Materialize (cost=0.00..1.07 rows=1 width=222) (actual time=0.000..0.000 rows=0 loops=17)

39. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on eton_impact_lang ei_lang_1 (cost=0.00..1.06 rows=1 width=222) (actual time=0.002..0.002 rows=0 loops=1)

  • Filter: ((code)::text = 'en'::text)
  • Rows Removed by Filter: 5
40. 0.051 0.051 ↑ 1.0 1 17

Index Scan using pk_orgref on orgref publisher_1 (cost=0.28..0.30 rows=1 width=26) (actual time=0.003..0.003 rows=1 loops=17)

  • Index Cond: (d_1.publisher_orgref = swa_org_ref)
41. 0.017 0.017 ↓ 0.0 0 17

Index Scan using pk_orgref on orgref resporg_1 (cost=0.28..0.30 rows=1 width=26) (actual time=0.001..0.001 rows=0 loops=17)

  • Index Cond: (d_1.responsible_org_orgref = swa_org_ref)
42. 0.068 0.068 ↑ 1.0 1 17

Index Only Scan using entity_category_pkey on entity_category ecat (cost=0.14..0.16 rows=1 width=118) (actual time=0.004..0.004 rows=1 loops=17)

  • Index Cond: (nsa_code = (d_1.entity_category)::text)
  • Heap Fetches: 0
43. 0.051 0.051 ↑ 1.0 1 17

Index Scan using tm_nsa_pkey on tm_nsa tmnsa (cost=0.14..0.16 rows=1 width=22) (actual time=0.003..0.003 rows=1 loops=17)

  • Index Cond: ((d_1.entity_category)::text = (tm_nsa_type_id)::text)
Planning time : 10.130 ms
Execution time : 14.079 ms