explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6W67 : Optimization for: Optimization for: plan #llhF; plan #fhWK

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 889.602 12,884.003 ↑ 2.7 325,624 1

Gather (cost=451,505.98..913,204.94 rows=878,854 width=130) (actual time=10,896.371..12,884.003 rows=325,624 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
2. 1,153.846 11,994.401 ↑ 3.4 108,541 3 / 3

Merge Left Join (cost=450,505.98..824,319.54 rows=366,189 width=130) (actual time=10,889.386..11,994.401 rows=108,541 loops=3)

  • Merge Cond: (iip.place_id = p1.place_id)
  • Join Filter: (p1.place_type = iip.place_type)
  • Rows Removed by Join Filter: 20810
  • Filter: (((p1.parent_place_id = ANY ('{99}'::integer[])) OR (p2.parent_place_id = ANY ('{99}'::integer[]))) AND ((p1.parent_place_type = ANY ('{4}'::integer[])) OR (p2.parent_place_type = ANY ('{4}'::integer[]))) AND ((p1.warehouse_id = iip.warehouse_id) OR (p2.warehouse_id = iip.warehouse_id)))
  • Rows Removed by Filter: 2369857
3. 466.229 8,501.368 ↑ 2.0 1,558,663 3 / 3

Merge Left Join (cost=450,505.55..463,602.71 rows=3,087,259 width=89) (actual time=7,655.950..8,501.368 rows=1,558,663 loops=3)

  • Merge Cond: (iip.place_id = bip.id)
4. 3,942.581 5,420.531 ↑ 1.3 1,558,663 3 / 3

Sort (cost=340,397.51..345,274.38 rows=1,950,749 width=48) (actual time=5,046.599..5,420.531 rows=1,558,663 loops=3)

  • Sort Key: iip.place_id
  • Sort Method: external merge Disk: 90368kB
5. 1,477.950 1,477.950 ↑ 1.3 1,558,663 3 / 3

Parallel Seq Scan on instance_in_place iip (cost=0.00..76,576.19 rows=1,950,749 width=48) (actual time=0.056..1,477.950 rows=1,558,663 loops=3)

  • Filter: (stock_type = ANY ('{1}'::integer[]))
  • Rows Removed by Filter: 45005
6. 14.513 2,614.608 ↓ 1.1 32,221 3 / 3

Sort (cost=110,108.04..110,179.94 rows=28,761 width=49) (actual time=2,609.341..2,614.608 rows=32,221 loops=3)

  • Sort Key: bip.id
  • Sort Method: quicksort Memory: 1651kB
7. 380.400 2,600.095 ↑ 1.6 18,308 3 / 3

Hash Left Join (cost=85,279.62..107,978.03 rows=28,761 width=49) (actual time=2,239.321..2,600.095 rows=18,308 loops=3)

  • Hash Cond: ((bip.place_id = p2.place_id) AND (bip.place_type = p2.place_type))
8. 4.254 4.254 ↓ 1.0 18,178 3 / 3

Seq Scan on boxing_in_place bip (cost=0.00..319.73 rows=18,173 width=20) (actual time=0.036..4.254 rows=18,178 loops=3)

9. 1,326.075 2,215.441 ↑ 1.0 1,842,945 3 / 3

Hash (cost=41,437.45..41,437.45 rows=1,842,945 width=41) (actual time=2,215.441..2,215.441 rows=1,842,945 loops=3)

  • Buckets: 1048576 Batches: 4 Memory Usage: 44231kB
10. 889.366 889.366 ↑ 1.0 1,842,945 3 / 3

Seq Scan on place p2 (cost=0.00..41,437.45 rows=1,842,945 width=41) (actual time=0.034..889.366 rows=1,842,945 loops=3)

11. 532.206 2,339.187 ↓ 2.0 3,657,479 3 / 3

Materialize (cost=0.43..209,930.17 rows=1,842,945 width=41) (actual time=0.023..2,339.187 rows=3,657,479 loops=3)

12. 1,806.981 1,806.981 ↑ 1.0 1,842,945 3 / 3

Index Scan using place_place_idx on place p1 (cost=0.43..205,322.81 rows=1,842,945 width=41) (actual time=0.020..1,806.981 rows=1,842,945 loops=3)

Planning time : 1.232 ms
Execution time : 12,922.986 ms