explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PwW9

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 17.479 18,893.017 ↓ 44.0 44 1

Merge Join (cost=1,400,077.77..1,444,388.75 rows=1 width=474) (actual time=18,708.635..18,893.017 rows=44 loops=1)

  • Merge Cond: (vbe.version_bom_component_id = vbc.id)
2.          

CTE vbcid

3. 0.041 51.281 ↓ 35.0 35 1

Nested Loop Semi Join (cost=0.43..109,115.93 rows=1 width=8) (actual time=4.290..51.281 rows=35 loops=1)

4. 50.290 50.290 ↑ 3.4 50 1

Seq Scan on version_bom_component vbc_1 (cost=0.00..14,066.69 rows=168 width=8) (actual time=4.231..50.290 rows=50 loops=1)

  • Filter: (version_bom_id = 1801)
  • Rows Removed by Filter: 314532
5. 0.950 0.950 ↑ 1.0 1 50

Index Scan using idx_version_bom_entry_version_bom_component_id on version_bom_entry vbe_1 (cost=0.43..562.42 rows=1 width=8) (actual time=0.019..0.019 rows=1 loops=50)

  • Index Cond: (version_bom_component_id = vbc_1.id)
  • Filter: ((license_definition)::text ~ '"codeSharing": 1'::text)
  • Rows Removed by Filter: 1
6. 2,215.317 18,823.841 ↑ 1.5 219,348 1

Unique (cost=1,290,949.57..1,331,230.38 rows=322,411 width=433) (actual time=10,753.082..18,823.841 rows=219,348 loops=1)

7. 14,596.614 16,608.524 ↑ 1.8 1,760,076 1

Sort (cost=1,290,949.57..1,299,005.73 rows=3,222,465 width=433) (actual time=10,753.078..16,608.524 rows=1,760,076 loops=1)

  • Sort Key: vbe.version_bom_component_id, vbe.usage, vbe.license_definition, vbe.match_type
  • Sort Method: external merge Disk: 1393008kB
8. 982.438 2,011.910 ↑ 1.0 3,194,649 1

Hash Left Join (cost=88.01..303,774.90 rows=3,222,465 width=433) (actual time=0.600..2,011.910 rows=3,194,649 loops=1)

  • Hash Cond: (vbe.id = vbs.id)
  • Filter: ((vbe.match_type <> 'SNIPPET'::text) OR ((vbs.review_status = 'REVIEWED'::text) AND (NOT vbs.ignored)))
  • Rows Removed by Filter: 1156
9. 1,028.898 1,028.898 ↑ 1.0 3,195,805 1

Seq Scan on version_bom_entry vbe (cost=0.00..291,579.13 rows=3,224,113 width=441) (actual time=0.009..1,028.898 rows=3,195,805 loops=1)

10. 0.202 0.574 ↑ 1.0 1,156 1

Hash (cost=73.56..73.56 rows=1,156 width=22) (actual time=0.574..0.574 rows=1,156 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 77kB
11. 0.372 0.372 ↑ 1.0 1,156 1

Seq Scan on version_bom_snippet vbs (cost=0.00..73.56 rows=1,156 width=22) (actual time=0.004..0.372 rows=1,156 loops=1)

12. 0.049 51.697 ↓ 43.0 43 1

Sort (cost=12.27..12.28 rows=1 width=41) (actual time=51.689..51.697 rows=43 loops=1)

  • Sort Key: vbcid.id
  • Sort Method: quicksort Memory: 27kB
13. 0.034 51.648 ↓ 35.0 35 1

Nested Loop (cost=0.98..12.26 rows=1 width=41) (actual time=4.320..51.648 rows=35 loops=1)

14. 0.029 51.544 ↓ 35.0 35 1

Nested Loop (cost=0.70..8.78 rows=1 width=33) (actual time=4.312..51.544 rows=35 loops=1)

15. 0.033 51.445 ↓ 35.0 35 1

Nested Loop (cost=0.42..8.47 rows=1 width=25) (actual time=4.303..51.445 rows=35 loops=1)

16. 51.307 51.307 ↓ 35.0 35 1

CTE Scan on vbcid (cost=0.00..0.02 rows=1 width=8) (actual time=4.293..51.307 rows=35 loops=1)

17. 0.105 0.105 ↑ 1.0 1 35

Index Scan using version_bom_component_pkey on version_bom_component vbc (cost=0.42..8.44 rows=1 width=17) (actual time=0.003..0.003 rows=1 loops=35)

  • Index Cond: (id = vbcid.id)
18. 0.070 0.070 ↑ 1.0 1 35

Index Scan using version_bom_pkey on version_bom vb (cost=0.28..0.30 rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=35)

  • Index Cond: (id = vbc.version_bom_id)
19. 0.070 0.070 ↑ 1.0 1 35

Index Scan using pk_central_release on central_release cr (cost=0.28..3.48 rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=35)

  • Index Cond: (id = vb.release_id)