explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TQX1

Settings
# exclusive inclusive rows x rows loops node
1. 0.074 1,432.227 ↑ 1.0 1 1

Nested Loop Left Join (cost=1,698.83..1,736.53 rows=1 width=1,351) (actual time=1,432.185..1,432.227 rows=1 loops=1)

  • Join Filter: (tti.item_id = i.item_id)
2. 0.011 1,432.124 ↑ 1.0 1 1

Nested Loop Left Join (cost=1,698.41..1,719.40 rows=1 width=695) (actual time=1,432.083..1,432.124 rows=1 loops=1)

  • Join Filter: (i.item_id = (8709975))
3. 0.002 0.219 ↑ 1.0 1 1

Nested Loop Left Join (cost=23.65..44.61 rows=1 width=679) (actual time=0.179..0.219 rows=1 loops=1)

4. 0.004 0.213 ↑ 1.0 1 1

Nested Loop Left Join (cost=23.51..41.57 rows=1 width=641) (actual time=0.173..0.213 rows=1 loops=1)

5. 0.004 0.204 ↑ 1.0 1 1

Nested Loop Left Join (cost=23.23..34.98 rows=1 width=598) (actual time=0.168..0.204 rows=1 loops=1)

6. 0.001 0.194 ↑ 1.0 1 1

Nested Loop Left Join (cost=22.96..28.40 rows=1 width=555) (actual time=0.160..0.194 rows=1 loops=1)

  • Join Filter: ((e.entity_category)::text = (nsa_lang.tm_nsa_type_id)::text)
7. 0.030 0.183 ↑ 1.0 1 1

Hash Join (cost=22.96..26.75 rows=1 width=527) (actual time=0.149..0.183 rows=1 loops=1)

  • Hash Cond: ((tmit.item_type)::text = (i.item_type)::text)
8. 0.022 0.022 ↑ 1.0 57 1

Seq Scan on item_type tmit (cost=0.00..3.57 rows=57 width=36) (actual time=0.005..0.022 rows=57 loops=1)

9. 0.002 0.131 ↑ 1.0 1 1

Hash (cost=22.95..22.95 rows=1 width=494) (actual time=0.131..0.131 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
10. 0.037 0.129 ↑ 1.0 1 1

Hash Join (cost=19.68..22.95 rows=1 width=494) (actual time=0.096..0.129 rows=1 loops=1)

  • Hash Cond: ((ec.nsa_code)::text = (e.entity_category)::text)
11. 0.018 0.018 ↑ 1.0 91 1

Seq Scan on entity_category ec (cost=0.00..2.91 rows=91 width=29) (actual time=0.002..0.018 rows=91 loops=1)

12. 0.003 0.074 ↑ 1.0 1 1

Hash (cost=19.67..19.67 rows=1 width=472) (actual time=0.074..0.074 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
13. 0.031 0.071 ↑ 1.0 1 1

Hash Right Join (cost=16.93..19.67 rows=1 width=472) (actual time=0.057..0.071 rows=1 loops=1)

  • Hash Cond: ((nsa.tm_nsa_type_id)::text = (e.entity_category)::text)
14. 0.009 0.009 ↑ 1.0 53 1

Seq Scan on tm_nsa nsa (cost=0.00..2.53 rows=53 width=35) (actual time=0.001..0.009 rows=53 loops=1)

15. 0.005 0.031 ↑ 1.0 1 1

Hash (cost=16.92..16.92 rows=1 width=445) (actual time=0.031..0.031 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
16. 0.005 0.026 ↑ 1.0 1 1

Nested Loop (cost=0.88..16.92 rows=1 width=445) (actual time=0.024..0.026 rows=1 loops=1)

17. 0.012 0.012 ↑ 1.0 1 1

Index Scan using idx_item_item_id on item i (cost=0.44..8.46 rows=1 width=216) (actual time=0.012..0.012 rows=1 loops=1)

  • Index Cond: (item_id = 8709975)
  • Filter: active
18. 0.009 0.009 ↑ 1.0 1 1

Index Scan using idx_entity_id on entity e (cost=0.44..8.46 rows=1 width=237) (actual time=0.008..0.009 rows=1 loops=1)

  • Index Cond: (entity_id = i.entity_id)
  • Filter: active
19. 0.010 0.010 ↓ 0.0 0 1

Seq Scan on tm_nsa_lang nsa_lang (cost=0.00..1.64 rows=1 width=36) (actual time=0.010..0.010 rows=0 loops=1)

  • Filter: ((code)::text = 'en'::text)
  • Rows Removed by Filter: 51
20. 0.006 0.006 ↑ 1.0 1 1

Index Scan using pk_orgref on orgref (cost=0.28..6.57 rows=1 width=47) (actual time=0.006..0.006 rows=1 loops=1)

  • Index Cond: (e.responsible_org_orgref = swa_org_ref)
21. 0.005 0.005 ↑ 1.0 1 1

Index Scan using pk_orgref on orgref orgref2 (cost=0.28..6.57 rows=1 width=47) (actual time=0.004..0.005 rows=1 loops=1)

  • Index Cond: (e.publisher_orgref = swa_org_ref)
22. 0.004 0.004 ↓ 0.0 0 1

Index Scan using pk_entity_category_code on entity_category_lang ec_lang (cost=0.14..3.04 rows=1 width=45) (actual time=0.004..0.004 rows=0 loops=1)

  • Index Cond: (((code)::text = 'en'::text) AND ((e.entity_category)::text = (nsa_code)::text))
23. 0.005 1,431.894 ↑ 1.0 1 1

Result (cost=1,674.75..1,674.76 rows=1 width=20) (actual time=1,431.894..1,431.894 rows=1 loops=1)

24.          

Initplan (forResult)

25. 0.002 743.438 ↓ 0.0 0 1

Limit (cost=0.43..837.38 rows=1 width=8) (actual time=743.438..743.438 rows=0 loops=1)

26. 743.436 743.436 ↓ 0.0 0 1

Index Scan Backward using idx_tomtom_item_impact_hourly_timestamp on tomtom_item_impact_hourly_agg (cost=0.43..185,802.02 rows=222 width=8) (actual time=743.436..743.436 rows=0 loops=1)

  • Index Cond: ("timestamp" IS NOT NULL)
  • Filter: (item_id = 8709975)
  • Rows Removed by Filter: 4250374
27. 0.004 688.451 ↓ 0.0 0 1

Limit (cost=0.43..837.38 rows=1 width=8) (actual time=688.451..688.451 rows=0 loops=1)

28. 688.447 688.447 ↓ 0.0 0 1

Index Scan using idx_tomtom_item_impact_hourly_timestamp on tomtom_item_impact_hourly_agg tomtom_item_impact_hourly_agg_1 (cost=0.43..185,802.02 rows=222 width=8) (actual time=688.447..688.447 rows=0 loops=1)

  • Index Cond: ("timestamp" IS NOT NULL)
  • Filter: (item_id = 8709975)
  • Rows Removed by Filter: 4250374
29. 0.011 0.011 ↓ 0.0 0 1

Index Scan using idx_tomtom_max_impact_xref_item_id_item_id on tomtom_max_impact_xref_item_id tti (cost=0.42..8.44 rows=1 width=10) (actual time=0.011..0.011 rows=0 loops=1)

  • Index Cond: (item_id = 8709975)
30.          

SubPlan (forNested Loop Left Join)

31. 0.003 0.018 ↑ 1.0 1 1

Limit (cost=0.00..8.60 rows=1 width=4) (actual time=0.017..0.018 rows=1 loops=1)

32. 0.015 0.015 ↑ 1.0 1 1

Seq Scan on organisation_extended_funcs extfuncs (cost=0.00..8.60 rows=1 width=4) (actual time=0.015..0.015 rows=1 loops=1)

  • Filter: ((e.publisher_organisation_id = organisation_id) AND (extended_function_id = ANY ('{14,15,16}'::integer[])))
  • Rows Removed by Filter: 21