explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NryR

Settings
# exclusive inclusive rows x rows loops node
1. 94.290 94.290 ↓ 8.8 176 1

CTE Scan on raw_items (cost=825.06..825.46 rows=20 width=245) (actual time=0.740..94.290 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.001..0.001 rows=1 loops=1)

4.          

CTE live_orgs

5. 0.062 0.062 ↑ 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.042..0.062 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.290 93.594 ↓ 9.3 176 1

Nested Loop Left Join (cost=5.54..813.51 rows=19 width=843) (actual time=0.734..93.594 rows=176 loops=1)

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

Hash Join (cost=5.12..791.65 rows=19 width=843) (actual time=0.720..92.776 rows=176 loops=1)

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

Nested Loop (cost=0.96..787.18 rows=34 width=847) (actual time=0.571..92.495 rows=176 loops=1)

10. 0.068 90.901 ↓ 4.2 201 1

Nested Loop (cost=0.41..411.73 rows=48 width=825) (actual time=0.553..90.901 rows=201 loops=1)

11. 0.003 0.003 ↑ 1.0 1 1

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

12. 90.830 90.830 ↓ 40.2 201 1

Index Scan using sidx_item_location_wgs84 on item i_cte (cost=0.41..411.66 rows=5 width=825) (actual time=0.547..90.830 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,405
13. 1.407 1.407 ↑ 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.007..0.007 rows=1 loops=201)

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

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

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

CTE Scan on live_orgs live_org (cost=0.00..2.56 rows=128 width=4) (actual time=0.043..0.109 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))
17.          

CTE noms_items

18. 0.000 0.000 ↓ 0.0 0 1

Result (cost=0.00..0.00 rows=0 width=843) (actual time=0.000..0.000 rows=0 loops=1)

  • One-Time Filter: false
19.          

CTE raw_items

20. 0.034 94.040 ↓ 8.8 176 1

Append (cost=0.00..0.38 rows=20 width=245) (actual time=0.739..94.040 rows=176 loops=1)

21. 94.005 94.005 ↓ 9.3 176 1

CTE Scan on tm_items (cost=0.00..0.38 rows=19 width=245) (actual time=0.739..94.005 rows=176 loops=1)

22. 0.001 0.001 ↓ 0.0 0 1

CTE Scan on noms_items (cost=0.00..0.00 rows=1 width=245) (actual time=0.001..0.001 rows=0 loops=1)

Planning time : 2.999 ms
Execution time : 94.588 ms