explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qDWO

Settings
# exclusive inclusive rows x rows loops node
1. 6.435 6.435 ↑ 1.4 17 1

CTE Scan on rws (cost=1,090.01..1,156.43 rows=24 width=840) (actual time=0.645..6.435 rows=17 loops=1)

2.          

CTE gtab

3. 0.075 0.075 ↑ 1.0 1 1

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

4.          

CTE rws

5. 0.005 6.018 ↑ 1.4 17 1

Limit (cost=0.00..1,089.75 rows=24 width=992) (actual time=0.627..6.018 rows=17 loops=1)

6. 0.004 6.013 ↑ 1.4 17 1

Append (cost=0.00..1,089.75 rows=24 width=992) (actual time=0.626..6.013 rows=17 loops=1)

7. 0.001 0.001 ↓ 0.0 0 1

Limit (cost=0.00..0.00 rows=1 width=563) (actual time=0.001..0.001 rows=0 loops=1)

8. 0.000 0.000 ↓ 0.0 0 1

Result (cost=0.00..0.00 rows=0 width=563) (actual time=0.000..0.000 rows=0 loops=1)

  • One-Time Filter: false
9. 2.220 6.008 ↑ 1.4 17 1

Nested Loop Left Join (cost=1.25..1,089.51 rows=23 width=1,009) (actual time=0.625..6.008 rows=17 loops=1)

10. 0.029 3.720 ↑ 1.4 17 1

Nested Loop (cost=1.11..1,067.93 rows=23 width=748) (actual time=0.338..3.720 rows=17 loops=1)

11. 0.024 3.623 ↑ 1.4 17 1

Nested Loop Left Join (cost=0.97..1,064.24 rows=23 width=748) (actual time=0.323..3.623 rows=17 loops=1)

12. 0.028 3.582 ↑ 1.4 17 1

Nested Loop Left Join (cost=0.69..1,057.31 rows=23 width=726) (actual time=0.319..3.582 rows=17 loops=1)

13. 0.016 3.503 ↑ 1.4 17 1

Nested Loop Left Join (cost=0.41..1,050.39 rows=23 width=704) (actual time=0.313..3.503 rows=17 loops=1)

  • Join Filter: (d.impact_score = ei_lang.impact_score)
14. 0.010 3.487 ↑ 1.4 17 1

Nested Loop (cost=0.41..1,048.98 rows=23 width=486) (actual time=0.308..3.487 rows=17 loops=1)

  • Join Filter: (d.impact_score = ei.impact_score)
  • Rows Removed by Join Filter: 33
15. 0.009 3.460 ↑ 1.4 17 1

Nested Loop (cost=0.41..1,046.44 rows=23 width=398) (actual time=0.303..3.460 rows=17 loops=1)

16. 0.076 0.076 ↑ 1.0 1 1

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

17. 3.322 3.375 ↓ 8.5 17 1

Index Scan using sidx_entity_location_bng on entity d (cost=0.41..1,046.40 rows=2 width=398) (actual time=0.225..3.375 rows=17 loops=1)

  • Index Cond: (location_bng && gtab.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
18.          

SubPlan (for Index Scan)

19. 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.publisher_organisation_id))
  • Heap Fetches: 0
20. 0.030 0.053 ↓ 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.034..0.053 rows=123 loops=1)

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

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

  • Index Cond: (extended_function_id = 14)
22. 0.014 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)

23. 0.003 0.003 ↑ 1.2 4 1

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

24. 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)

25. 0.004 0.004 ↓ 0.0 0 1

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

  • Filter: ((code)::text = 'en'::text)
  • Rows Removed by Filter: 5
26. 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: (d.publisher_orgref = swa_org_ref)
27. 0.017 0.017 ↓ 0.0 0 17

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

  • Index Cond: (d.responsible_org_orgref = swa_org_ref)
28. 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.entity_category)::text)
  • Heap Fetches: 0
29. 0.068 0.068 ↑ 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.004..0.004 rows=1 loops=17)

  • Index Cond: ((d.entity_category)::text = (tm_nsa_type_id)::text)
Planning time : 13.110 ms
Execution time : 6.776 ms