explain.depesz.com

PostgreSQL's explain analyze made readable

Result: B82A

Settings
# exclusive inclusive rows x rows loops node
1. 11.319 2,598.998 ↑ 3.4 35,876 1

Merge Right Join (cost=796,561.12..1,165,660.19 rows=122,956 width=32) (actual time=2,569.941..2,598.998 rows=35,876 loops=1)

  • Merge Cond: (vbfile2.composite_id = composite.id)
  • Filter: (vbfile2.id IS NULL)
  • Rows Removed by Filter: 607
2.          

CTE composite

3. 13.748 1,876.054 ↓ 1.8 160,391 1

Append (cost=155.35..343,273.71 rows=91,400 width=743) (actual time=2.681..1,876.054 rows=160,391 loops=1)

4. 1,146.210 1,148.539 ↓ 2.2 14,516 1

Bitmap Heap Scan on scan_composite_element sce (cost=155.35..25,136.79 rows=6,550 width=667) (actual time=2.680..1,148.539 rows=14,516 loops=1)

  • Recheck Cond: (scan_id = '6aba20de-818e-4e18-aff7-bdc0614177af'::uuid)
  • Filter: (type <> 3)
  • Heap Blocks: exact=1,962
5. 2.329 2.329 ↓ 2.2 14,516 1

Bitmap Index Scan on scan_id_idx (cost=0.00..153.72 rows=6,554 width=0) (actual time=2.329..2.329 rows=14,516 loops=1)

  • Index Cond: (scan_id = '6aba20de-818e-4e18-aff7-bdc0614177af'::uuid)
6. 694.994 713.767 ↓ 1.7 145,875 1

Bitmap Heap Scan on scan_composite_leaf scl (cost=1,970.16..317,222.92 rows=84,850 width=749) (actual time=21.653..713.767 rows=145,875 loops=1)

  • Recheck Cond: (scan_id = '6aba20de-818e-4e18-aff7-bdc0614177af'::uuid)
  • Heap Blocks: exact=15,143
7. 18.773 18.773 ↓ 1.7 145,875 1

Bitmap Index Scan on scan_id_leaf_idx (cost=0.00..1,948.94 rows=84,850 width=0) (actual time=18.772..18.773 rows=145,875 loops=1)

  • Index Cond: (scan_id = '6aba20de-818e-4e18-aff7-bdc0614177af'::uuid)
8.          

CTE vbfile

9. 121.758 132.163 ↑ 1.0 45,050 1

Bitmap Heap Scan on version_bom_file vbf (cost=1,311.98..107,769.09 rows=46,394 width=779) (actual time=11.806..132.163 rows=45,050 loops=1)

  • Recheck Cond: (scan_id = '6aba20de-818e-4e18-aff7-bdc0614177af'::uuid)
  • Heap Blocks: exact=8,822
10. 10.405 10.405 ↑ 1.0 45,153 1

Bitmap Index Scan on scan_composite_index (cost=0.00..1,300.38 rows=46,394 width=0) (actual time=10.405..10.405 rows=45,153 loops=1)

  • Index Cond: (scan_id = '6aba20de-818e-4e18-aff7-bdc0614177af'::uuid)
11. 23.926 249.793 ↑ 1.0 45,050 1

Sort (cost=4,523.80..4,639.78 rows=46,394 width=16) (actual time=240.246..249.793 rows=45,050 loops=1)

  • Sort Key: vbfile2.composite_id
  • Sort Method: quicksort Memory: 3,648kB
12. 225.867 225.867 ↑ 1.0 45,050 1

CTE Scan on vbfile vbfile2 (cost=0.00..927.88 rows=46,394 width=16) (actual time=11.815..225.867 rows=45,050 loops=1)

13. 20.874 2,337.886 ↑ 2.9 36,483 1

Sort (cost=340,994.53..341,259.55 rows=106,010 width=40) (actual time=2,329.685..2,337.886 rows=36,483 loops=1)

  • Sort Key: composite.id
  • Sort Method: quicksort Memory: 4,055kB
14. 33.997 2,317.012 ↑ 2.9 36,483 1

Merge Right Join (cost=13,883.12..332,145.96 rows=106,010 width=40) (actual time=2,303.348..2,317.012 rows=36,483 loops=1)

  • Merge Cond: (vbfile1.composite_id = composite.root_bom_consumer_node_id)
  • Filter: (vbfile1.id IS NULL)
  • Rows Removed by Filter: 123,908
15. 23.995 51.260 ↑ 1.0 45,050 1

Sort (cost=4,523.80..4,639.78 rows=46,394 width=16) (actual time=39.458..51.260 rows=45,050 loops=1)

  • Sort Key: vbfile1.composite_id
  • Sort Method: quicksort Memory: 3,648kB
16. 27.265 27.265 ↑ 1.0 45,050 1

CTE Scan on vbfile vbfile1 (cost=0.00..927.88 rows=46,394 width=16) (actual time=0.048..27.265 rows=45,050 loops=1)

17. 80.868 2,231.755 ↓ 1.8 160,391 1

Sort (cost=9,359.32..9,587.82 rows=91,400 width=48) (actual time=2,201.150..2,231.755 rows=160,391 loops=1)

  • Sort Key: composite.root_bom_consumer_node_id
  • Sort Method: quicksort Memory: 18,548kB
18. 2,150.887 2,150.887 ↓ 1.8 160,391 1

CTE Scan on composite (cost=0.00..1,828.00 rows=91,400 width=48) (actual time=2.693..2,150.887 rows=160,391 loops=1)