explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dVo7 : Vari1

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

Sort (cost=2,976,239.80..2,976,269.49 rows=11,879 width=265) (actual time=2,781.158..2,789.536 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=1289030
2. 237.765 2,764.316 ↑ 1.0 11,879 1

Hash Join (cost=930.81..2,975,435.82 rows=11,879 width=265) (actual time=10.267..2,764.316 rows=11,879 loops=1)

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

Seq Scan on report_productmetasummary pms (cost=0.00..852.79 rows=11,879 width=101) (actual time=0.006..9.988 rows=11,879 loops=1)

  • Buffers: shared hit=734
4. 4.792 10.094 ↑ 1.0 4,614 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 3558kB
  • Buffers: shared hit=827
5. 5.302 5.302 ↑ 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.302 rows=4,614 loops=1)

  • Buffers: shared hit=827
6.          

SubPlan (for Hash Join)

7. 56.423 130.669 ↑ 1.0 1 11,879

Nested Loop (cost=0.43..4.89 rows=1 width=274) (actual time=0.008..0.011 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 225.701 ↑ 1.0 1 11,879

Aggregate (cost=15.36..15.37 rows=1 width=8) (actual time=0.018..0.019 rows=1 loops=11,879)

  • Buffers: shared hit=83103
11. 66.403 190.064 ↑ 1.0 1 11,879

Nested Loop (cost=0.57..15.36 rows=1 width=4) (actual time=0.009..0.016 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.35 rows=4 width=8) (actual time=0.003..0.004 rows=1 loops=11,879)

  • Index Cond: (product_meta_id = pms.pm_id)
  • Buffers: shared hit=37398
13. 76.145 76.145 ↑ 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.005..0.005 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. 35.637 962.199 ↑ 1.0 1 11,879

Aggregate (cost=91.41..91.42 rows=1 width=8) (actual time=0.080..0.081 rows=1 loops=11,879)

  • Buffers: shared hit=443685
15. 18.990 926.562 ↓ 0.0 0 11,879

Nested Loop Left Join (cost=0.99..91.40 rows=1 width=4) (actual time=0.050..0.078 rows=0 loops=11,879)

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

Nested Loop (cost=0.71..88.89 rows=1 width=8) (actual time=0.031..0.076 rows=0 loops=11,879)

  • Buffers: shared hit=440472
17. 463.281 463.281 ↑ 2.7 6 11,879

Index Scan using order_basketitem_product_meta_id_9c39f8d5 on order_basketitem bi (cost=0.42..49.07 rows=16 width=8) (actual time=0.009..0.039 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=213101
18. 302.688 302.688 ↓ 0.0 0 75,672

Index Scan using order_order_pkey on order_order o (cost=0.29..2.44 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=75,672)

  • 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=227371
19. 4.768 4.768 ↑ 1.0 1 1,192

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.004 rows=1 loops=1,192)

  • Index Cond: (order_id = o.id)
  • Buffers: shared hit=3213
20. 35.637 1,187.900 ↑ 1.0 1 11,879

Aggregate (cost=138.60..138.61 rows=1 width=8) (actual time=0.099..0.100 rows=1 loops=11,879)

  • Buffers: shared hit=696871
21. 28.849 1,152.263 ↓ 0.0 0 11,879

Nested Loop (cost=0.99..138.59 rows=1 width=20) (actual time=0.055..0.097 rows=0 loops=11,879)

  • Join Filter: (o_1.id = p_2.order_id)
  • Buffers: shared hit=696871
22. 343.659 1,116.626 ↓ 0.0 0 11,879

Nested Loop (cost=0.71..136.51 rows=1 width=28) (actual time=0.019..0.094 rows=0 loops=11,879)

  • Buffers: shared hit=689602
23. 296.975 296.975 ↑ 2.9 13 11,879

Index Scan using order_basketitem_product_meta_id_9c39f8d5 on order_basketitem bi_1 (cost=0.42..48.97 rows=38 width=24) (actual time=0.003..0.025 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=213097
24. 475.992 475.992 ↓ 0.0 0 158,664

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,664)

  • 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=476505
25. 6.788 6.788 ↓ 0.0 0 3,394

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,394)

  • Index Cond: (order_id = bi_1.order_id)
  • Heap Fetches: 481
  • Buffers: shared hit=7269
Planning time : 2.485 ms
Execution time : 2,809.222 ms