explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mNAq

Settings
# exclusive inclusive rows x rows loops node
1. 0.018 11,760.985 ↑ 1.0 15 1

Limit (cost=111.93..679.64 rows=15 width=1,401) (actual time=6.958..11,760.985 rows=15 loops=1)

2. 11,753.497 11,760.967 ↑ 8.5 15 1

Result (cost=111.93..4,956.41 rows=128 width=1,401) (actual time=6.957..11,760.967 rows=15 loops=1)

3. 0.347 4.245 ↑ 8.5 15 1

Sort (cost=111.93..112.25 rows=128 width=1,209) (actual time=4.236..4.245 rows=15 loops=1)

  • Sort Key: (lower(businessobjects.businessobject_view_id))
  • Sort Method: top-N heapsort Memory: 63kB
4. 1.124 3.898 ↑ 2.3 56 1

Hash Left Join (cost=19.31..108.79 rows=128 width=1,209) (actual time=1.721..3.898 rows=56 loops=1)

  • Hash Cond: (('H_'::text || dvb_core.f_string_between(businessobjects.businessobject_view_id, '.'::text, '_S_'::text, false, false)) = hubs.hub_id)
5. 1.086 2.724 ↑ 2.3 56 1

Hash Right Join (cost=14.10..100.44 rows=128 width=1,106) (actual time=1.642..2.724 rows=56 loops=1)

  • Hash Cond: (systems.system_id = COALESCE(dvb_core.f_string_between(businessobjects.businessobject_view_id, '_S_'::text, '_C_'::text, false, false), dvb_core.f_string_between(businessobjects.businessobject_view_id, '_S_'::text, ''::text, false, false)))
6. 0.006 0.041 ↑ 26.1 20 1

Append (cost=0.00..17.85 rows=522 width=64) (actual time=0.016..0.041 rows=20 loops=1)

7. 0.016 0.016 ↑ 28.9 18 1

Seq Scan on systems (cost=0.00..15.20 rows=520 width=64) (actual time=0.013..0.016 rows=18 loops=1)

8. 0.002 0.016 ↑ 1.0 1 1

Subquery Scan on "*SELECT* 2" (cost=0.00..0.02 rows=1 width=64) (actual time=0.015..0.016 rows=1 loops=1)

9. 0.014 0.014 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=128) (actual time=0.014..0.014 rows=1 loops=1)

10. 0.001 0.003 ↑ 1.0 1 1

Subquery Scan on "*SELECT* 3" (cost=0.00..0.02 rows=1 width=64) (actual time=0.003..0.003 rows=1 loops=1)

11. 0.002 0.002 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=128) (actual time=0.002..0.002 rows=1 loops=1)

12. 1.526 1.597 ↓ 1.1 56 1

Hash (cost=13.49..13.49 rows=49 width=1,074) (actual time=1.597..1.597 rows=56 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 68kB
13. 0.071 0.071 ↓ 1.1 56 1

Seq Scan on businessobjects (cost=0.00..13.49 rows=49 width=1,074) (actual time=0.010..0.071 rows=56 loops=1)

14. 0.026 0.050 ↑ 1.0 98 1

Hash (cost=3.98..3.98 rows=98 width=46) (actual time=0.050..0.050 rows=98 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
15. 0.024 0.024 ↑ 1.0 98 1

Seq Scan on hubs (cost=0.00..3.98 rows=98 width=46) (actual time=0.005..0.024 rows=98 loops=1)

16.          

SubPlan (for Result)

17. 1.410 3.225 ↑ 1.0 1 15

Aggregate (cost=32.18..32.19 rows=1 width=32) (actual time=0.215..0.215 rows=1 loops=15)

18. 1.815 1.815 ↓ 2.0 4 15

Seq Scan on x_relations r (cost=0.00..29.15 rows=2 width=37) (actual time=0.082..0.121 rows=4 loops=15)

  • Filter: (target_id = businessobjects.businessobject_view_id)
  • Rows Removed by Filter: 808
Planning time : 2.554 ms