explain.depesz.com

PostgreSQL's explain analyze made readable

Result: j81Z : 111

Settings
# exclusive inclusive rows x rows loops node
1. 0.016 21.098 ↑ 1.0 8 1

Sort (cost=6,722.82..6,722.84 rows=8 width=78) (actual time=21.097..21.098 rows=8 loops=1)

  • Sort Key: ("position"('7,6,2,3,1,4,5,8'::text, (pos.status)::text)), ((SubPlan 8))
  • Sort Method: quicksort Memory: 26kB
2. 0.074 21.082 ↑ 1.0 8 1

Nested Loop Left Join (cost=0.56..6,722.70 rows=8 width=78) (actual time=0.089..21.082 rows=8 loops=1)

  • Join Filter: (c.id = pos.contragent_id)
  • Rows Removed by Join Filter: 7
3. 0.043 0.043 ↑ 1.0 8 1

Index Scan using contragents_pkey on contragents c (cost=0.28..47.07 rows=8 width=67) (actual time=0.009..0.043 rows=8 loops=1)

  • Index Cond: (id = ANY ('{857,1581,1582,11051,11647,12127,12165,277}'::integer[]))
4. 0.004 0.008 ↑ 2.0 1 8

Materialize (cost=0.28..9.53 rows=2 width=15) (actual time=0.001..0.001 rows=1 loops=8)

5. 0.004 0.004 ↑ 2.0 1 1

Index Scan using nsi_price_order_supplier_order_id_idx on nsi_price_order_supplier pos (cost=0.28..9.52 rows=2 width=15) (actual time=0.004..0.004 rows=1 loops=1)

  • Index Cond: (price_order_id = 3565)
6.          

SubPlan (forNested Loop Left Join)

7. 0.008 0.024 ↑ 1.0 1 8

Aggregate (cost=17.84..17.85 rows=1 width=14) (actual time=0.003..0.003 rows=1 loops=8)

8. 0.005 0.016 ↓ 0.0 0 8

Nested Loop (cost=0.56..17.82 rows=1 width=14) (actual time=0.001..0.002 rows=0 loops=8)

9. 0.008 0.008 ↓ 0.0 0 8

Index Scan using nsi_price_order_supplier_positions_order_supplier_id_fkey on nsi_price_order_supplier_positions posp (cost=0.28..9.51 rows=1 width=13) (actual time=0.001..0.001 rows=0 loops=8)

  • Index Cond: (price_order_supplier_id = pos.id)
  • Filter: deliverable
10. 0.003 0.003 ↑ 1.0 1 1

Index Scan using nsi_price_order_positions_pkey on nsi_price_order_positions pop (cost=0.28..8.30 rows=1 width=9) (actual time=0.002..0.003 rows=1 loops=1)

  • Index Cond: (id = posp.price_order_position_id)
11. 0.008 0.024 ↑ 1.0 1 8

Aggregate (cost=26.27..26.28 rows=1 width=9) (actual time=0.003..0.003 rows=1 loops=8)

12. 0.002 0.016 ↓ 0.0 0 8

Nested Loop (cost=0.98..26.27 rows=1 width=9) (actual time=0.002..0.002 rows=0 loops=8)

13. 0.005 0.008 ↓ 0.0 0 8

Nested Loop (cost=0.56..17.82 rows=1 width=9) (actual time=0.001..0.001 rows=0 loops=8)

14. 0.000 0.000 ↓ 0.0 0 8

Index Scan using nsi_price_order_supplier_positions_order_supplier_id_fkey on nsi_price_order_supplier_positions posp_1 (cost=0.28..9.51 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=8)

  • Index Cond: (price_order_supplier_id = pos.id)
  • Filter: deliverable
15. 0.003 0.003 ↑ 1.0 1 1

Index Scan using nsi_price_order_positions_pkey on nsi_price_order_positions pop_1 (cost=0.28..8.30 rows=1 width=9) (actual time=0.002..0.003 rows=1 loops=1)

  • Index Cond: (id = posp_1.price_order_position_id)
16. 0.006 0.006 ↑ 1.0 1 1

Index Scan using nsi_supplier_position_pkey on nsi_supplier_position sp (cost=0.42..8.44 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=1)

  • Index Cond: (id = posp_1.supplier_position_id)
17. 0.008 0.016 ↑ 1.0 1 8

Aggregate (cost=17.82..17.83 rows=1 width=0) (actual time=0.001..0.002 rows=1 loops=8)

18. 0.008 0.008 ↓ 0.0 0 8

Nested Loop (cost=0.56..17.82 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=8)

19. 0.000 0.000 ↓ 0.0 0 8

Index Scan using nsi_price_order_supplier_positions_order_supplier_id_fkey on nsi_price_order_supplier_positions posp_2 (cost=0.28..9.51 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=8)

  • Index Cond: (price_order_supplier_id = pos.id)
  • Filter: (NOT deliverable)
  • Rows Removed by Filter: 0
20. 0.000 0.000 ↓ 0.0 0

Index Scan using nsi_price_order_positions_pkey on nsi_price_order_positions pop_2 (cost=0.28..8.30 rows=1 width=4) (never executed)

  • Index Cond: (id = posp_2.price_order_position_id)
  • Filter: priority
21. 0.008 0.008 ↑ 1.0 1 8

Aggregate (cost=9.52..9.53 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=8)

22. 0.000 0.000 ↓ 0.0 0 8

Index Scan using nsi_price_order_supplier_positions_order_supplier_id_fkey on nsi_price_order_supplier_positions posp_3 (cost=0.28..9.51 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=8)

  • Index Cond: (price_order_supplier_id = pos.id)
  • Filter: (is_alternative AND deliverable)
  • Rows Removed by Filter: 0
23. 0.001 0.002 ↑ 1.0 1 1

Aggregate (cost=9.52..9.53 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=1)

24. 0.001 0.001 ↑ 1.0 1 1

Index Scan using nsi_price_order_supplier_positions_order_supplier_id_fkey on nsi_price_order_supplier_positions posp_4 (cost=0.28..9.51 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)

  • Index Cond: (price_order_supplier_id = pos.id)
  • Filter: deliverable
25. 1.750 20.755 ↑ 1.0 1 7

Aggregate (cost=704.64..704.65 rows=1 width=4) (actual time=2.965..2.965 rows=1 loops=7)

26. 1.806 19.005 ↓ 117.2 2,930 7

Nested Loop (cost=645.75..704.58 rows=25 width=4) (actual time=1.754..2.715 rows=2,930 loops=7)

27. 0.010 0.014 ↑ 1.0 1 7

HashAggregate (cost=11.02..11.03 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=7)

  • Group Key: nsi_price_order_positions.dictionary_position_id
28. 0.001 0.004 ↑ 2.0 1 1

Bitmap Heap Scan on nsi_price_order_positions (cost=4.30..11.01 rows=2 width=4) (actual time=0.004..0.004 rows=1 loops=1)

  • Recheck Cond: (price_order_id = 3565)
  • Heap Blocks: exact=1
29. 0.003 0.003 ↑ 2.0 1 1

Bitmap Index Scan on nsi_price_order_positions_order_id_idx_btree (cost=0.00..4.29 rows=2 width=0) (actual time=0.003..0.003 rows=1 loops=1)

  • Index Cond: (price_order_id = 3565)
30. 5.159 17.185 ↓ 225.4 2,930 7

Bitmap Heap Scan on nsi_supplier_position sp_1 (cost=634.74..693.42 rows=13 width=4) (actual time=1.750..2.455 rows=2,930 loops=7)

  • Recheck Cond: ((dictionary_position_id = nsi_price_order_positions.dictionary_position_id) AND (contragent_id = c.id))
  • Filter: ((NOT deleted) AND is_actual)
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=1968
31. 0.672 12.026 ↓ 0.0 0 7

BitmapAnd (cost=634.74..634.74 rows=15 width=0) (actual time=1.718..1.718 rows=0 loops=7)

32. 9.247 9.247 ↓ 50.7 20,514 7

Bitmap Index Scan on nsi_sp_position_id_idx (cost=0.00..55.45 rows=405 width=0) (actual time=1.321..1.321 rows=20,514 loops=7)

  • Index Cond: (dictionary_position_id = nsi_price_order_positions.dictionary_position_id)
33. 2.107 2.107 ↓ 1.2 5,185 7

Bitmap Index Scan on nsi_sp_contragent_id_idx (cost=0.00..578.09 rows=4,490 width=0) (actual time=0.301..0.301 rows=5,185 loops=7)

  • Index Cond: (contragent_id = c.id)
34. 0.024 0.064 ↑ 1.0 1 8

Aggregate (cost=18.67..18.68 rows=1 width=32) (actual time=0.008..0.008 rows=1 loops=8)

35. 0.000 0.040 ↓ 0.0 0 8

HashAggregate (cost=18.64..18.65 rows=1 width=14) (actual time=0.005..0.005 rows=0 loops=8)

  • Group Key: pop_3.id
36. 0.005 0.040 ↓ 0.0 0 8

Nested Loop (cost=10.36..18.63 rows=1 width=14) (actual time=0.005..0.005 rows=0 loops=8)

37. 0.007 0.032 ↓ 0.0 0 8

Nested Loop (cost=10.08..18.27 rows=1 width=18) (actual time=0.004..0.004 rows=0 loops=8)

38. 0.007 0.024 ↓ 0.0 0 8

Nested Loop (cost=9.80..17.83 rows=1 width=13) (actual time=0.003..0.003 rows=0 loops=8)

39. 0.008 0.016 ↓ 0.0 0 8

HashAggregate (cost=9.52..9.53 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=8)

  • Group Key: nsi_price_order_supplier_positions.price_order_position_id
40. 0.008 0.008 ↓ 0.0 0 8

Index Scan using nsi_price_order_supplier_positions_order_supplier_id_fkey on nsi_price_order_supplier_positions (cost=0.28..9.51 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=8)

  • Index Cond: (price_order_supplier_id = pos.id)
  • Filter: deliverable
41. 0.001 0.001 ↑ 1.0 1 1

Index Scan using nsi_price_order_positions_pkey on nsi_price_order_positions pop_3 (cost=0.28..8.30 rows=1 width=9) (actual time=0.001..0.001 rows=1 loops=1)

  • Index Cond: (id = nsi_price_order_supplier_positions.price_order_position_id)
42. 0.001 0.001 ↑ 2.0 1 1

Index Scan using nsi_price_order_supplier_positions_order_position_id_fkey on nsi_price_order_supplier_positions posp_5 (cost=0.28..0.42 rows=2 width=13) (actual time=0.001..0.001 rows=1 loops=1)

  • Index Cond: (price_order_position_id = pop_3.id)
  • Filter: deliverable
43. 0.003 0.003 ↓ 0.0 0 1

Index Scan using nsi_price_order_supplier_pkey on nsi_price_order_supplier px (cost=0.28..0.35 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=1)

  • Index Cond: (id = posp_5.price_order_supplier_id)
  • Filter: ((status = ANY ('{2,6}'::integer[])) AND (price_order_id = 3565))
  • Rows Removed by Filter: 1
44. 0.016 0.032 ↑ 1.0 1 8

Subquery Scan on p (cost=17.83..17.85 rows=1 width=32) (actual time=0.004..0.004 rows=1 loops=8)

45. 0.008 0.016 ↑ 1.0 1 8

Aggregate (cost=17.83..17.84 rows=1 width=10) (actual time=0.002..0.002 rows=1 loops=8)

46. 0.007 0.008 ↓ 0.0 0 8

Nested Loop (cost=0.56..17.82 rows=1 width=10) (actual time=0.001..0.001 rows=0 loops=8)

47. 0.000 0.000 ↓ 0.0 0 8

Index Scan using nsi_price_order_supplier_positions_order_supplier_id_fkey on nsi_price_order_supplier_positions posp_6 (cost=0.28..9.51 rows=1 width=9) (actual time=0.000..0.000 rows=0 loops=8)

  • Index Cond: (price_order_supplier_id = pos.id)
  • Filter: deliverable
48. 0.001 0.001 ↑ 1.0 1 1

Index Scan using nsi_price_order_positions_pkey on nsi_price_order_positions pop_4 (cost=0.28..8.30 rows=1 width=9) (actual time=0.001..0.001 rows=1 loops=1)

  • Index Cond: (id = posp_6.price_order_position_id)
49. 0.024 0.032 ↑ 1.0 1 8

Aggregate (cost=11.02..11.03 rows=1 width=0) (actual time=0.004..0.004 rows=1 loops=8)

50. 0.000 0.008 ↓ 0.0 0 8

Bitmap Heap Scan on nsi_price_order_positions pop_5 (cost=4.30..11.02 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=8)

  • Recheck Cond: (winner_supplier_id = pos.id)
  • Filter: (price_order_id = 3565)
51. 0.008 0.008 ↓ 0.0 0 8

Bitmap Index Scan on nsi_price_order_positions_winner_supplier__id_idx (cost=0.00..4.29 rows=2 width=0) (actual time=0.001..0.001 rows=0 loops=8)

  • Index Cond: (winner_supplier_id = pos.id)