explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tE2F

Settings
# exclusive inclusive rows x rows loops node
1. 1.864 1.864 ↑ 1.4 9 1

CTE Scan on raw_items (cost=1,282.66..1,282.92 rows=13 width=6,716) (actual time=0.594..1.864 rows=9 loops=1)

2.          

CTE live_orgs

3. 0.030 0.051 ↓ 1.0 123 1

Bitmap Heap Scan on organisation_extended_funcs (cost=5.83..12.97 rows=122 width=4) (actual time=0.032..0.051 rows=123 loops=1)

  • Recheck Cond: ((extended_function_id = 14) AND (organisation_id >= 0) AND (organisation_id <= 9,999))
  • Heap Blocks: exact=5
4. 0.021 0.021 ↓ 1.0 123 1

Bitmap Index Scan on organisation_extended_funcs_pkey (cost=0.00..5.80 rows=122 width=0) (actual time=0.021..0.021 rows=123 loops=1)

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

CTE gtab

6. 0.070 0.070 ↑ 1.0 1 1

Result (cost=0.00..0.26 rows=1 width=32) (actual time=0.070..0.070 rows=1 loops=1)

7.          

CTE entity_categories

8. 0.110 0.147 ↓ 9.9 99 1

Seq Scan on entity_category entity_category_1 (cost=3.25..7.48 rows=10 width=118) (actual time=0.045..0.147 rows=99 loops=1)

  • Filter: ((nsa_code)::text = ANY ((COALESCE($2))::text[]))
9.          

Initplan (for Seq Scan)

10. 0.027 0.037 ↑ 1.0 1 1

Aggregate (cost=3.24..3.25 rows=1 width=32) (actual time=0.037..0.037 rows=1 loops=1)

11. 0.010 0.010 ↑ 1.0 99 1

Seq Scan on entity_category (cost=0.00..2.99 rows=99 width=118) (actual time=0.003..0.010 rows=99 loops=1)

12.          

CTE tm_items

13. 0.013 1.106 ↑ 1.5 8 1

Nested Loop Left Join (cost=8.80..760.91 rows=12 width=4,571) (actual time=0.581..1.106 rows=8 loops=1)

  • Filter: (sche.cancelled IS DISTINCT FROM true)
14. 0.015 1.061 ↑ 1.5 8 1

Hash Join (cost=8.39..744.77 rows=12 width=4,571) (actual time=0.572..1.061 rows=8 loops=1)

  • Hash Cond: (e_cte.publisher_organisation_id = live_org.organisation_id)
15. 0.008 0.949 ↑ 2.8 8 1

Nested Loop (cost=4.42..740.60 rows=22 width=4,575) (actual time=0.467..0.949 rows=8 loops=1)

16. 0.008 0.860 ↑ 3.7 9 1

Nested Loop (cost=0.41..475.80 rows=33 width=4,554) (actual time=0.310..0.860 rows=9 loops=1)

17. 0.072 0.072 ↑ 1.0 1 1

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

18. 0.780 0.780 ↓ 3.0 9 1

Index Scan using sidx_item_location_bng on item i_cte (cost=0.41..475.75 rows=3 width=4,554) (actual time=0.237..0.780 rows=9 loops=1)

  • Index Cond: (location_bng && gtab.bbox)
  • Filter: (active AND ((item_type)::text = ANY ('{DR}'::text[])) AND (item_entity_type = 2) AND (start_date <= to_date('05/08/2020 00:00:00'::text, 'DD/MM/YYYY HH24:MI:SS'::text)) AND (end_date >= to_date('05/08/2020 23:59:59'::text, 'DD/MM/YYYY HH24:MI:SS'::text)))
  • Rows Removed by Filter: 430
19. 0.027 0.081 ↑ 1.0 1 9

Bitmap Heap Scan on entity e_cte (cost=4.01..8.02 rows=1 width=29) (actual time=0.009..0.009 rows=1 loops=9)

  • Recheck Cond: (entity_id = i_cte.entity_id)
  • Filter: active
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=9
20. 0.054 0.054 ↑ 1.0 1 9

Bitmap Index Scan on pk_entity (cost=0.00..4.01 rows=1 width=0) (actual time=0.006..0.006 rows=1 loops=9)

  • Index Cond: (entity_id = i_cte.entity_id)
21. 0.021 0.097 ↓ 1.0 123 1

Hash (cost=2.44..2.44 rows=122 width=4) (actual time=0.097..0.097 rows=123 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
22. 0.076 0.076 ↓ 1.0 123 1

CTE Scan on live_orgs live_org (cost=0.00..2.44 rows=122 width=4) (actual time=0.033..0.076 rows=123 loops=1)

23. 0.032 0.032 ↓ 0.0 0 8

Index Scan using idx_schedule_item_id on schedule sche (cost=0.42..1.30 rows=3 width=11) (actual time=0.004..0.004 rows=0 loops=8)

  • Index Cond: (i_cte.item_id = item_id)
  • Filter: ((actual_start_date < CURRENT_TIMESTAMP) AND (actual_end_date > CURRENT_TIMESTAMP))
24.          

CTE noms_items

25. 0.002 0.656 ↑ 1.0 1 1

Nested Loop Left Join (cost=5.36..500.80 rows=1 width=4,571) (actual time=0.242..0.656 rows=1 loops=1)

  • Filter: (sche_1.cancelled IS DISTINCT FROM true)
26. 0.009 0.647 ↑ 1.0 1 1

Nested Loop (cost=4.95..485.20 rows=1 width=4,571) (actual time=0.233..0.647 rows=1 loops=1)

  • Join Filter: ((e_cte_1.entity_category)::text = (ec.entity_category)::text)
  • Rows Removed by Join Filter: 98
27. 0.002 0.467 ↑ 1.0 1 1

Nested Loop (cost=4.95..484.87 rows=1 width=4,579) (actual time=0.186..0.467 rows=1 loops=1)

28. 0.005 0.456 ↑ 1.0 1 1

Nested Loop (cost=0.41..476.32 rows=1 width=4,554) (actual time=0.175..0.456 rows=1 loops=1)

29. 0.001 0.001 ↑ 1.0 1 1

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

30. 0.450 0.450 ↑ 1.0 1 1

Index Scan using sidx_item_location_bng on item i_cte_1 (cost=0.41..476.29 rows=1 width=4,554) (actual time=0.170..0.450 rows=1 loops=1)

  • Index Cond: (location_bng && gtab_1.bbox)
  • Filter: (active AND (item_entity_type = 5) AND (start_date <= to_date('05/08/2020 00:00:00'::text, 'DD/MM/YYYY HH24:MI:SS'::text)) AND (end_date >= to_date('05/08/2020 23:59:59'::text, 'DD/MM/YYYY HH24:MI:SS'::text)) AND ((item_type)::text = ANY ('{RC,DR,HDR,O,BC}'::text[])))
  • Rows Removed by Filter: 438
31. 0.004 0.009 ↑ 1.0 1 1

Bitmap Heap Scan on entity e_cte_1 (cost=4.54..8.55 rows=1 width=33) (actual time=0.009..0.009 rows=1 loops=1)

  • Recheck Cond: (entity_id = i_cte_1.entity_id)
  • Filter: (active AND (publisher_orgref = 11))
  • Heap Blocks: exact=1
32. 0.005 0.005 ↑ 1.0 1 1

Bitmap Index Scan on pk_entity (cost=0.00..4.54 rows=1 width=0) (actual time=0.005..0.005 rows=1 loops=1)

  • Index Cond: (entity_id = i_cte_1.entity_id)
33. 0.171 0.171 ↓ 9.9 99 1

CTE Scan on entity_categories ec (cost=0.00..0.20 rows=10 width=118) (actual time=0.046..0.171 rows=99 loops=1)

34. 0.007 0.007 ↓ 0.0 0 1

Index Scan using idx_schedule_item_id on schedule sche_1 (cost=0.42..15.55 rows=3 width=11) (actual time=0.007..0.007 rows=0 loops=1)

  • Index Cond: (i_cte_1.item_id = item_id)
  • Filter: ((actual_start_date < CURRENT_TIMESTAMP) AND (actual_end_date > CURRENT_TIMESTAMP))
  • Rows Removed by Filter: 2
35.          

CTE raw_items

36. 0.002 1.828 ↑ 1.4 9 1

Append (cost=0.00..0.26 rows=13 width=6,716) (actual time=0.591..1.828 rows=9 loops=1)

37. 1.165 1.165 ↑ 1.5 8 1

CTE Scan on tm_items (cost=0.00..0.24 rows=12 width=6,716) (actual time=0.591..1.165 rows=8 loops=1)

38. 0.661 0.661 ↑ 1.0 1 1

CTE Scan on noms_items (cost=0.00..0.02 rows=1 width=6,716) (actual time=0.247..0.661 rows=1 loops=1)

Planning time : 3.861 ms
Execution time : 2.474 ms