explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6kHm

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 1,194.801 ↓ 0.0 0 1

Hash Left Join (cost=28,038.77..2,196,928.03 rows=1,849 width=531) (actual time=1,194.801..1,194.801 rows=0 loops=1)

  • Hash Cond: (d.publisher_organisation_id = liveorg.organisation_id)
2.          

CTE live_orgs

3. 0.000 0.000 ↓ 0.0 0

Index Only Scan using organisation_extended_funcs_pkey on organisation_extended_funcs (cost=0.27..5.72 rows=83 width=4) (never executed)

  • Index Cond: (extended_function_id = 14)
  • Heap Fetches: 0
4. 0.008 1,194.800 ↓ 0.0 0 1

Hash Join (cost=28,030.34..2,191,321.82 rows=1,849 width=395) (actual time=1,194.800..1,194.800 rows=0 loops=1)

  • Hash Cond: (d.publisher_orgref = puborgref.swa_org_ref)
5. 0.004 1,193.898 ↓ 0.0 0 1

Hash Join (cost=27,973.74..2,191,239.71 rows=1,867 width=377) (actual time=1,193.898..1,193.898 rows=0 loops=1)

  • Hash Cond: (d.responsible_org_orgref = resporgref.swa_org_ref)
6. 0.006 1,193.055 ↓ 0.0 0 1

Hash Join (cost=27,917.14..2,191,157.37 rows=1,888 width=355) (actual time=1,193.055..1,193.055 rows=0 loops=1)

  • Hash Cond: ((d.entity_category)::text = (ec.nsa_code)::text)
7. 0.001 1,192.947 ↓ 0.0 0 1

Hash Left Join (cost=27,909.95..2,191,103.28 rows=7,471 width=299) (actual time=1,192.947..1,192.947 rows=0 loops=1)

  • Hash Cond: ((d.entity_category)::text = (ec_lang.nsa_code)::text)
8. 994.838 1,192.946 ↓ 0.0 0 1

Bitmap Heap Scan on entity d (cost=27,906.82..2,191,071.30 rows=7,471 width=261) (actual time=1,192.946..1,192.946 rows=0 loops=1)

  • Recheck Cond: (end_date >= to_timestamp('01/09/2018 00:00:00'::text, 'DD/MM/YYYY HH24:MI:SS'::text))
  • Rows Removed by Index Recheck: 39783
  • Filter: ((tm__entity_id_sw_xref IS NULL) AND (COALESCE(tomtom__max_average_speed, '-9'::double precision) >= '0'::double precision) AND (published = 1) AND (start_date <= to_timestamp('21/12/2018 23:59:59'::text, 'DD/MM/YYYY HH24:MI:SS'::text)) AND ((entity_category)::text = ANY ('{TMNSA08,INCIDENT,QUEUE,OBSTRUCTION,NSA022,ALERT,"SIGNAL FAILURE",TMNSA00,ACCIDENT,NSA021,CLOSED,TMNSA10,TMNSA11,TMNSA07,WEATHER,NSA023,TMNSA12,TMNSA36,TMNSA37,TMNSA37}'::text[])))
  • Rows Removed by Filter: 1172779
  • Heap Blocks: exact=50688 lossy=79951
9. 198.108 198.108 ↑ 1.0 1,173,000 1

Bitmap Index Scan on idx_entity_end_date (cost=0.00..27,904.95 rows=1,225,135 width=0) (actual time=198.108..198.108 rows=1,173,000 loops=1)

  • Index Cond: (end_date >= to_timestamp('01/09/2018 00:00:00'::text, 'DD/MM/YYYY HH24:MI:SS'::text))
10. 0.000 0.000 ↓ 0.0 0

Hash (cost=3.11..3.11 rows=1 width=45) (never executed)

11. 0.000 0.000 ↓ 0.0 0

Seq Scan on entity_category_lang ec_lang (cost=0.00..3.11 rows=1 width=45) (never executed)

  • Filter: ((code)::text = 'en'::text)
12. 0.013 0.102 ↓ 1.0 24 1

Hash (cost=6.91..6.91 rows=23 width=63) (actual time=0.102..0.102 rows=24 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
13. 0.025 0.089 ↓ 1.0 24 1

Hash Left Join (cost=3.19..6.91 rows=23 width=63) (actual time=0.059..0.089 rows=24 loops=1)

  • Hash Cond: ((ec.layer_code)::text = (tn.layer_code)::text)
14. 0.017 0.017 ↓ 1.0 24 1

Seq Scan on entity_category ec (cost=0.00..3.14 rows=23 width=55) (actual time=0.006..0.017 rows=24 loops=1)

  • Filter: ((entitygroupid)::text = 'INC'::text)
  • Rows Removed by Filter: 67
15. 0.021 0.047 ↑ 1.1 47 1

Hash (cost=2.53..2.53 rows=53 width=32) (actual time=0.047..0.047 rows=47 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
16. 0.026 0.026 ↑ 1.0 53 1

Seq Scan on tm_nsa tn (cost=0.00..2.53 rows=53 width=32) (actual time=0.003..0.026 rows=53 loops=1)

17. 0.398 0.839 ↑ 1.0 1,298 1

Hash (cost=40.38..40.38 rows=1,298 width=26) (actual time=0.839..0.839 rows=1,298 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 81kB
18. 0.441 0.441 ↑ 1.0 1,298 1

Seq Scan on orgref resporgref (cost=0.00..40.38 rows=1,298 width=26) (actual time=0.003..0.441 rows=1,298 loops=1)

  • Filter: (active = 1)
  • Rows Removed by Filter: 12
19. 0.392 0.894 ↑ 1.0 1,298 1

Hash (cost=40.38..40.38 rows=1,298 width=26) (actual time=0.894..0.894 rows=1,298 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 81kB
20. 0.502 0.502 ↑ 1.0 1,298 1

Seq Scan on orgref puborgref (cost=0.00..40.38 rows=1,298 width=26) (actual time=0.007..0.502 rows=1,298 loops=1)

  • Filter: (active = 1)
  • Rows Removed by Filter: 12
21. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.66..1.66 rows=83 width=4) (never executed)

22. 0.000 0.000 ↓ 0.0 0

CTE Scan on live_orgs liveorg (cost=0.00..1.66 rows=83 width=4) (never executed)