explain.depesz.com

PostgreSQL's explain analyze made readable

Result: deJY

Settings
# exclusive inclusive rows x rows loops node
1. 0.550 219.653 ↓ 30.0 30 1

Nested Loop Left Join (cost=10.65..62,318.16 rows=1 width=945) (actual time=3.352..219.653 rows=30 loops=1)

2. 8.392 218.983 ↓ 30.0 30 1

Nested Loop Left Join (cost=6.37..62,309.33 rows=1 width=383) (actual time=3.292..218.983 rows=30 loops=1)

  • Join Filter: (e_cte.publisher_organisation_id = lku_orgref.organisation_id)
  • Rows Removed by Join Filter: 38910
3. 0.026 198.081 ↓ 30.0 30 1

Nested Loop Left Join (cost=6.37..62,252.73 rows=1 width=365) (actual time=2.633..198.081 rows=30 loops=1)

  • Join Filter: ((e_cte.entity_category)::text = (lku_ec_lang.nsa_code)::text)
4. 0.801 197.785 ↓ 30.0 30 1

Nested Loop Left Join (cost=6.37..62,249.61 rows=1 width=335) (actual time=2.619..197.785 rows=30 loops=1)

  • Join Filter: ((e_cte.entity_category)::text = (lku_ec.nsa_code)::text)
  • Rows Removed by Join Filter: 2700
5. 0.971 196.384 ↓ 30.0 30 1

Nested Loop (cost=6.37..62,245.56 rows=1 width=313) (actual time=2.594..196.384 rows=30 loops=1)

  • Join Filter: ((e_cte.entity_category)::text = (tmnsa.tm_nsa_type_id)::text)
  • Rows Removed by Join Filter: 2819
6. 190.202 190.233 ↓ 259.0 259 1

Index Scan using idx_entity_entitytype on entity e_cte (cost=6.37..62,242.16 rows=1 width=284) (actual time=1.706..190.233 rows=259 loops=1)

  • Index Cond: (entity_type = ANY ('{2,4,5}'::integer[]))
  • Filter: (active AND (location_point_easting >= 393214) AND (location_point_easting <= 480509) AND (location_point_northing >= 245607) AND (location_point_northing <= 308006) AND (COALESCE(tomtom__max_average_speed, '-1'::double precision) >= '-1'::double precision) AND (start_date <= to_date('31/10/2018 00:00:00'::text, 'DD/MM/YYYY HH24:MI:SS'::text)) AND (end_date >= to_date('01/10/2018 00:00:00'::text, 'DD/MM/YYYY HH24:MI:SS'::text)) AND ((entity_type <> 2) OR (hashed SubPlan 1)) AND (COALESCE((tm__sw_cancelled_status)::integer, 0) = 0))
  • Rows Removed by Filter: 290548
7.          

SubPlan (forIndex Scan)

8. 0.031 0.031 ↑ 1.0 82 1

Index Only Scan using organisation_extended_funcs_pkey on organisation_extended_funcs (cost=0.27..5.72 rows=83 width=4) (actual time=0.009..0.031 rows=82 loops=1)

  • Index Cond: (extended_function_id = 14)
  • Heap Fetches: 0
9. 5.180 5.180 ↑ 1.0 11 259

Seq Scan on tm_nsa tmnsa (cost=0.00..3.26 rows=11 width=37) (actual time=0.003..0.020 rows=11 loops=259)

  • Filter: ((tm_nsa_type_id)::text = ANY ('{TMNSA21,TMNSA22,TMNSA23,TMNSA24,TMNSA01,TMNSA42,TMNSA41,TMNSA43,TMNSA46,TMNSA30,TMNSA32}'::text[]))
  • Rows Removed by Filter: 42
10. 0.600 0.600 ↑ 1.0 91 30

Seq Scan on entity_category lku_ec (cost=0.00..2.91 rows=91 width=29) (actual time=0.001..0.020 rows=91 loops=30)

11. 0.270 0.270 ↓ 0.0 0 30

Seq Scan on entity_category_lang lku_ec_lang (cost=0.00..3.11 rows=1 width=45) (actual time=0.009..0.009 rows=0 loops=30)

  • Filter: ((code)::text = 'en'::text)
  • Rows Removed by Filter: 89
12. 12.510 12.510 ↑ 1.0 1,298 30

Seq Scan on orgref lku_orgref (cost=0.00..40.38 rows=1,298 width=26) (actual time=0.001..0.417 rows=1,298 loops=30)

  • Filter: (active = 1)
  • Rows Removed by Filter: 12
13. 0.060 0.120 ↑ 1.0 1 30

Bitmap Heap Scan on la (cost=4.28..8.30 rows=1 width=15) (actual time=0.004..0.004 rows=1 loops=30)

  • Recheck Cond: (e_cte.lha_id = la_id)
  • Heap Blocks: exact=30
14. 0.060 0.060 ↑ 1.0 1 30

Bitmap Index Scan on la_pkey (cost=0.00..4.28 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=30)

  • Index Cond: (e_cte.lha_id = la_id)