explain.depesz.com

PostgreSQL's explain analyze made readable

Result: v6eh : Vari2

Settings
# exclusive inclusive rows x rows loops node
1. 25.827 2,293.401 ↑ 1.0 11,879 1

Sort (cost=2,994,611.83..2,994,641.53 rows=11,879 width=265) (actual time=2,285.027..2,293.401 rows=11,879 loops=1)

  • Sort Key: (((COALESCE((SubPlan 2), '0'::bigint) - (SubPlan 3)) - (SubPlan 4))) DESC
  • Sort Method: quicksort Memory: 3506kB
  • Buffers: shared hit=1288917
2. 240.417 2,267.574 ↑ 1.0 11,879 1

Hash Join (cost=930.81..2,993,807.85 rows=11,879 width=265) (actual time=9.805..2,267.574 rows=11,879 loops=1)

  • Hash Cond: (pms.product_id = p.id)
  • Buffers: shared hit=1288917
3. 9.937 9.937 ↑ 1.0 11,879 1

Seq Scan on report_productmetasummary pms (cost=0.00..853.79 rows=11,879 width=101) (actual time=0.007..9.937 rows=11,879 loops=1)

  • Buffers: shared hit=735
4. 4.510 9.669 ↑ 1.0 4,614 1

Hash (cost=873.14..873.14 rows=4,614 width=741) (actual time=9.668..9.669 rows=4,614 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 3560kB
  • Buffers: shared hit=827
5. 5.159 5.159 ↑ 1.0 4,614 1

Seq Scan on mainproduct_product p (cost=0.00..873.14 rows=4,614 width=741) (actual time=0.005..5.159 rows=4,614 loops=1)

  • Buffers: shared hit=827
6.          

SubPlan (for Hash Join)

7. 32.665 106.911 ↑ 1.0 1 11,879

Nested Loop (cost=0.43..4.89 rows=1 width=274) (actual time=0.007..0.009 rows=1 loops=11,879)

  • Buffers: shared hit=63810
8. 47.516 47.516 ↑ 1.0 1 11,879

Index Only Scan using mainproduct_productcateg_product_id_category_id_84e5f253_uniq on mainproduct_productcategory pc (cost=0.28..2.50 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=11,879)

  • Index Cond: (product_id = p.id)
  • Heap Fetches: 13365
  • Buffers: shared hit=37080
9. 26.730 26.730 ↑ 1.0 1 13,365

Index Scan using mainproduct_category_pkey on mainproduct_category c (cost=0.14..2.36 rows=1 width=278) (actual time=0.002..0.002 rows=1 loops=13,365)

  • Index Cond: (id = pc.category_id)
  • Buffers: shared hit=26730
10. 35.637 166.306 ↑ 1.0 1 11,879

Aggregate (cost=15.35..15.36 rows=1 width=8) (actual time=0.013..0.014 rows=1 loops=11,879)

  • Buffers: shared hit=83103
11. 52.695 130.669 ↑ 1.0 1 11,879

Nested Loop (cost=0.57..15.35 rows=1 width=4) (actual time=0.006..0.011 rows=1 loops=11,879)

  • Buffers: shared hit=83103
12. 47.516 47.516 ↑ 4.0 1 11,879

Index Scan using shelf_shelvedproducts_product_meta_id_75b6924b on shelf_shelvedproducts sp (cost=0.29..5.34 rows=4 width=8) (actual time=0.002..0.004 rows=1 loops=11,879)

  • Index Cond: (product_meta_id = pms.pm_id)
  • Buffers: shared hit=37398
13. 30.458 30.458 ↑ 1.0 1 15,229

Index Scan using shelf_shelf_pkey on shelf_shelf s (cost=0.28..2.50 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=15,229)

  • Index Cond: (id = sp.shelf_id)
  • Filter: (warehouse_id = 1)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=45705
14. 23.758 724.619 ↑ 1.0 1 11,879

Aggregate (cost=92.88..92.89 rows=1 width=8) (actual time=0.060..0.061 rows=1 loops=11,879)

  • Buffers: shared hit=443820
15. 20.203 700.861 ↓ 0.0 0 11,879

Nested Loop Left Join (cost=0.99..92.88 rows=1 width=4) (actual time=0.035..0.059 rows=0 loops=11,879)

  • Filter: (p_1.id IS NULL)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=443820
16. 129.198 677.103 ↓ 0.0 0 11,879

Nested Loop (cost=0.71..90.37 rows=1 width=8) (actual time=0.023..0.057 rows=0 loops=11,879)

  • Buffers: shared hit=440666
17. 320.733 320.733 ↑ 2.8 6 11,879

Index Scan using order_basketitem_product_meta_id_9c39f8d5 on order_basketitem bi (cost=0.42..49.16 rows=17 width=8) (actual time=0.007..0.027 rows=6 loops=11,879)

  • Index Cond: (product_meta_id = pms.pm_id)
  • Filter: ((NOT is_cancelled) AND (reserved_quantity_for_delivery > 0))
  • Rows Removed by Filter: 9
  • Buffers: shared hit=213139
18. 227.172 227.172 ↓ 0.0 0 75,724

Index Scan using order_order_pkey on order_order o (cost=0.29..2.38 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=75,724)

  • Index Cond: (id = bi.order_id)
  • Filter: ((NOT is_cancelled) AND (NOT is_closed) AND (NOT is_packed) AND (NOT is_invoiced) AND (warehouse_id = 1) AND (type = ANY ('{2,4,6,9}'::integer[])))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=227527
19. 3.555 3.555 ↑ 1.0 1 1,185

Index Scan using order_picking_order_id_7d4cf753 on order_picking p_1 (cost=0.29..2.50 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=1,185)

  • Index Cond: (order_id = o.id)
  • Buffers: shared hit=3154
20. 23.758 1,009.715 ↑ 1.0 1 11,879

Aggregate (cost=138.68..138.69 rows=1 width=8) (actual time=0.085..0.085 rows=1 loops=11,879)

  • Buffers: shared hit=696622
21. 17.190 985.957 ↓ 0.0 0 11,879

Nested Loop (cost=0.99..138.67 rows=1 width=20) (actual time=0.053..0.083 rows=0 loops=11,879)

  • Join Filter: (o_1.id = p_2.order_id)
  • Buffers: shared hit=696622
22. 236.706 962.199 ↓ 0.0 0 11,879

Nested Loop (cost=0.71..136.59 rows=1 width=28) (actual time=0.018..0.081 rows=0 loops=11,879)

  • Buffers: shared hit=689673
23. 249.459 249.459 ↑ 2.9 13 11,879

Index Scan using order_basketitem_product_meta_id_9c39f8d5 on order_basketitem bi_1 (cost=0.42..49.05 rows=38 width=24) (actual time=0.003..0.021 rows=13 loops=11,879)

  • Index Cond: (product_meta_id = pms.pm_id)
  • Filter: (NOT is_cancelled)
  • Rows Removed by Filter: 2
  • Buffers: shared hit=213126
24. 476.034 476.034 ↓ 0.0 0 158,678

Index Scan using order_order_pkey on order_order o_1 (cost=0.29..2.25 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=158,678)

  • Index Cond: (id = bi_1.order_id)
  • Filter: ((NOT is_cancelled) AND (NOT is_closed) AND (NOT is_picked) AND (NOT is_invoiced) AND (NOT is_packed) AND (NOT is_cargo_sent) AND (warehouse_id = 1) AND (type = ANY ('{2,4,6,9}'::integer[])))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=476547
25. 6.568 6.568 ↓ 0.0 0 3,284

Index Only Scan using order_picking_order_id_7d4cf753 on order_picking p_2 (cost=0.29..2.07 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=3,284)

  • Index Cond: (order_id = bi_1.order_id)
  • Heap Fetches: 381
  • Buffers: shared hit=6949
Planning time : 2.427 ms
Execution time : 2,300.767 ms