explain.depesz.com

PostgreSQL's explain analyze made readable

Result: zNAD

Settings
# exclusive inclusive rows x rows loops node
1. 1.698 1.698 ↓ 1.2 5 1

CTE Scan on raw_items (cost=2,206.88..2,206.96 rows=4 width=6,716) (actual time=1.469..1.698 rows=5 loops=1)

2.          

CTE live_orgs

3. 0.031 0.051 ↓ 1.0 123 1

Bitmap Heap Scan on organisation_extended_funcs (cost=5.83..12.97 rows=122 width=4) (actual time=0.031..0.051 rows=123 loops=1)

  • Recheck Cond: ((extended_function_id = 14) AND (organisation_id >= 0) AND (organisation_id <= 9,999))
  • Heap Blocks: exact=5
4. 0.020 0.020 ↓ 1.0 123 1

Bitmap Index Scan on organisation_extended_funcs_pkey (cost=0.00..5.80 rows=122 width=0) (actual time=0.020..0.020 rows=123 loops=1)

  • Index Cond: ((extended_function_id = 14) AND (organisation_id >= 0) AND (organisation_id <= 9,999))
5.          

CTE entity_categories

6. 0.000 0.000 ↓ 0.0 0

Seq Scan on entity_category entity_category_1 (cost=3.25..7.48 rows=10 width=118) (never executed)

  • Filter: ((nsa_code)::text = ANY ((COALESCE($1))::text[]))
7.          

Initplan (for Seq Scan)

8. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=3.24..3.25 rows=1 width=32) (never executed)

9. 0.000 0.000 ↓ 0.0 0

Seq Scan on entity_category (cost=0.00..2.99 rows=99 width=118) (never executed)

10.          

CTE tm_items

11. 0.011 1.593 ↓ 1.7 5 1

Nested Loop Left Join (cost=5.53..2,042.72 rows=3 width=4,512) (actual time=1.449..1.593 rows=5 loops=1)

  • Filter: (sche.cancelled IS DISTINCT FROM true)
12. 0.379 1.562 ↓ 1.7 5 1

Nested Loop (cost=5.11..1,914.92 rows=3 width=4,512) (actual time=1.437..1.562 rows=5 loops=1)

  • Join Filter: (e_cte.publisher_organisation_id = live_org.organisation_id)
  • Rows Removed by Join Filter: 610
13. 0.076 0.076 ↓ 1.0 123 1

CTE Scan on live_orgs live_org (cost=0.00..2.44 rows=122 width=4) (actual time=0.032..0.076 rows=123 loops=1)

14. 0.101 1.107 ↑ 1.0 5 123

Materialize (cost=5.11..1,903.35 rows=5 width=4,516) (actual time=0.001..0.009 rows=5 loops=123)

15. 0.010 1.006 ↑ 1.0 5 1

Nested Loop (cost=5.11..1,903.32 rows=5 width=4,516) (actual time=0.067..1.006 rows=5 loops=1)

16. 0.948 0.948 ↑ 1.2 6 1

Index Scan using idx_item_et_type_coords_datee on item i_cte (cost=0.56..1,843.33 rows=7 width=4,495) (actual time=0.050..0.948 rows=6 loops=1)

  • Index Cond: ((item_entity_type = 2) AND ((item_type)::text = ANY ('{DR}'::text[])) AND (location_point_easting >= 303,841) AND (location_point_easting <= 327,339) AND (location_point_northing >= 518,485) AND (location_point_northing <= 543,444) AND (start_date <= to_date('05/08/2020 00:00:00'::text, 'DD/MM/YYYY HH24:MI:SS'::text)) AND (end_date >= to_date('05/08/2020 23:59:59'::text, 'DD/MM/YYYY HH24:MI:SS'::text)))
  • Filter: active
17. 0.018 0.048 ↑ 1.0 1 6

Bitmap Heap Scan on entity e_cte (cost=4.56..8.57 rows=1 width=29) (actual time=0.008..0.008 rows=1 loops=6)

  • Recheck Cond: (entity_id = i_cte.entity_id)
  • Filter: active
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=6
18. 0.030 0.030 ↑ 1.0 1 6

Bitmap Index Scan on pk_entity (cost=0.00..4.56 rows=1 width=0) (actual time=0.005..0.005 rows=1 loops=6)

  • Index Cond: (entity_id = i_cte.entity_id)
19. 0.020 0.020 ↓ 0.0 0 5

Index Scan using idx_schedule_item_id on schedule sche (cost=0.42..42.55 rows=3 width=11) (actual time=0.004..0.004 rows=0 loops=5)

  • Index Cond: (i_cte.item_id = item_id)
  • Filter: ((actual_start_date < CURRENT_TIMESTAMP) AND (actual_end_date > CURRENT_TIMESTAMP))
20.          

CTE noms_items

21. 0.000 0.040 ↓ 0.0 0 1

Nested Loop Left Join (cost=5.53..143.64 rows=1 width=4,512) (actual time=0.040..0.040 rows=0 loops=1)

  • Filter: (sche_1.cancelled IS DISTINCT FROM true)
22. 0.000 0.040 ↓ 0.0 0 1

Nested Loop (cost=5.11..101.04 rows=1 width=4,512) (actual time=0.040..0.040 rows=0 loops=1)

  • Join Filter: ((e_cte_1.entity_category)::text = (ec.entity_category)::text)
23. 0.000 0.040 ↓ 0.0 0 1

Nested Loop (cost=5.11..100.71 rows=1 width=4,520) (actual time=0.040..0.040 rows=0 loops=1)

24. 0.040 0.040 ↓ 0.0 0 1

Index Scan using idx_item_et_type_coords_datee on item i_cte_1 (cost=0.56..92.14 rows=1 width=4,495) (actual time=0.040..0.040 rows=0 loops=1)

  • Index Cond: ((item_entity_type = 5) AND ((item_type)::text = ANY ('{RC,DR,HDR,O,BC}'::text[])) AND (location_point_easting >= 303,841) AND (location_point_easting <= 327,339) AND (location_point_northing >= 518,485) AND (location_point_northing <= 543,444) AND (start_date <= to_date('05/08/2020 00:00:00'::text, 'DD/MM/YYYY HH24:MI:SS'::text)) AND (end_date >= to_date('05/08/2020 23:59:59'::text, 'DD/MM/YYYY HH24:MI:SS'::text)))
  • Filter: active
25. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on entity e_cte_1 (cost=4.56..8.57 rows=1 width=33) (never executed)

  • Recheck Cond: (entity_id = i_cte_1.entity_id)
  • Filter: (active AND (publisher_orgref = 11))
26. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on pk_entity (cost=0.00..4.56 rows=1 width=0) (never executed)

  • Index Cond: (entity_id = i_cte_1.entity_id)
27. 0.000 0.000 ↓ 0.0 0

CTE Scan on entity_categories ec (cost=0.00..0.20 rows=10 width=118) (never executed)

28. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_schedule_item_id on schedule sche_1 (cost=0.42..42.55 rows=3 width=11) (never executed)

  • Index Cond: (i_cte_1.item_id = item_id)
  • Filter: ((actual_start_date < CURRENT_TIMESTAMP) AND (actual_end_date > CURRENT_TIMESTAMP))
29.          

CTE raw_items

30. 0.001 1.676 ↓ 1.2 5 1

Append (cost=0.00..0.08 rows=4 width=6,716) (actual time=1.460..1.676 rows=5 loops=1)

31. 1.634 1.634 ↓ 1.7 5 1

CTE Scan on tm_items (cost=0.00..0.06 rows=3 width=6,716) (actual time=1.460..1.634 rows=5 loops=1)

32. 0.041 0.041 ↓ 0.0 0 1

CTE Scan on noms_items (cost=0.00..0.02 rows=1 width=6,716) (actual time=0.041..0.041 rows=0 loops=1)

Planning time : 3.785 ms
Execution time : 2.219 ms