explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DjhK

Settings
# exclusive inclusive rows x rows loops node
1. 88.755 88.755 ↓ 88.0 176 1

CTE Scan on geom_data (cost=832.89..832.93 rows=2 width=406) (actual time=87.035..88.755 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.039 0.039 ↑ 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.039 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.226 86.022 ↓ 9.3 176 1

Nested Loop Left Join (cost=5.54..809.78 rows=19 width=843) (actual time=0.534..86.022 rows=176 loops=1)

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

Hash Join (cost=5.12..787.86 rows=19 width=843) (actual time=0.520..85.268 rows=176 loops=1)

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

Nested Loop (cost=0.96..783.38 rows=34 width=847) (actual time=0.426..85.060 rows=176 loops=1)

10. 0.059 83.527 ↓ 4.2 201 1

Nested Loop (cost=0.41..407.70 rows=48 width=825) (actual time=0.409..83.527 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. 83.466 83.466 ↓ 40.2 201 1

Index Scan using sidx_item_location_wgs84 on item i_cte (cost=0.41..407.63 rows=5 width=825) (actual time=0.404..83.466 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,403
13. 1.407 1.407 ↑ 1.0 1 201

Index Scan using pk_entity on entity e_cte (cost=0.55..7.83 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.021 0.087 ↑ 1.0 128 1

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

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

CTE Scan on live_orgs live_org (cost=0.00..2.56 rows=128 width=4) (actual time=0.029..0.066 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.031 86.389 ↓ 8.8 176 1

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

21. 86.357 86.357 ↓ 9.3 176 1

CTE Scan on tm_items (cost=0.00..0.38 rows=19 width=245) (actual time=0.536..86.357 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)

23.          

CTE tm_entities

24. 0.579 88.191 ↓ 176.0 176 1

Nested Loop Left Join (cost=0.93..11.51 rows=1 width=446) (actual time=87.028..88.191 rows=176 loops=1)

  • Join Filter: ((i.item_type)::text = (itemtype1_lang.item_type)::text)
25. 0.094 87.084 ↓ 176.0 176 1

Hash Join (cost=0.66..4.83 rows=1 width=255) (actual time=87.002..87.084 rows=176 loops=1)

  • Hash Cond: ((itemtype1.item_type)::text = (i.item_type)::text)
26. 0.013 0.013 ↑ 1.0 84 1

Seq Scan on item_type itemtype1 (cost=0.00..3.84 rows=84 width=28) (actual time=0.004..0.013 rows=84 loops=1)

27. 0.252 86.977 ↓ 176.0 176 1

Hash (cost=0.65..0.65 rows=1 width=230) (actual time=86.977..86.977 rows=176 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 107kB
28. 86.725 86.725 ↓ 176.0 176 1

CTE Scan on raw_items i (cost=0.00..0.65 rows=1 width=230) (actual time=0.540..86.725 rows=176 loops=1)

  • Filter: ((published = ANY ('{0,1}'::integer[])) AND (tm__sw_cancelled_status = 0) AND (publish_date <= (CURRENT_DATE + '3650 days'::interval day)))
29. 0.528 0.528 ↓ 0.0 0 176

Index Scan using pk_item_type_lang_code on item_type_lang itemtype1_lang (cost=0.27..6.44 rows=1 width=22) (actual time=0.003..0.003 rows=0 loops=176)

  • Index Cond: ((code)::text = 'en'::text)
30.          

CTE geom_data

31. 0.018 88.566 ↓ 88.0 176 1

Append (cost=0.00..0.05 rows=2 width=406) (actual time=87.032..88.566 rows=176 loops=1)

32. 88.502 88.502 ↓ 176.0 176 1

CTE Scan on tm_entities d (cost=0.00..0.02 rows=1 width=406) (actual time=87.032..88.502 rows=176 loops=1)

33. 0.046 0.046 ↓ 0.0 0 1

CTE Scan on tm_entities d_1 (cost=0.00..0.02 rows=1 width=406) (actual time=0.046..0.046 rows=0 loops=1)

  • Filter: (location_wgs84 IS NULL)
  • Rows Removed by Filter: 176
Planning time : 2.843 ms
Execution time : 89.117 ms