explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tGtV

Settings
# exclusive inclusive rows x rows loops node
1. 76.433 76.433 ↓ 3.7 176 1

CTE Scan on tm_items (cost=271,523.98..271,524.94 rows=48 width=245) (actual time=9.299..76.433 rows=176 loops=1)

2.          

CTE gtab

3. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.000..0.001 rows=1 loops=1)

4.          

CTE live_orgs

5. 0.041 0.041 ↑ 1.0 128 1

Index Only Scan using organisation_extended_funcs_pkey on organisation_extended_funcs (cost=0.28..11.15 rows=128 width=4) (actual time=0.029..0.041 rows=128 loops=1)

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

CTE tm_items_nonspatial

7. 52.181 52.181 ↑ 11.2 4,412 1

Index Scan using idx_item_et_type_coords_datee on item i (cost=0.56..269,499.90 rows=49,361 width=4,719) (actual time=0.060..52.181 rows=4,412 loops=1)

  • Index Cond: ((item_entity_type = 2) AND ((item_type)::text = ANY ('{RC,DC}'::text[])) AND (start_date <= to_date('15/09/2020 00:00:00'::text, 'DD/MM/YYYY HH24:MI:SS'::text)) AND (end_date >= to_date('14/09/2020 00:00:00'::text, 'DD/MM/YYYY HH24:MI:SS'::text)))
  • Filter: active
  • Rows Removed by Filter: 140
8.          

CTE tm_items

9. 0.089 76.124 ↓ 3.7 176 1

Nested Loop Left Join (cost=5.13..2,012.92 rows=48 width=245) (actual time=9.297..76.124 rows=176 loops=1)

  • Filter: (sche.cancelled IS DISTINCT FROM true)
10. 0.100 75.507 ↓ 9.3 176 1

Hash Join (cost=4.71..1,991.07 rows=19 width=245) (actual time=9.285..75.507 rows=176 loops=1)

  • Hash Cond: (e_cte.publisher_organisation_id = live_org.organisation_id)
11. 0.213 75.321 ↓ 5.2 176 1

Nested Loop (cost=0.55..1,986.60 rows=34 width=249) (actual time=9.191..75.321 rows=176 loops=1)

12. 2.492 74.103 ↓ 4.1 201 1

Nested Loop (cost=0.00..1,604.25 rows=49 width=227) (actual time=9.178..74.103 rows=201 loops=1)

  • Join Filter: (i_cte.location_wgs84 && gtab.bbox)
  • Rows Removed by Join Filter: 4,211
13. 0.004 0.004 ↑ 1.0 1 1

CTE Scan on gtab (cost=0.00..0.02 rows=1 width=32) (actual time=0.003..0.004 rows=1 loops=1)

14. 71.607 71.607 ↑ 11.2 4,412 1

CTE Scan on tm_items_nonspatial i_cte (cost=0.00..987.22 rows=49,361 width=227) (actual time=0.065..71.607 rows=4,412 loops=1)

15. 1.005 1.005 ↑ 1.0 1 201

Index Scan using pk_entity on entity e_cte (cost=0.55..7.80 rows=1 width=30) (actual time=0.005..0.005 rows=1 loops=201)

  • Index Cond: (entity_id = i_cte.entity_id)
  • Filter: active
  • Rows Removed by Filter: 0
16. 0.016 0.086 ↑ 1.0 128 1

Hash (cost=2.56..2.56 rows=128 width=4) (actual time=0.086..0.086 rows=128 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
17. 0.070 0.070 ↑ 1.0 128 1

CTE Scan on live_orgs live_org (cost=0.00..2.56 rows=128 width=4) (actual time=0.030..0.070 rows=128 loops=1)

18. 0.528 0.528 ↓ 0.0 0 176

Index Scan using idx_schedule_item_id on schedule sche (cost=0.41..1.09 rows=3 width=11) (actual time=0.003..0.003 rows=0 loops=176)

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