explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XTZV

Settings
# exclusive inclusive rows x rows loops node
1. 0.046 130,705.772 ↓ 50.0 50 1

Limit (cost=1,666,957.43..2,763,018.16 rows=1 width=1,151) (actual time=5,047.237..130,705.772 rows=50 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. 2,264.757 130,705.726 ↓ 50.0 50 1

Nested Loop Anti Join (cost=1,666,957.42..2,763,018.15 rows=1 width=1,151) (actual time=5,047.236..130,705.726 rows=50 loops=1)

  • Join Filter: (t.entity_id = d.entity_id)
  • Rows Removed by Join Filter: 21,731,632
5. 0.522 8,092.117 ↓ 52.0 52 1

Nested Loop Left Join (cost=1,666,857.42..2,742,496.24 rows=1 width=820) (actual time=2,643.955..8,092.117 rows=52 loops=1)

  • Join Filter: ((d.entity_category)::text = (tmnsa.tm_nsa_type_id)::text)
  • Rows Removed by Join Filter: 2,963
6. 2.007 8,091.231 ↓ 52.0 52 1

Nested Loop Left Join (cost=1,666,857.42..2,742,492.05 rows=1 width=864) (actual time=2,643.937..8,091.231 rows=52 loops=1)

  • Join Filter: (d.responsible_org_orgref = resporg.swa_org_ref)
  • Rows Removed by Join Filter: 27,691
7. 2.546 8,086.884 ↓ 52.0 52 1

Nested Loop Left Join (cost=1,666,857.42..2,742,429.67 rows=1 width=842) (actual time=2,643.858..8,086.884 rows=52 loops=1)

  • Join Filter: (d.publisher_orgref = publisher.swa_org_ref)
  • Rows Removed by Join Filter: 27,631
8. 0.165 8,080.750 ↓ 52.0 52 1

Nested Loop Left Join (cost=1,666,857.42..2,742,367.30 rows=1 width=820) (actual time=2,643.738..8,080.750 rows=52 loops=1)

  • Join Filter: (d.impact_score = ei_lang.impact_score)
9. 0.125 8,080.325 ↓ 52.0 52 1

Nested Loop (cost=1,666,857.42..2,742,366.22 rows=1 width=602) (actual time=2,643.732..8,080.325 rows=52 loops=1)

  • Join Filter: (d.impact_score = ei.impact_score)
  • Rows Removed by Join Filter: 96
10. 19.820 8,080.148 ↓ 1.6 52 1

Nested Loop (cost=1,666,857.42..2,742,362.76 rows=32 width=514) (actual time=2,643.711..8,080.148 rows=52 loops=1)

  • Join Filter: (d.location_wgs84 && gtab.bbox)
  • Rows Removed by Join Filter: 23,517
11. 8,060.271 8,060.328 ↑ 1.1 23,569 1

Foreign Scan on entity d (cost=1,666,857.42..2,741,766.27 rows=26,510 width=370) (actual time=240.911..8,060.328 rows=23,569 loops=1)

  • Filter: ((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: 327,802
12.          

SubPlan (for Foreign 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..4.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.057 0.057 ↑ 1.0 97 1

Index Only Scan using organisation_extended_funcs_pkey on organisation_extended_funcs oef_1 (cost=0.28..5.97 rows=97 width=4) (actual time=0.041..0.057 rows=97 loops=1)

  • Index Cond: (extended_function_id = 14)
  • Heap Fetches: 1
15. 0.000 0.000 ↑ 1.0 1 23,569

CTE Scan on gtab (cost=0.00..0.02 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=23,569)

16. 0.036 0.052 ↑ 1.7 3 52

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

17. 0.016 0.016 ↑ 1.2 4 1

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

18. 0.260 0.260 ↓ 0.0 0 52

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

  • Filter: ((code)::text = 'en'::text)
  • Rows Removed by Filter: 5
19. 3.588 3.588 ↑ 2.5 532 52

Seq Scan on orgref publisher (cost=0.00..45.50 rows=1,350 width=26) (actual time=0.001..0.069 rows=532 loops=52)

20. 2.340 2.340 ↑ 2.5 534 52

Seq Scan on orgref resporg (cost=0.00..45.50 rows=1,350 width=26) (actual time=0.000..0.045 rows=534 loops=52)

21. 0.364 0.364 ↓ 1.1 57 52

Seq Scan on tm_nsa tmnsa (cost=0.00..3.53 rows=53 width=22) (actual time=0.002..0.007 rows=57 loops=52)

22. 120,348.852 120,348.852 ↓ 1.0 417,916 52

Foreign Scan on archived_entities t (cost=100.00..15,299.09 rows=417,703 width=8) (actual time=0.574..2,314.401 rows=417,916 loops=52)

Planning time : 12.332 ms
Execution time : 130,707.737 ms