explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xvOR

Settings
# exclusive inclusive rows x rows loops node
1. 144.751 921.094 ↓ 7.2 80,196 1

Merge Left Join (cost=14,447.46..15,383.53 rows=11,121 width=87) (actual time=704.146..921.094 rows=80,196 loops=1)

  • Merge Cond: ((entityindicators.entity_id = iv.entity_id) AND (entity_indicator_dimensions.dimension_id = iv.dimension_id) AND (entityindicators.indicator_id = iv.indicator_id))
2. 24.321 94.537 ↓ 1.0 11,453 1

Sort (cost=1,476.51..1,504.31 rows=11,121 width=12) (actual time=85.967..94.537 rows=11,453 loops=1)

  • Sort Key: entityindicators.entity_id, entity_indicator_dimensions.dimension_id, entityindicators.indicator_id
  • Sort Method: quicksort Memory: 921kB
3. 20.179 70.216 ↓ 1.0 11,453 1

Hash Join (cost=458.77..729.12 rows=11,121 width=12) (actual time=24.834..70.216 rows=11,453 loops=1)

  • Hash Cond: ((entity_indicators.id = entity_indicator_dimensions.entity_indicator_id) AND (entity_dimensions.dimension_id = entity_indicator_dimensions.dimension_id))
4. 11.833 29.977 ↑ 1.0 11,453 1

Hash Join (cost=99.44..309.63 rows=11,458 width=20) (actual time=4.702..29.977 rows=11,453 loops=1)

  • Hash Cond: (entity_indicators.entity_id = entity_dimensions.entity_id)
5. 4.142 18.033 ↑ 1.0 2,378 1

Hash Join (cost=97.23..172.02 rows=2,379 width=20) (actual time=4.583..18.033 rows=2,378 loops=1)

  • Hash Cond: (entityindicators.id = entity_indicators.id)
6. 3.860 10.076 ↑ 1.0 2,378 1

Hash Join (cost=25.73..94.26 rows=2,379 width=12) (actual time=0.746..10.076 rows=2,378 loops=1)

  • Hash Cond: (entityindicators.indicator_id = indicators.id)
7. 3.347 5.490 ↑ 1.0 2,378 1

Append (cost=0.00..62.22 rows=2,379 width=12) (actual time=0.007..5.490 rows=2,378 loops=1)

8. 2.092 2.092 ↑ 1.0 2,378 1

Seq Scan on entity_indicators entityindicators (cost=0.00..41.78 rows=2,378 width=12) (actual time=0.006..2.092 rows=2,378 loops=1)

9. 0.001 0.051 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=7.19..8.55 rows=1 width=12) (actual time=0.051..0.051 rows=0 loops=1)

10. 0.014 0.050 ↓ 0.0 0 1

Hash Join (cost=7.19..8.54 rows=1 width=46) (actual time=0.048..0.050 rows=0 loops=1)

  • Hash Cond: (entity_units.id = entityunitindicators.entity_unit_id)
11. 0.014 0.014 ↑ 25.0 1 1

Seq Scan on entity_units (cost=0.00..1.25 rows=25 width=8) (actual time=0.014..0.014 rows=1 loops=1)

12. 0.004 0.022 ↓ 0.0 0 1

Hash (cost=7.17..7.17 rows=1 width=12) (actual time=0.021..0.022 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
13. 0.018 0.018 ↓ 0.0 0 1

Index Scan using idx_entity_unit_indicators_entity_unit_id on entity_unit_indicators entityunitindicators (cost=0.28..7.17 rows=1 width=12) (actual time=0.018..0.018 rows=0 loops=1)

  • Index Cond: (entity_unit_id IS NULL)
14. 0.308 0.726 ↑ 1.0 389 1

Hash (cost=20.86..20.86 rows=389 width=4) (actual time=0.726..0.726 rows=389 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 22kB
15. 0.418 0.418 ↑ 1.0 389 1

Seq Scan on indicators (cost=0.00..20.86 rows=389 width=4) (actual time=0.037..0.418 rows=389 loops=1)

  • Filter: (status_id <> 4)
16. 1.958 3.815 ↑ 1.0 2,378 1

Hash (cost=41.78..41.78 rows=2,378 width=8) (actual time=3.814..3.815 rows=2,378 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 125kB
17. 1.857 1.857 ↑ 1.0 2,378 1

Seq Scan on entity_indicators (cost=0.00..41.78 rows=2,378 width=8) (actual time=0.007..1.857 rows=2,378 loops=1)

18. 0.054 0.111 ↑ 1.0 54 1

Hash (cost=1.54..1.54 rows=54 width=8) (actual time=0.110..0.111 rows=54 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
19. 0.057 0.057 ↑ 1.0 54 1

Seq Scan on entity_dimensions (cost=0.00..1.54 rows=54 width=8) (actual time=0.015..0.057 rows=54 loops=1)

  • Filter: active
20. 10.310 20.060 ↑ 1.0 11,453 1

Hash (cost=187.53..187.53 rows=11,453 width=8) (actual time=20.059..20.060 rows=11,453 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 576kB
21. 9.750 9.750 ↑ 1.0 11,453 1

Seq Scan on entity_indicator_dimensions (cost=0.00..187.53 rows=11,453 width=8) (actual time=0.011..9.750 rows=11,453 loops=1)

22. 438.637 681.806 ↑ 1.0 82,073 1

Sort (cost=12,970.94..13,176.87 rows=82,371 width=87) (actual time=618.165..681.806 rows=82,073 loops=1)

  • Sort Key: iv.entity_id, iv.dimension_id, iv.indicator_id
  • Sort Method: quicksort Memory: 14,614kB
23. 112.627 243.169 ↑ 1.0 82,073 1

Append (cost=0.00..6,245.42 rows=82,371 width=87) (actual time=0.022..243.169 rows=82,073 loops=1)

24. 70.525 70.525 ↑ 1.0 41,757 1

Seq Scan on indicators_values_2019 iv (cost=0.00..3,327.76 rows=42,165 width=87) (actual time=0.020..70.525 rows=41,757 loops=1)

  • Filter: ((entity_unit_id IS NULL) AND (year >= 2,019) AND (year <= 2,020) AND (year_month >= '201907'::numeric) AND (year_month <= '202007'::numeric))
  • Rows Removed by Filter: 56,481
25. 60.017 60.017 ↓ 1.0 40,316 1

Seq Scan on indicators_values_2020 iv_1 (cost=0.00..2,505.80 rows=40,206 width=87) (actual time=0.017..60.017 rows=40,316 loops=1)

  • Filter: ((entity_unit_id IS NULL) AND (year >= 2,019) AND (year <= 2,020) AND (year_month >= '201907'::numeric) AND (year_month <= '202007'::numeric))
  • Rows Removed by Filter: 33,678