explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Fb7JT

Settings
# exclusive inclusive rows x rows loops node
1. 90.783 90.783 ↓ 9.3 176 1

CTE Scan on tm_items (cost=820.75..821.13 rows=19 width=245) (actual time=0.539..90.783 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.038 0.038 ↑ 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.027..0.038 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

7. 0.279 90.350 ↓ 9.3 176 1

Nested Loop Left Join (cost=5.54..809.58 rows=19 width=842) (actual time=0.535..90.350 rows=176 loops=1)

  • Filter: (sche.cancelled IS DISTINCT FROM true)
8. 0.140 89.543 ↓ 9.3 176 1

Hash Join (cost=5.12..787.70 rows=19 width=842) (actual time=0.524..89.543 rows=176 loops=1)

  • Hash Cond: (e_cte.publisher_organisation_id = live_org.organisation_id)
9. 0.344 89.319 ↓ 5.2 176 1

Nested Loop (cost=0.96..783.22 rows=34 width=846) (actual time=0.433..89.319 rows=176 loops=1)

10. 0.065 87.769 ↓ 4.2 201 1

Nested Loop (cost=0.41..407.70 rows=48 width=824) (actual time=0.415..87.769 rows=201 loops=1)

11. 0.002 0.002 ↑ 1.0 1 1

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

12. 87.702 87.702 ↓ 40.2 201 1

Index Scan using sidx_item_location_wgs84 on item i_cte (cost=0.41..407.63 rows=5 width=824) (actual time=0.408..87.702 rows=201 loops=1)

  • Index Cond: (location_wgs84 && gtab.bbox)
  • Filter: (active AND ((item_type)::text = ANY ('{RC,DC}'::text[])) AND (item_entity_type = 2) 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)))
  • Rows Removed by Filter: 56,406
13. 1.206 1.206 ↑ 1.0 1 201

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

  • Index Cond: (entity_id = i_cte.entity_id)
  • Filter: active
  • Rows Removed by Filter: 0
14. 0.019 0.084 ↑ 1.0 128 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
15. 0.065 0.065 ↑ 1.0 128 1

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

16. 0.528 0.528 ↓ 0.0 0 176

Index Scan using idx_schedule_item_id on schedule sche (cost=0.41..1.11 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.476 ms
Execution time : 90.943 ms