explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LhAY

Settings
# exclusive inclusive rows x rows loops node
1. 3,534.872 3,563.078 ↑ 2.0 82 1

CTE Scan on sortedfiles r (cost=346,409.39..347,246.59 rows=167 width=460) (actual time=3,558.749..3,563.078 rows=82 loops=1)

  • Filter: ((dr > 0) AND (dr <= 50))
  • Rows Removed by Filter: 11,878
2.          

CTE file

3. 286.568 2,474.621 ↓ 3.2 273,292 1

Bitmap Heap Scan on scan_composite_leaf scl (cost=1,979.91..318,072.28 rows=85,076 width=760) (actual time=2,208.835..2,474.621 rows=273,292 loops=1)

  • Recheck Cond: (scan_id = 'd16fdf60-581e-4f6c-af86-123007611e88'::uuid)
  • Heap Blocks: exact=20,347
4. 2,188.053 2,188.053 ↓ 6.4 546,481 1

Bitmap Index Scan on scan_id_leaf_idx (cost=0.00..1,958.64 rows=85,076 width=0) (actual time=2,188.053..2,188.053 rows=546,481 loops=1)

  • Index Cond: (scan_id = 'd16fdf60-581e-4f6c-af86-123007611e88'::uuid)
5.          

CTE vbssm

6. 3.429 124.086 ↑ 1.3 11,960 1

Hash Join (cost=45.79..13,225.69 rows=15,745 width=135) (actual time=30.102..124.086 rows=11,960 loops=1)

  • Hash Cond: (vbssm.version_bom_id = vb.id)
7. 120.046 120.046 ↑ 1.3 11,960 1

Seq Scan on version_bom_string_search_match vbssm (cost=0.00..12,963.41 rows=15,745 width=143) (actual time=29.426..120.046 rows=11,960 loops=1)

  • Filter: ((scan_id = 'd16fdf60-581e-4f6c-af86-123007611e88'::uuid) AND (license_id = '39692bc6-4d1c-4466-a02c-fa6f21170587'::uuid))
  • Rows Removed by Filter: 163,934
8. 0.307 0.611 ↓ 1.0 1,328 1

Hash (cost=29.24..29.24 rows=1,324 width=8) (actual time=0.611..0.611 rows=1,328 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 68kB
9. 0.304 0.304 ↓ 1.0 1,328 1

Seq Scan on version_bom vb (cost=0.00..29.24 rows=1,324 width=8) (actual time=0.018..0.304 rows=1,328 loops=1)

10.          

CTE sortedfiles

11. 12.158 3,545.756 ↑ 2.8 11,960 1

WindowAgg (cost=13,688.16..14,357.92 rows=33,488 width=492) (actual time=3,530.528..3,545.756 rows=11,960 loops=1)

12. 17.812 3,533.598 ↑ 2.8 11,960 1

Sort (cost=13,688.16..13,771.88 rows=33,488 width=444) (actual time=3,530.505..3,533.598 rows=11,960 loops=1)

  • Sort Key: file.name, file.id
  • Sort Method: quicksort Memory: 8,781kB
13. 34.333 3,515.786 ↑ 2.8 11,960 1

Merge Join (cost=10,080.27..11,171.31 rows=33,488 width=444) (actual time=3,390.595..3,515.786 rows=11,960 loops=1)

  • Merge Cond: ((vbssm_1.composite_id = file.id) AND (vbssm_1.scan_id = file.scan_id))
14. 9.936 142.760 ↑ 1.3 11,960 1

Sort (cost=1,412.53..1,451.89 rows=15,745 width=152) (actual time=139.599..142.760 rows=11,960 loops=1)

  • Sort Key: vbssm_1.composite_id, vbssm_1.scan_id
  • Sort Method: quicksort Memory: 5,168kB
15. 132.824 132.824 ↑ 1.3 11,960 1

CTE Scan on vbssm vbssm_1 (cost=0.00..314.90 rows=15,745 width=152) (actual time=30.105..132.824 rows=11,960 loops=1)

16. 657.945 3,338.693 ↓ 3.2 273,836 1

Sort (cost=8,667.74..8,880.43 rows=85,076 width=292) (actual time=3,249.058..3,338.693 rows=273,836 loops=1)

  • Sort Key: file.id, file.scan_id
  • Sort Method: external sort Disk: 76,888kB
17. 2,680.748 2,680.748 ↓ 3.2 273,292 1

CTE Scan on file (cost=0.00..1,701.52 rows=85,076 width=292) (actual time=2,208.843..2,680.748 rows=273,292 loops=1)

18.          

Initplan (for CTE Scan)

19. 0.847 28.206 ↑ 1.0 1 1

Aggregate (cost=753.48..753.49 rows=1 width=8) (actual time=28.206..28.206 rows=1 loops=1)

20. 27.359 27.359 ↑ 2.8 11,960 1

CTE Scan on sortedfiles (cost=0.00..669.76 rows=33,488 width=8) (actual time=0.001..27.359 rows=11,960 loops=1)