explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PNCf

Settings
# exclusive inclusive rows x rows loops node
1. 0.242 3,692.559 ↓ 3.1 901 1

Limit (cost=1,731,274.54..2,563,157.40 rows=294 width=1,151) (actual time=433.138..3,692.559 rows=901 loops=1)

2. 1,239.878 3,692.317 ↓ 3.1 901 1

Nested Loop Left Join (cost=1,731,274.54..2,563,157.40 rows=294 width=1,151) (actual time=433.137..3,692.317 rows=901 loops=1)

  • Join Filter: ((d.entity_category)::text = (tmnsa.tm_nsa_type_id)::text)
  • Rows Removed by Join Filter: 51,737
3. 1.350 2,449.736 ↓ 3.1 901 1

Nested Loop Left Join (cost=1,731,274.54..2,562,470.22 rows=294 width=864) (actual time=432.684..2,449.736 rows=901 loops=1)

4. 1.083 2,447.485 ↓ 3.1 901 1

Nested Loop Left Join (cost=1,731,274.26..2,562,381.29 rows=294 width=842) (actual time=432.669..2,447.485 rows=901 loops=1)

5. 16.406 2,443.699 ↓ 3.1 901 1

Merge Anti Join (cost=1,731,273.98..2,562,292.36 rows=294 width=820) (actual time=432.647..2,443.699 rows=901 loops=1)

  • Merge Cond: (d.entity_id = t.entity_id)
6. 0.512 1,212.024 ↓ 1.5 902 1

Nested Loop Left Join (cost=1,731,173.56..2,528,257.25 rows=589 width=820) (actual time=186.961..1,212.024 rows=902 loops=1)

  • Join Filter: (d.impact_score = ei_lang.impact_score)
7. 1.293 1,211.512 ↓ 1.5 902 1

Nested Loop (cost=1,731,173.56..2,528,247.35 rows=589 width=602) (actual time=186.955..1,211.512 rows=902 loops=1)

  • Join Filter: (d.impact_score = ei.impact_score)
  • Rows Removed by Join Filter: 1,208
8. 1,210.176 1,210.219 ↑ 21.8 902 1

Foreign Scan on entity d (cost=1,731,173.56..2,526,779.54 rows=19,622 width=370) (actual time=186.942..1,210.219 rows=902 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 1 or hashed SubPlan 2) AND (entity_type = 2)) OR (entity_type <> 2)))
  • Rows Removed by Filter: 51,867
9.          

SubPlan (for Foreign Scan)

10. 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
11. 0.043 0.043 ↑ 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.029..0.043 rows=97 loops=1)

  • Index Cond: (extended_function_id = 14)
  • Heap Fetches: 1
12. 0.000 0.000 ↑ 2.5 2 902

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

13. 0.008 0.008 ↑ 1.2 4 1

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

14. 0.000 0.000 ↓ 0.0 0 902

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

15. 0.003 0.003 ↓ 0.0 0 1

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

  • Filter: ((code)::text = 'en'::text)
  • Rows Removed by Filter: 5
16. 1,215.269 1,215.269 ↑ 1.9 220,289 1

Foreign Scan on archived_entities t (cost=100.42..20,688.03 rows=417,703 width=8) (actual time=1.139..1,215.269 rows=220,289 loops=1)

17. 2.703 2.703 ↑ 1.0 1 901

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

  • Index Cond: (d.publisher_orgref = swa_org_ref)
18. 0.901 0.901 ↑ 1.0 1 901

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

  • Index Cond: (d.responsible_org_orgref = swa_org_ref)
19. 2.686 2.703 ↓ 1.1 58 901

Materialize (cost=0.00..3.80 rows=53 width=22) (actual time=0.000..0.003 rows=58 loops=901)

20. 0.017 0.017 ↓ 1.1 58 1

Seq Scan on tm_nsa tmnsa (cost=0.00..3.53 rows=53 width=22) (actual time=0.005..0.017 rows=58 loops=1)

Planning time : 12.289 ms
Execution time : 3,694.099 ms