explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OSPk

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

CTE Scan on tm_items (cost=271,523.98..271,524.94 rows=48 width=245) (actual time=11.434..92.735 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.043 0.043 ↑ 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.043 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. 62.556 62.556 ↑ 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.040..62.556 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.228 92.348 ↓ 3.7 176 1

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

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

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

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

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

12. 3.181 89.747 ↓ 4.1 201 1

Nested Loop (cost=0.00..1,604.25 rows=49 width=227) (actual time=11.291..89.747 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. 86.562 86.562 ↑ 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.045..86.562 rows=4,412 loops=1)

15. 1.407 1.407 ↑ 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.007..0.007 rows=1 loops=201)

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
17. 0.079 0.079 ↑ 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.079 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.508 ms
Execution time : 94.593 ms