explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KXR7

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 1,958.556 80,450.266 ↓ 14,228.8 4,667,054 1

Merge Join (cost=1,610,414.28..1,654,731.61 rows=328 width=482) (actual time=60,274.414..80,450.266 rows=4,667,054 loops=1)

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

CTE vbcid

3. 32,245.135 32,245.135 ↓ 6,120.3 1,970,726 1

Seq Scan on version_bom_entry (cost=0.00..315,758.75 rows=322 width=8) (actual time=1.880..32,245.135 rows=1,970,726 loops=1)

  • Filter: ((license_definition)::text ~ '"codeSharing": 1'::text)
  • Rows Removed by Filter: 1225079
4. 4,697.653 30,525.590 ↓ 1.1 355,945 1

Unique (cost=1,290,943.39..1,331,224.04 rows=322,410 width=433) (actual time=13,338.462..30,525.590 rows=355,945 loops=1)

5. 22,796.142 25,827.937 ↑ 1.0 3,194,649 1

Sort (cost=1,290,943.39..1,298,999.52 rows=3,222,452 width=433) (actual time=13,338.460..25,827.937 rows=3,194,649 loops=1)

  • Sort Key: vbe.version_bom_component_id, vbe.usage, vbe.license_definition, vbe.match_type
  • Sort Method: external merge Disk: 1393024kB
6. 1,025.227 3,031.795 ↑ 1.0 3,194,649 1

Hash Left Join (cost=88.01..303,773.73 rows=3,222,452 width=433) (actual time=1.855..3,031.795 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
7. 2,005.633 2,005.633 ↑ 1.0 3,195,805 1

Seq Scan on version_bom_entry vbe (cost=0.00..291,578.00 rows=3,224,100 width=441) (actual time=0.898..2,005.633 rows=3,195,805 loops=1)

8. 0.233 0.935 ↑ 1.0 1,156 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 77kB
9. 0.702 0.702 ↑ 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.013..0.702 rows=1,156 loops=1)

10. 4,118.657 47,966.120 ↓ 14,496.5 4,667,866 1

Sort (cost=3,712.13..3,712.94 rows=322 width=49) (actual time=46,935.928..47,966.120 rows=4,667,866 loops=1)

  • Sort Key: vbc.id
  • Sort Method: external sort Disk: 136768kB
11. 1,944.238 43,847.463 ↓ 6,120.3 1,970,726 1

Nested Loop (cost=64.92..3,698.72 rows=322 width=49) (actual time=4.050..43,847.463 rows=1,970,726 loops=1)

12. 755.706 39,932.499 ↓ 6,120.3 1,970,726 1

Hash Join (cost=64.64..2,575.99 rows=322 width=41) (actual time=3.391..39,932.499 rows=1,970,726 loops=1)

  • Hash Cond: (vbc.version_bom_id = vb.id)
13. 33,263.961 39,176.139 ↓ 6,120.3 1,970,726 1

Nested Loop (cost=0.42..2,507.34 rows=322 width=25) (actual time=2.714..39,176.139 rows=1,970,726 loops=1)

14. 5,912.178 5,912.178 ↑ 1.0 1 1,970,726

Index Scan using version_bom_component_pkey on version_bom_component vbc (cost=0.42..7.76 rows=1 width=17) (actual time=0.002..0.003 rows=1 loops=1,970,726)

  • Index Cond: (id = vbcid.id)
15. 0.223 0.654 ↑ 1.0 1,432 1

Hash (cost=46.32..46.32 rows=1,432 width=24) (actual time=0.654..0.654 rows=1,432 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 95kB
16. 0.431 0.431 ↑ 1.0 1,432 1

Seq Scan on version_bom vb (cost=0.00..46.32 rows=1,432 width=24) (actual time=0.023..0.431 rows=1,432 loops=1)

17. 1,970.726 1,970.726 ↑ 1.0 1 1,970,726

Index Scan using pk_central_release on central_release cr (cost=0.28..3.48 rows=1 width=24) (actual time=0.001..0.001 rows=1 loops=1,970,726)

  • Index Cond: (id = vb.release_id)
Planning time : 9.540 ms
Execution time : 80,859.441 ms