explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KMlm

Settings
# exclusive inclusive rows x rows loops node
1. 23.286 23.286 ↓ 10.8 1,368 1

CTE Scan on tm_items (cost=9,834.26..9,836.80 rows=127 width=6,715) (actual time=0.183..23.286 rows=1,368 loops=1)

2.          

CTE live_orgs

3. 0.031 0.053 ↑ 1.0 104 1

Bitmap Heap Scan on organisation_extended_funcs (cost=5.10..11.44 rows=107 width=4) (actual time=0.030..0.053 rows=104 loops=1)

  • Recheck Cond: (extended_function_id = 14)
  • Heap Blocks: exact=5
4. 0.022 0.022 ↑ 1.0 106 1

Bitmap Index Scan on organisation_extended_funcs_pkey (cost=0.00..5.08 rows=107 width=0) (actual time=0.022..0.022 rows=106 loops=1)

  • Index Cond: (extended_function_id = 14)
5.          

CTE tm_items

6. 1.200 18.269 ↓ 10.8 1,368 1

Nested Loop Left Join (cost=4.90..9,822.82 rows=127 width=4,698) (actual time=0.173..18.269 rows=1,368 loops=1)

  • Filter: (sche.cancelled IS DISTINCT FROM true)
7. 1.014 14.333 ↓ 10.8 1,368 1

Hash Join (cost=4.47..7,352.38 rows=127 width=4,698) (actual time=0.161..14.333 rows=1,368 loops=1)

  • Hash Cond: (e_cte.publisher_organisation_id = live_org.organisation_id)
8. 0.944 13.229 ↓ 5.1 1,371 1

Nested Loop (cost=0.99..7,346.62 rows=269 width=4,702) (actual time=0.064..13.229 rows=1,371 loops=1)

9. 6.801 6.801 ↓ 5.1 1,371 1

Index Scan using idx_item_et_type_coords_datee on item i_cte (cost=0.56..5,072.22 rows=269 width=4,681) (actual time=0.052..6.801 rows=1,371 loops=1)

  • Index Cond: ((item_entity_type = 2) AND ((item_type)::text = ANY ('{RC,DC}'::text[])) AND (location_point_easting >= 385816) AND (location_point_easting <= 406245) AND (location_point_northing >= 235894) AND (location_point_northing <= 248403) AND (start_date <= (LOCALTIMESTAMP + '1 day'::interval)) AND (end_date <= LOCALTIMESTAMP))
  • Filter: active
  • Rows Removed by Filter: 133
10. 5.484 5.484 ↑ 1.0 1 1,371

Index Scan using pk_entity on entity e_cte (cost=0.44..8.46 rows=1 width=29) (actual time=0.004..0.004 rows=1 loops=1,371)

  • Index Cond: (entity_id = i_cte.entity_id)
11. 0.012 0.090 ↑ 1.0 104 1

Hash (cost=2.14..2.14 rows=107 width=4) (actual time=0.090..0.090 rows=104 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
12. 0.078 0.078 ↑ 1.0 104 1

CTE Scan on live_orgs live_org (cost=0.00..2.14 rows=107 width=4) (actual time=0.032..0.078 rows=104 loops=1)

13. 2.736 2.736 ↓ 0.0 0 1,368

Index Scan using idx_schedule_item_id on schedule sche (cost=0.42..19.31 rows=11 width=11) (actual time=0.002..0.002 rows=0 loops=1,368)

  • Index Cond: (i_cte.item_id = item_id)
  • Filter: ((actual_start_date < CURRENT_TIMESTAMP) AND (actual_end_date > CURRENT_TIMESTAMP))
Planning time : 1.599 ms
Execution time : 23.730 ms