explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uQP1

Settings
# exclusive inclusive rows x rows loops node
1. 143.314 58,577.225 ↓ 661.2 214,887 1

Merge Join (cost=28,358,694.08..28,403,021.83 rows=325 width=474) (actual time=43,319.673..58,577.225 rows=214,887 loops=1)

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

CTE vbcid

3. 153.209 31,454.818 ↓ 577.7 184,276 1

Unique (cost=315,857.12..315,858.73 rows=319 width=8) (actual time=30,884.966..31,454.818 rows=184,276 loops=1)

4.          

Initplan (for Unique)

5. 9.355 9.355 ↑ 168.0 1 1

Seq Scan on version_bom_component vbc_1 (cost=0.00..14,066.69 rows=168 width=0) (actual time=9.355..9.355 rows=1 loops=1)

  • Filter: (version_bom_id = 1801)
  • Rows Removed by Filter: 30132
6. 1,151.046 31,292.254 ↓ 6,120.3 1,970,726 1

Sort (cost=315,773.39..315,774.20 rows=322 width=8) (actual time=30,884.966..31,292.254 rows=1,970,726 loops=1)

  • Sort Key: version_bom_entry.version_bom_component_id
  • Sort Method: external merge Disk: 34632kB
7. 272.827 30,141.208 ↓ 6,120.3 1,970,726 1

Result (cost=0.00..315,759.98 rows=322 width=8) (actual time=9.400..30,141.208 rows=1,970,726 loops=1)

  • One-Time Filter: $0
8. 29,868.381 29,868.381 ↓ 6,120.3 1,970,726 1

Seq Scan on version_bom_entry (cost=0.00..315,759.98 rows=322 width=8) (actual time=0.040..29,868.381 rows=1,970,726 loops=1)

  • Filter: ((license_definition)::text ~ '"codeSharing": 1'::text)
  • Rows Removed by Filter: 1225079
9. 4,427.376 26,023.158 ↓ 1.1 355,945 1

Unique (cost=28,039,151.22..28,079,442.33 rows=322,411 width=433) (actual time=10,935.251..26,023.158 rows=355,945 loops=1)

10. 19,874.311 21,595.782 ↑ 1.0 3,194,649 1

Sort (cost=28,039,151.22..28,047,209.44 rows=3,223,289 width=433) (actual time=10,935.250..21,595.782 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: 1393048kB
11. 1,720.853 1,721.471 ↑ 1.0 3,194,649 1

Seq Scan on version_bom_entry vbe (cost=0.00..27,051,717.03 rows=3,223,289 width=433) (actual time=0.017..1,721.471 rows=3,194,649 loops=1)

  • Filter: ((match_type <> 'SNIPPET'::text) OR (alternatives: SubPlan 3 or hashed SubPlan 4))
  • Rows Removed by Filter: 1156
12.          

SubPlan (for Seq Scan)

13. 0.000 0.000 ↓ 0.0 0

Index Scan using version_bom_snippet_pkey on version_bom_snippet vbs (cost=0.28..8.30 rows=1 width=0) (never executed)

  • Index Cond: (id = vbe.id)
  • Filter: ((NOT ignored) AND (review_status = 'REVIEWED'::text))
14. 0.618 0.618 ↓ 0.0 0 1

Seq Scan on version_bom_snippet vbs_1 (cost=0.00..76.45 rows=1 width=8) (actual time=0.618..0.618 rows=0 loops=1)

  • Filter: ((NOT ignored) AND (review_status = 'REVIEWED'::text))
  • Rows Removed by Filter: 1156
15. 78.933 32,410.753 ↓ 674.4 215,124 1

Sort (cost=3,684.13..3,684.93 rows=319 width=41) (actual time=32,384.405..32,410.753 rows=215,124 loops=1)

  • Sort Key: vbcid.id
  • Sort Method: quicksort Memory: 20541kB
16. 99.870 32,331.820 ↓ 577.7 184,276 1

Nested Loop (cost=64.92..3,670.86 rows=319 width=41) (actual time=30,885.772..32,331.820 rows=184,276 loops=1)

17. 51.201 32,047.674 ↓ 577.7 184,276 1

Hash Join (cost=64.64..2,558.54 rows=319 width=33) (actual time=30,885.761..32,047.674 rows=184,276 loops=1)

  • Hash Cond: (vbc.version_bom_id = vb.id)
18. 137.066 31,995.741 ↓ 577.7 184,276 1

Nested Loop (cost=0.42..2,489.93 rows=319 width=25) (actual time=30,885.009..31,995.741 rows=184,276 loops=1)

19. 31,490.123 31,490.123 ↓ 577.7 184,276 1

CTE Scan on vbcid (cost=0.00..6.38 rows=319 width=8) (actual time=30,884.969..31,490.123 rows=184,276 loops=1)

20. 368.552 368.552 ↑ 1.0 1 184,276

Index Scan using version_bom_component_pkey on version_bom_component vbc (cost=0.42..7.78 rows=1 width=17) (actual time=0.002..0.002 rows=1 loops=184,276)

  • Index Cond: (id = vbcid.id)
21. 0.206 0.732 ↑ 1.0 1,432 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 95kB
22. 0.526 0.526 ↑ 1.0 1,432 1

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

23. 184.276 184.276 ↑ 1.0 1 184,276

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=184,276)

  • Index Cond: (id = vb.release_id)