explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Bbv

Settings
# exclusive inclusive rows x rows loops node
1. 0.397 22,569.248 ↑ 1.0 100 1

Limit (cost=1,089,033.34..1,089,039.34 rows=100 width=210) (actual time=22,568.106..22,569.248 rows=100 loops=1)

2. 0.717 22,568.851 ↑ 6,494.3 100 1

GroupAggregate (cost=1,089,033.34..1,127,999.26 rows=649,432 width=210) (actual time=22,568.103..22,568.851 rows=100 loops=1)

  • Group Key: i.id, attr.weight, attr.complete, attr.type
3. 1,266.149 22,568.134 ↑ 6,430.0 101 1

Sort (cost=1,089,033.34..1,090,656.92 rows=649,432 width=567) (actual time=22,568.059..22,568.134 rows=101 loops=1)

  • Sort Key: i.id, attr.weight, attr.complete, attr.type
  • Sort Method: external merge Disk: 358456kB
4. 928.260 21,301.985 ↓ 1.0 660,498 1

Hash Left Join (cost=788,535.50..862,072.51 rows=649,432 width=567) (actual time=19,125.260..21,301.985 rows=660,498 loops=1)

  • Hash Cond: (period_i.id = period_attr.bo_instance_id)
5. 1,244.787 20,373.375 ↓ 1.0 660,498 1

Hash Right Join (cost=788,522.23..859,623.17 rows=649,432 width=497) (actual time=19,124.898..20,373.375 rows=660,498 loops=1)

  • Hash Cond: (manager_rel.to_bo_instance_id = person_i.id)
  • Filter: ((i.search_text ~~* '%%'::text) OR (person_i.search_text ~~* '%%'::text) OR (manager_i.search_text ~~* '%%'::text) OR (period_i.search_text ~~* '%%'::text))
6. 0.000 18.661 ↑ 1.0 6,306 1

Gather (cost=1,128.17..39,408.17 rows=6,620 width=224) (actual time=2.932..18.661 rows=6,306 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
7. 19.429 33.860 ↑ 1.3 2,102 3

Nested Loop Left Join (cost=128.17..37,746.17 rows=2,758 width=224) (actual time=0.903..33.860 rows=2,102 loops=3)

8. 13.105 14.424 ↑ 1.3 2,102 3

Parallel Bitmap Heap Scan on boi_rel manager_rel (cost=127.74..19,869.20 rows=2,758 width=16) (actual time=0.831..14.424 rows=2,102 loops=3)

  • Recheck Cond: (boc_rel_type_id = 26)
  • Filter: (NOT is_deleted)
  • Rows Removed by Filter: 18
  • Heap Blocks: exact=629
9. 1.319 1.319 ↑ 1.0 6,372 1

Bitmap Index Scan on idx_boi_rel_boc_rel_type_id (cost=0.00..126.09 rows=6,621 width=0) (actual time=1.318..1.319 rows=6,372 loops=1)

  • Index Cond: (boc_rel_type_id = 26)
10. 0.007 0.007 ↑ 1.0 1 6,306

Index Scan using bo_instance_pkey on bo_instance manager_i (cost=0.43..6.48 rows=1 width=216) (actual time=0.007..0.007 rows=1 loops=6,306)

  • Index Cond: (id = manager_rel.from_bo_instance_id)
  • Filter: (NOT is_deleted)
11. 823.267 19,109.927 ↓ 1.0 660,498 1

Hash (cost=746,931.16..746,931.16 rows=649,432 width=381) (actual time=19,109.925..19,109.927 rows=660,498 loops=1)

  • Buckets: 65536 Batches: 32 Memory Usage: 8891kB
12. 1,716.181 18,286.660 ↓ 1.0 660,498 1

Hash Left Join (cost=485,876.67..746,931.16 rows=649,432 width=381) (actual time=12,188.162..18,286.660 rows=660,498 loops=1)

  • Hash Cond: (person_rel.from_bo_instance_id = person_i.id)
13. 955.452 12,845.814 ↓ 1.0 660,498 1

Hash Left Join (cost=358,662.28..535,010.01 rows=649,432 width=173) (actual time=8,344.650..12,845.814 rows=660,498 loops=1)

  • Hash Cond: (period_rel.to_bo_instance_id = period_i.id)
14. 1,341.649 8,970.428 ↓ 1.0 660,498 1

Hash Left Join (cost=263,930.89..395,664.86 rows=649,432 width=123) (actual time=5,423.398..8,970.428 rows=660,498 loops=1)

  • Hash Cond: (i.id = attr.bo_instance_id)
15. 1,299.778 6,383.187 ↓ 1.0 660,498 1

Hash Right Join (cost=218,019.81..320,094.23 rows=649,432 width=74) (actual time=4,177.435..6,383.187 rows=660,498 loops=1)

  • Hash Cond: (period_rel.from_bo_instance_id = i.id)
16. 917.749 964.630 ↑ 1.0 742,708 1

Bitmap Heap Scan on boi_rel period_rel (cost=13,951.10..95,581.14 rows=744,999 width=16) (actual time=57.800..964.630 rows=742,708 loops=1)

  • Recheck Cond: (boc_rel_type_id = 20)
  • Filter: (NOT is_deleted)
  • Rows Removed by Filter: 7
  • Heap Blocks: exact=58307
17. 46.881 46.881 ↑ 1.0 742,717 1

Bitmap Index Scan on idx_boi_rel_boc_rel_type_id (cost=0.00..13,764.85 rows=745,123 width=0) (actual time=46.880..46.881 rows=742,717 loops=1)

  • Index Cond: (boc_rel_type_id = 20)
18. 578.787 4,118.779 ↓ 1.0 660,498 1

Hash (cost=188,339.80..188,339.80 rows=649,432 width=66) (actual time=4,118.778..4,118.779 rows=660,498 loops=1)

  • Buckets: 262144 Batches: 8 Memory Usage: 9766kB
19. 1,289.144 3,539.992 ↓ 1.0 660,498 1

Hash Right Join (cost=90,615.23..188,339.80 rows=649,432 width=66) (actual time=1,351.788..3,539.992 rows=660,498 loops=1)

  • Hash Cond: (person_rel.to_bo_instance_id = i.id)
20. 910.903 956.064 ↓ 1.0 742,708 1

Bitmap Heap Scan on boi_rel person_rel (cost=13,863.19..95,435.30 rows=740,365 width=16) (actual time=56.143..956.064 rows=742,708 loops=1)

  • Recheck Cond: (boc_rel_type_id = 29)
  • Filter: (NOT is_deleted)
  • Rows Removed by Filter: 7
  • Heap Blocks: exact=58308
21. 45.161 45.161 ↓ 1.0 742,717 1

Bitmap Index Scan on idx_boi_rel_boc_rel_type_id (cost=0.00..13,678.10 rows=740,489 width=0) (actual time=45.159..45.161 rows=742,717 loops=1)

  • Index Cond: (boc_rel_type_id = 29)
22. 558.766 1,294.784 ↓ 1.0 660,498 1

Hash (cost=61,657.15..61,657.15 rows=649,432 width=58) (actual time=1,294.782..1,294.784 rows=660,498 loops=1)

  • Buckets: 262144 Batches: 8 Memory Usage: 9031kB
23. 694.946 736.018 ↓ 1.0 660,498 1

Bitmap Heap Scan on bo_instance i (cost=13,938.91..61,657.15 rows=649,432 width=58) (actual time=46.287..736.018 rows=660,498 loops=1)

  • Recheck Cond: (bo_class_id = 15)
  • Filter: ((NOT is_deleted) AND (tenant_id = 319))
  • Rows Removed by Filter: 82217
  • Heap Blocks: exact=30122
24. 41.072 41.072 ↑ 1.0 742,717 1

Bitmap Index Scan on idx_bo_instance_bo_class_id (cost=0.00..13,776.55 rows=745,616 width=0) (actual time=41.071..41.072 rows=742,717 loops=1)

  • Index Cond: (bo_class_id = 15)
25. 624.916 1,245.592 ↑ 1.0 742,715 1

Hash (cost=28,648.15..28,648.15 rows=742,715 width=57) (actual time=1,245.591..1,245.592 rows=742,715 loops=1)

  • Buckets: 262144 Batches: 8 Memory Usage: 10793kB
26. 620.676 620.676 ↑ 1.0 742,715 1

Seq Scan on attrib_goal attr (cost=0.00..28,648.15 rows=742,715 width=57) (actual time=0.015..620.676 rows=742,715 loops=1)

27. 1,484.120 2,919.934 ↓ 1.0 1,750,873 1

Hash (cost=54,040.84..54,040.84 rows=1,750,684 width=58) (actual time=2,919.933..2,919.934 rows=1,750,873 loops=1)

  • Buckets: 262144 Batches: 16 Memory Usage: 11880kB
28. 1,435.814 1,435.814 ↓ 1.0 1,750,873 1

Seq Scan on bo_instance period_i (cost=0.00..54,040.84 rows=1,750,684 width=58) (actual time=0.016..1,435.814 rows=1,750,873 loops=1)

  • Filter: (NOT is_deleted)
  • Rows Removed by Filter: 24
29. 1,807.200 3,724.665 ↓ 1.0 1,750,873 1

Hash (cost=54,040.84..54,040.84 rows=1,750,684 width=216) (actual time=3,724.664..3,724.665 rows=1,750,873 loops=1)

  • Buckets: 65536 Batches: 32 Memory Usage: 14643kB
30. 1,917.465 1,917.465 ↓ 1.0 1,750,873 1

Seq Scan on bo_instance person_i (cost=0.00..54,040.84 rows=1,750,684 width=216) (actual time=0.027..1,917.465 rows=1,750,873 loops=1)

  • Filter: (NOT is_deleted)
  • Rows Removed by Filter: 24
31. 0.153 0.350 ↑ 1.0 190 1

Hash (cost=10.90..10.90 rows=190 width=78) (actual time=0.348..0.350 rows=190 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 31kB
32. 0.197 0.197 ↑ 1.0 190 1

Seq Scan on attrib_period period_attr (cost=0.00..10.90 rows=190 width=78) (actual time=0.011..0.197 rows=190 loops=1)

Planning time : 4.205 ms
Execution time : 22,634.038 ms