explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dNfE

Settings
# exclusive inclusive rows x rows loops node
1. 0.016 16.226 ↓ 1.3 52 1

Limit (cost=0.99..955.60 rows=41 width=831) (actual time=0.682..16.226 rows=52 loops=1)

2.          

CTE gtab

3. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=1)

4. 11.189 16.210 ↓ 1.3 52 1

Nested Loop Left Join (cost=0.98..955.59 rows=41 width=831) (actual time=0.682..16.210 rows=52 loops=1)

5. 0.053 4.865 ↓ 1.3 52 1

Nested Loop Left Join (cost=0.84..886.29 rows=41 width=718) (actual time=0.276..4.865 rows=52 loops=1)

6. 0.085 4.708 ↓ 1.3 52 1

Nested Loop Left Join (cost=0.56..874.06 rows=41 width=696) (actual time=0.268..4.708 rows=52 loops=1)

7. 0.033 4.467 ↓ 1.3 52 1

Nested Loop Left Join (cost=0.29..861.83 rows=41 width=674) (actual time=0.257..4.467 rows=52 loops=1)

  • Join Filter: (d.impact_score = ei_lang.impact_score)
8. 0.076 4.434 ↓ 1.3 52 1

Nested Loop (cost=0.29..860.15 rows=41 width=456) (actual time=0.254..4.434 rows=52 loops=1)

  • Join Filter: (d.impact_score = ei.impact_score)
  • Rows Removed by Join Filter: 96
9. 0.028 4.358 ↓ 1.3 52 1

Nested Loop (cost=0.29..856.41 rows=41 width=368) (actual time=0.249..4.358 rows=52 loops=1)

10. 0.003 0.003 ↑ 1.0 1 1

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

11. 4.275 4.327 ↓ 13.0 52 1

Index Scan using sidx_entity_location_wgs84 on entity d (cost=0.29..856.35 rows=4 width=368) (actual time=0.241..4.327 rows=52 loops=1)

  • Index Cond: (location_wgs84 && gtab.bbox)
  • Filter: ((tm__entity_id_sw_xref IS NULL) AND ((works_state <> 1) OR (works_state IS NULL)) AND ((entitygroupid)::text = 'RW'::text) AND (published = 1) AND (start_date <= to_timestamp('01/08/2020 23:59:59'::text, 'DD/MM/YYYY HH24:MI:SS'::text)) AND (end_date >= to_timestamp('01/08/2020 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)) AND (((alternatives: SubPlan 2 or hashed SubPlan 3) AND (entity_type = 2)) OR (entity_type <> 2)))
  • Rows Removed by Filter: 1,469
12.          

SubPlan (for Index Scan)

13. 0.000 0.000 ↓ 0.0 0

Index Only Scan using organisation_extended_funcs_pkey on organisation_extended_funcs oef (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
14. 0.031 0.052 ↑ 1.0 127 1

Bitmap Heap Scan on organisation_extended_funcs oef_1 (cost=9.26..15.85 rows=127 width=4) (actual time=0.033..0.052 rows=127 loops=1)

  • Recheck Cond: (extended_function_id = 14)
  • Heap Blocks: exact=5
15. 0.021 0.021 ↑ 1.0 127 1

Bitmap Index Scan on organisation_extended_funcs_pkey (cost=0.00..9.23 rows=127 width=0) (actual time=0.021..0.021 rows=127 loops=1)

  • Index Cond: (extended_function_id = 14)
16. 0.000 0.000 ↑ 1.7 3 52

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

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

18. 0.000 0.000 ↓ 0.0 0 52

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

19. 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
20. 0.156 0.156 ↑ 1.0 1 52

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=52)

  • Index Cond: (d.publisher_orgref = swa_org_ref)
21. 0.104 0.104 ↑ 1.0 1 52

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=52)

  • Index Cond: (d.responsible_org_orgref = swa_org_ref)
22. 0.156 0.156 ↓ 0.0 0 52

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=0 loops=52)

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