explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xS35

Settings
# exclusive inclusive rows x rows loops node
1. 33.438 8,236.830 ↑ 1,578.0 1 1

Merge Right Join (cost=1,694,276.51..1,722,208.77 rows=1,578 width=12) (actual time=8,236.828..8,236.830 rows=1 loops=1)

  • Merge Cond: ((ton_ke.product_id = dt.product_id) AND (ton_ke.location_id = sl.id))
  • rows=2,962,246 loops=7)
2.          

CTE ton_ke

3. 778.375 7,092.747 ↑ 1.7 2,195,339 1

Finalize GroupAggregate (cost=449,096.88..1,209,758.85 rows=3,722,688 width=16) (actual time=3,195.751..7,092.747 rows=2,195,339 loops=1)

  • Group Key: sis.product_id, tsc.location_id
4. 2,925.209 6,314.372 ↑ 3.3 5,750,833 1

Gather Merge (cost=449,096.88..1,029,258.99 rows=19,103,064 width=16) (actual time=3,195.737..6,314.372 rows=5,750,833 loops=1)

  • Workers Planned: 6
  • Workers Launched: 6
5. 391.341 3,389.163 ↑ 3.9 821,548 7 / 7

Partial GroupAggregate (cost=449,086.78..512,763.66 rows=3,183,844 width=16) (actual time=2,678.467..3,389.163 rows=821,548 loops=7)

  • Group Key: sis.product_id, tsc.location_id
6. 1,174.159 2,997.822 ↑ 1.8 1,778,710 7 / 7

Sort (cost=449,086.78..457,046.39 rows=3,183,844 width=12) (actual time=2,678.437..2,997.822 rows=1,778,710 loops=7)

  • Sort Key: sis.product_id, tsc.location_id
  • Sort Method: external merge Disk: 40,144kB
7. 1,272.810 1,823.663 ↑ 1.8 1,778,710 7 / 7

Hash Join (cost=25,708.88..105,194.41 rows=3,183,844 width=12) (actual time=555.545..1,823.663 rows=1,778,710 loops=7)

  • Hash Cond: (sis.shelf_id = tsc.id)
  • -> Parallel Seq Scan on stock_inventory_shelf sis (cost=0.00..70412.33 rows=3,456,443 width=12) (actual time=0.038..436.108
8. 211.303 550.853 ↑ 1.0 1,028,710 7 / 7

Hash (cost=12,845.12..12,845.12 rows=1,029,101 width=8) (actual time=550.852..550.853 rows=1,028,710 loops=7)

  • Buckets: 1,048,576 Batches: 1 Memory Usage: 48,376kB
9. 339.550 339.550 ↑ 1.0 1,028,710 7 / 7

Seq Scan on tiki_shelf_code tsc (cost=0.00..12,845.12 rows=1,029,101 width=8) (actual time=0.026..339.550 rows=1,028,710 loops=7)

  • Filter: active
  • Rows Removed by Filter: 90,052
10. 579.786 8,202.970 ↑ 12.9 288,456 1

Sort (cost=484,381.91..493,688.63 rows=3,722,688 width=16) (actual time=8,164.100..8,202.970 rows=288,456 loops=1)

  • Sort Key: ton_ke.product_id, ton_ke.location_id
  • Sort Method: external merge Disk: 55,864kB
11. 7,623.184 7,623.184 ↑ 1.7 2,195,339 1

CTE Scan on ton_ke (cost=0.00..74,453.76 rows=3,722,688 width=16) (actual time=3,195.755..7,623.184 rows=2,195,339 loops=1)

12. 0.034 0.422 ↑ 1,578.0 1 1

Sort (cost=135.74..139.68 rows=1,578 width=8) (actual time=0.422..0.422 rows=1 loops=1)

  • Sort Key: dt.product_id, sl.id
  • Sort Method: quicksort Memory: 25kB
13. 0.005 0.388 ↑ 1,578.0 1 1

Nested Loop (cost=0.99..51.91 rows=1,578 width=8) (actual time=0.239..0.388 rows=1 loops=1)

14. 0.144 0.144 ↑ 789.0 1 1

Index Scan using tiki_checkstock_detail_checkstock_id_idx on tiki_checkstock_detail dt (cost=0.43..28.08 rows=789 width=8) (actual time=0.101..0.144 rows=1 loops=1)

  • Index Cond: (checkstock_id = 19,086)
15. 0.003 0.239 ↑ 2.0 1 1

Materialize (cost=0.56..4.12 rows=2 width=8) (actual time=0.132..0.239 rows=1 loops=1)

16. 0.118 0.236 ↑ 2.0 1 1

Nested Loop (cost=0.56..4.11 rows=2 width=8) (actual time=0.129..0.236 rows=1 loops=1)

  • -> Index Scan using tiki_checkstock_pkey on tiki_checkstock ct (cost=0.29..0.51 rows=1 width=8) (actual time=0.060..0.110 rows=1loops=1)
  • Index Cond: (id = 19,086)
17. 0.118 0.118 ↑ 1.0 1 1

Index Scan using stock_location_warehouse_id_idx on stock_location sl (cost=0.28..3.59 rows=1 width=8) (actual time=0.061..0.118 rows=1 loops=1)

  • Index Cond: (warehouse_id = ct.warehouse_id)
  • Filter: ((tiki_code)::text = 'sale'::text)
  • Rows Removed by Filter: 27
Planning time : 1.103 ms
Execution time : 8,353.522 ms