explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BgIW

Settings
# exclusive inclusive rows x rows loops node
1. 0.039 193.869 ↑ 3,397.0 1 1

Subquery Scan on v (cost=148.18..224,032.09 rows=3,397 width=3,885) (actual time=193.844..193.869 rows=1 loops=1)

  • Functions: 106
  • Options: Inlining false, Optimization false, Expressions true, Deforming true
  • Timing: Generation 34.376 ms, Inlining 0.000 ms, Optimization 7.881 ms, Emission 173.270 ms, Total 215.528 ms
2. 7.733 193.830 ↑ 3,397.0 1 1

Nested Loop Left Join (cost=148.18..223,845.26 rows=3,397 width=3,853) (actual time=193.805..193.830 rows=1 loops=1)

3. 0.114 185.792 ↑ 973.0 1 1

Hash Left Join (cost=147.90..1,992.63 rows=973 width=701) (actual time=185.779..185.792 rows=1 loops=1)

  • Hash Cond: (((d.doc #>> '{maintenancesite,region}'::text[]) = r.region) AND (g.gr_id = (r.gr_id)::integer))
4. 0.003 185.651 ↑ 973.0 1 1

Nested Loop Left Join (cost=146.05..1,979.82 rows=973 width=677) (actual time=185.639..185.651 rows=1 loops=1)

5. 0.057 183.885 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.87..5.32 rows=1 width=673) (actual time=183.884..183.885 rows=1 loops=1)

6. 183.807 183.807 ↑ 1.0 1 1

Index Scan using task_draft_task_id_idx on task_draft d (cost=0.43..2.66 rows=1 width=653) (actual time=183.806..183.807 rows=1 loops=1)

  • Index Cond: (task_id = '5416242611'::bigint)
  • Filter: ((proc_status <> 3) AND (proc_def = 'NOCSiteFaultManagement'::text) AND (proc_status = 1))
7. 0.021 0.021 ↑ 1.0 1 1

Index Scan using act_idx_hi_task_id on act_hi_taskinst t (cost=0.44..2.66 rows=1 width=30) (actual time=0.021..0.021 rows=1 loops=1)

  • Index Cond: ((id_)::text = (d.task_id)::text)
8. 0.020 1.763 ↑ 973.0 1 1

Hash Join (cost=145.17..1,964.78 rows=973 width=8) (actual time=1.752..1.763 rows=1 loops=1)

  • Hash Cond: (g.gr_id = (((g_1.cl_attributes ->> 'country_id'::text))::integer))
9. 0.091 0.100 ↑ 431.2 5 1

Bitmap Heap Scan on gr_entities g (cost=25.72..1,839.61 rows=2,156 width=8) (actual time=0.091..0.100 rows=5 loops=1)

  • Recheck Cond: (entity_id = ((d.doc #>> '{maintenancesite,id_thing}'::text[]))::integer)
  • Filter: (ent_type = 'thing'::text)
  • Heap Blocks: exact=5
10. 0.009 0.009 ↑ 454.8 5 1

Bitmap Index Scan on idx_gr_entities_entity_id_2 (cost=0.00..25.19 rows=2,274 width=0) (actual time=0.009..0.009 rows=5 loops=1)

  • Index Cond: (entity_id = ((d.doc #>> '{maintenancesite,id_thing}'::text[]))::integer)
11. 0.003 1.643 ↑ 33.3 6 1

Hash (cost=116.95..116.95 rows=200 width=4) (actual time=1.643..1.643 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
12. 0.091 1.640 ↑ 33.3 6 1

HashAggregate (cost=111.45..114.95 rows=200 width=4) (actual time=1.637..1.640 rows=6 loops=1)

  • Group Key: ((g_1.cl_attributes ->> 'country_id'::text))::integer
13. 1.549 1.549 ↑ 3.1 528 1

Seq Scan on groups g_1 (cost=0.00..107.30 rows=1,660 width=4) (actual time=0.034..1.549 rows=528 loops=1)

  • Filter: ((cl_attributes ->> 'country_id'::text) IS NOT NULL)
  • Rows Removed by Filter: 1140
14. 0.014 0.027 ↑ 1.0 34 1

Hash (cost=1.34..1.34 rows=34 width=38) (actual time=0.027..0.027 rows=34 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
15. 0.013 0.013 ↑ 1.0 34 1

Seq Scan on region_managers r (cost=0.00..1.34 rows=34 width=38) (actual time=0.008..0.013 rows=34 loops=1)

16. 0.009 0.009 ↑ 24.0 1 1

Index Scan using ix_user_activiti_id_text on user u (cost=0.28..0.70 rows=24 width=36) (actual time=0.008..0.009 rows=1 loops=1)

  • Index Cond: ((activiti_id)::text = (t.owner_)::text)
17.          

SubPlan (for Nested Loop Left Join)

18. 0.004 0.011 ↑ 1.0 1 1

Aggregate (cost=0.39..0.40 rows=1 width=8) (actual time=0.011..0.011 rows=1 loops=1)

19. 0.003 0.007 ↑ 10.0 1 1

Result (cost=0.00..0.22 rows=10 width=40) (actual time=0.006..0.007 rows=1 loops=1)

20. 0.003 0.004 ↑ 10.0 1 1

ProjectSet (cost=0.00..0.07 rows=10 width=32) (actual time=0.004..0.004 rows=1 loops=1)

21. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)

22. 0.003 0.007 ↑ 1.0 1 1

Aggregate (cost=0.39..0.40 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=1)

23. 0.001 0.004 ↑ 10.0 1 1

Result (cost=0.00..0.22 rows=10 width=40) (actual time=0.004..0.004 rows=1 loops=1)

24. 0.002 0.003 ↑ 10.0 1 1

ProjectSet (cost=0.00..0.07 rows=10 width=32) (actual time=0.002..0.003 rows=1 loops=1)

25. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)

26. 0.003 0.007 ↑ 1.0 1 1

Aggregate (cost=0.39..0.40 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=1)

27. 0.002 0.004 ↑ 10.0 1 1

Result (cost=0.00..0.22 rows=10 width=40) (actual time=0.004..0.004 rows=1 loops=1)

28. 0.001 0.002 ↑ 10.0 1 1

ProjectSet (cost=0.00..0.07 rows=10 width=32) (actual time=0.002..0.002 rows=1 loops=1)

29. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)

30. 0.063 0.188 ↑ 3.0 1 1

Result (cost=0.43..16.86 rows=3 width=32) (actual time=0.187..0.188 rows=1 loops=1)

31. 0.094 0.125 ↑ 3.0 1 1

Append (cost=0.43..16.82 rows=3 width=164) (actual time=0.124..0.125 rows=1 loops=1)

32. 0.011 0.011 ↓ 0.0 0 1

Index Scan using ix_thing_thing_id on thing (cost=0.43..2.65 rows=1 width=405) (actual time=0.011..0.011 rows=0 loops=1)

  • Index Cond: (thing_id = (d.doc #>> '{maintenancesite,new_ihs_id}'::text[]))
33. 0.006 0.006 ↓ 0.0 0 1

Seq Scan on project (cost=0.00..11.65 rows=1 width=32) (actual time=0.005..0.006 rows=0 loops=1)

  • Filter: (thing_id = (d.doc #>> '{maintenancesite,new_ihs_id}'::text[]))
34. 0.014 0.014 ↑ 1.0 1 1

Index Scan using ix_sites_ihs_thing_id on sites_ihs (cost=0.29..2.51 rows=1 width=55) (actual time=0.014..0.014 rows=1 loops=1)

  • Index Cond: (thing_id = (d.doc #>> '{maintenancesite,new_ihs_id}'::text[]))
35. 0.043 0.043 ↑ 1.0 1 1

Index Scan using ix_sites_ihs_thing_id on sites_ihs sites_ihs_1 (cost=0.29..2.51 rows=1 width=32) (actual time=0.043..0.043 rows=1 loops=1)

  • Index Cond: (thing_id = (d.doc #>> '{maintenancesite,new_ihs_id}'::text[]))
36. 0.003 0.010 ↑ 1.0 1 1

Aggregate (cost=0.69..0.70 rows=1 width=32) (actual time=0.010..0.010 rows=1 loops=1)

37. 0.003 0.007 ↓ 0.0 0 1

Sort (cost=0.48..0.51 rows=10 width=72) (actual time=0.007..0.007 rows=0 loops=1)

  • Sort Key: (((unnest(d.proc_comment))).created)
  • Sort Method: quicksort Memory: 25kB
38. 0.002 0.004 ↓ 0.0 0 1

Result (cost=0.00..0.32 rows=10 width=72) (actual time=0.003..0.004 rows=0 loops=1)

39. 0.001 0.002 ↓ 0.0 0 1

ProjectSet (cost=0.00..0.07 rows=10 width=32) (actual time=0.002..0.002 rows=0 loops=1)

40. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)

41. 0.005 0.030 ↑ 1.0 1 1

Aggregate (cost=39.13..39.14 rows=1 width=32) (actual time=0.030..0.030 rows=1 loops=1)

42. 0.002 0.025 ↓ 0.0 0 1

Bitmap Heap Scan on related_content c (cost=3.29..32.50 rows=26 width=8) (actual time=0.025..0.025 rows=0 loops=1)

  • Recheck Cond: (((process_id)::text = (d.proc_inst_id)::text) OR ((task_id)::text = (d.task_id)::text))
43. 0.004 0.023 ↓ 0.0 0 1

BitmapOr (cost=3.29..3.29 rows=26 width=0) (actual time=0.023..0.023 rows=0 loops=1)

44. 0.010 0.010 ↓ 0.0 0 1

Bitmap Index Scan on idx_relcont_procid (cost=0.00..1.67 rows=17 width=0) (actual time=0.010..0.010 rows=0 loops=1)

  • Index Cond: ((process_id)::text = (d.proc_inst_id)::text)
45. 0.009 0.009 ↓ 0.0 0 1

Bitmap Index Scan on idx_relcont_taskid (cost=0.00..1.61 rows=10 width=0) (actual time=0.009..0.009 rows=0 loops=1)

  • Index Cond: ((task_id)::text = (d.task_id)::text)
Execution time : 229.002 ms