explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uNG9

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 123.893 ↑ 1.0 25 1

Limit (cost=3,053,887.63..3,053,887.69 rows=25 width=128) (actual time=123.890..123.893 rows=25 loops=1)

2. 1.030 123.892 ↑ 29.6 25 1

Sort (cost=3,053,887.63..3,053,889.48 rows=739 width=128) (actual time=123.889..123.892 rows=25 loops=1)

  • Sort Key: ("position"('7,6,2,3,1,4,5,8'::text, (pos.status)::text)), ((SubPlan 7))
  • Sort Method: top-N heapsort Memory: 31kB
3. 6.153 122.862 ↑ 1.0 739 1

Merge Left Join (cost=36.17..3,053,866.78 rows=739 width=128) (actual time=0.833..122.862 rows=739 loops=1)

  • Merge Cond: (c.id = pos.contragent_id)
4. 4.511 4.511 ↑ 1.0 739 1

Index Scan using contragents_pkey on contragents c (cost=0.42..4,672.20 rows=739 width=117) (actual time=0.009..4.511 rows=739 loops=1)

  • Index Cond: (id = ANY ('{131746,56082,168891,174862,75751,205106,179306,231601,160222,227763,81471,263555,171777,59463,216739,183352,26796,230936,3962,2141,194356,94659,248131,22714,176237,266427,260633,258843,234301,216358,216265,213630,213182,211065,209499,209008,208172,206467,205815,205554,205347,203573,202939,202378,202312,202291,202246,202212,201407,201262,200837,199744,199369,199131,198764,198617,197867,197674,197542,197447,197281,196447,196381,196281,196082,195944,195209,195106,195084,195071,194894,194820,194792,194504,194260,193943,193839,193721,193369,193083,192662,192632,192621,192536,192121,192076,191713,191463,191325,191070,191011,190584,190484,190227,190115,189937,189477,189361,189353,189330,189085,188946,188708,188588,188483,188222,188207,188035,187886,183675,183569,182778,182493,182384,182033,182011,181377,180314,179985,179943,179731,179447,178846,178557,178264,177970,177869,177749,177335,177312,177298,177104,176916,176346,176222,176137,176068,175836,175825,175435,175428,175042,174501,174467,174264,174038,173823,173372,172828,172820,172634,172573,172377,172094,171877,171810,171580,170996,170773,170474,170450,170369,170298,170272,170257,170202,170171,170017,169185,168953,168414,168220,167918,167271,167125,166622,166459,166077,166052,165803,165747,165617,165395,165332,165302,165117,164791,164624,163991,163815,163792,163694,163536,163178,163104,162895,162535,162485,162343,162181,162151,162005,161519,161281,161113,161072,160608,160167,159639,159544,159454,159442,159108,159093,158704,158487,158197,157614,157070,156964,156621,156216,156134,156017,155722,155576,155569,155113,154203,154188,154162,154146,154027,154003,153812,153751,153706,152957,152940,152920,152700,152643,152442,152229,152172,151963,151837,151136,150505,150478,149843,148504,148376,148012,147466,147367,147090,147082,146683,145861,144938,144932,144854,144752,144571,144545,144161,143968,143703,142991,142490,141984,139223,139139,138796,138692,138456,138095,138072,137542,137186,137089,136856,136840,136652,136640,136301,136144,135518,134420,134110,133985,133605,133079,133026,132017,131599,131176,130489,129602,128725,128409,127319,127306,127027,126885,126807,125207,124656,123824,123632,123397,123268,122443,121829,119431,119349,119333,119109,118737,117797,117498,117079,116938,116457,115662,115660,115094,114449,114336,114318,114056,113749,113621,113329,112746,112679,112526,112192,111282,111216,110851,110340,108363,107444,107432,107258,107209,105601,105589,105413,104736,104699,104008,102923,101220,99648,98606,98495,98283,98267,97854,97110,96860,96139,95273,95103,94222,94125,93766,92003,91669,91600,91507,91418,91269,91237,90376,90359,90307,89456,89428,88767,88641,88325,87943,87859,87774,87704,87083,86840,86349,86190,86183,86049,85986,85679,85661,85577,85535,84994,84932,84714,84672,84655,83758,83590,83538,83032,82912,82845,82564,82468,82392,82371,82358,82279,82137,81991,81842,81449,81381,81355,81245,81232,81168,81157,81075,80942,80269,80254,80209,79997,79792,79647,79620,79535,79316,79206,79170,79033,78523,78515,78286,78259,78253,78161,78094,77992,77970,77865,77854,77701,77587,77364,77317,77214,77056,77037,76993,76848,76725,76557,76401,76380,76204,76166,76148,76037,75993,75820,75434,75412,75385,75384,75297,74993,74947,74792,74778,74773,74768,74745,74584,74510,74443,74399,74293,74282,74108,73758,73318,72504,72392,72284,72009,71733,71592,71477,71379,71304,71182,70546,70333,70239,187824,187787,187011,186845,186375,186203,186143,185828,185797,185768,185764,185534,185393,185359,184937,184936,184781,184477,184383,183857,70060,69878,68941,68879,68708,68255,67698,67261,66818,66450,66157,65960,65609,65400,65300,64761,64374,64370,63883,63836,63473,62526,62506,62434,62429,62383,61872,61252,60932,60457,60397,60186,60098,60054,58301,58286,58119,58027,57846,57675,57647,57481,56946,56789,55808,55606,55578,55577,55244,55138,54881,54442,54340,54336,53742,53549,53483,53470,53006,52100,52081,51643,51352,51309,51057,50613,50220,49568,49430,49044,48093,47005,46636,45913,45903,45790,45253,45081,44990,44792,43972,43773,43442,43428,43326,43197,42520,42362,42210,42145,41228,41093,41059,40341,40098,40060,40050,38978,37980,37234,37018,36790,36787,36388,35892,35264,35193,35114,35039,34970,34901,34316,34245,33550,33364,33062,32546,32456,32248,31964,31022,29200,28832,28369,28342,28156,27703,26776,26681,26675,25352,24378,23823,21586,20898,20858,20822,20434,19799,19766,19666,17534,17362,16889,16627,16569,15940,15854,15769,15463,15395,15275,14977,14945,14854,14627,13632,13295,13070,12739,12695,12567,12496,12394,12095,11901,11763,11626,11599,7133,6798,6108,6044,5597,4951,4860,4666,4441,4030,3918,3905,3381,3069,2929,2802,2676,2628,2554,2366,2335,2290,2042,1923,1790,1720,1701,1651,1461,1406,1384,1375,1363,1341,1320,1307,1249,1235,1176,1107,1091,1079,1073,1036,988}'::integer[]))
5. 0.309 0.609 ↓ 6.4 740 1

Sort (cost=35.75..36.04 rows=116 width=15) (actual time=0.457..0.609 rows=740 loops=1)

  • Sort Key: pos.contragent_id
  • Sort Method: quicksort Memory: 59kB
6. 0.300 0.300 ↓ 6.4 740 1

Index Scan using nsi_price_order_supplier_order_id_idx on nsi_price_order_supplier pos (cost=0.43..31.77 rows=116 width=15) (actual time=0.015..0.300 rows=740 loops=1)

  • Index Cond: (price_order_id = 87813)
7.          

SubPlan (forMerge Left Join)

8. 0.739 6.651 ↑ 1.0 1 739

Aggregate (cost=154.27..154.28 rows=1 width=15) (actual time=0.009..0.009 rows=1 loops=739)

9. 0.439 5.912 ↓ 0.0 0 739

Nested Loop (cost=0.99..154.13 rows=9 width=15) (actual time=0.007..0.008 rows=0 loops=739)

10. 5.173 5.173 ↓ 0.0 0 739

Index Scan using nsi_price_order_supplier_positions_order_supplier_id_fkey on nsi_price_order_supplier_positions posp (cost=0.56..78.06 rows=9 width=14) (actual time=0.006..0.007 rows=0 loops=739)

  • Index Cond: (price_order_supplier_id = pos.id)
  • Filter: deliverable
  • Rows Removed by Filter: 20
11. 0.300 0.300 ↑ 1.0 1 150

Index Scan using nsi_price_order_positions_pkey on nsi_price_order_positions pop (cost=0.42..8.44 rows=1 width=9) (actual time=0.002..0.002 rows=1 loops=150)

  • Index Cond: (id = posp.price_order_position_id)
12. 0.739 6.651 ↑ 1.0 1 739

Aggregate (cost=162.59..162.60 rows=1 width=10) (actual time=0.009..0.009 rows=1 loops=739)

13. 0.439 5.912 ↓ 0.0 0 739

Nested Loop (cost=1.42..162.59 rows=1 width=10) (actual time=0.007..0.008 rows=0 loops=739)

14. 0.000 5.173 ↓ 0.0 0 739

Nested Loop (cost=0.99..154.13 rows=1 width=9) (actual time=0.006..0.007 rows=0 loops=739)

15. 4.434 4.434 ↓ 0.0 0 739

Index Scan using nsi_price_order_supplier_positions_order_supplier_id_fkey on nsi_price_order_supplier_positions posp_1 (cost=0.56..78.06 rows=9 width=8) (actual time=0.005..0.006 rows=0 loops=739)

  • Index Cond: (price_order_supplier_id = pos.id)
  • Filter: deliverable
  • Rows Removed by Filter: 20
16. 0.900 0.900 ↑ 1.0 1 150

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

  • Index Cond: (id = posp_1.supplier_position_id)
17. 0.300 0.300 ↑ 1.0 1 150

Index Scan using nsi_price_order_positions_pkey on nsi_price_order_positions pop_1 (cost=0.42..8.44 rows=1 width=9) (actual time=0.002..0.002 rows=1 loops=150)

  • Index Cond: (id = posp_1.price_order_position_id)
18. 1.478 35.472 ↑ 1.0 1 739

Aggregate (cost=3,312.96..3,312.97 rows=1 width=0) (actual time=0.048..0.048 rows=1 loops=739)

19. 0.300 33.994 ↑ 1.7 20 739

Nested Loop (cost=0.99..3,312.87 rows=34 width=0) (actual time=0.006..0.046 rows=20 loops=739)

20. 4.434 4.434 ↑ 20.2 20 739

Index Scan using nsi_price_order_supplier_positions_order_supplier_id_fkey on nsi_price_order_supplier_positions posp_2 (cost=0.56..78.06 rows=404 width=4) (actual time=0.003..0.006 rows=20 loops=739)

  • Index Cond: (price_order_supplier_id = pos.id)
  • Filter: (NOT deliverable)
  • Rows Removed by Filter: 0
21. 29.260 29.260 ↑ 1.0 1 14,630

Index Scan using nsi_price_order_positions_pkey on nsi_price_order_positions pop_2 (cost=0.42..8.00 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=14,630)

  • Index Cond: (id = posp_2.price_order_position_id)
  • Filter: priority
22. 0.000 4.434 ↑ 1.0 1 739

Aggregate (cost=78.06..78.07 rows=1 width=0) (actual time=0.006..0.006 rows=1 loops=739)

23. 4.434 4.434 ↓ 0.0 0 739

Index Scan using nsi_price_order_supplier_positions_order_supplier_id_fkey on nsi_price_order_supplier_positions posp_3 (cost=0.56..78.06 rows=1 width=0) (actual time=0.006..0.006 rows=0 loops=739)

  • Index Cond: (price_order_supplier_id = pos.id)
  • Filter: (is_alternative AND deliverable)
  • Rows Removed by Filter: 20
24. 0.000 4.434 ↑ 1.0 1 739

Aggregate (cost=78.08..78.09 rows=1 width=0) (actual time=0.006..0.006 rows=1 loops=739)

25. 4.434 4.434 ↓ 0.0 0 739

Index Scan using nsi_price_order_supplier_positions_order_supplier_id_fkey on nsi_price_order_supplier_positions posp_4 (cost=0.56..78.06 rows=9 width=0) (actual time=0.005..0.006 rows=0 loops=739)

  • Index Cond: (price_order_supplier_id = pos.id)
  • Filter: deliverable
  • Rows Removed by Filter: 20
26. 1.478 39.906 ↑ 1.0 1 739

Aggregate (cost=172.31..172.32 rows=1 width=32) (actual time=0.054..0.054 rows=1 loops=739)

27. 0.739 38.428 ↓ 0.0 0 739

HashAggregate (cost=172.28..172.30 rows=1 width=15) (actual time=0.052..0.052 rows=0 loops=739)

  • Group Key: pop_3.id
28. 0.049 37.689 ↓ 0.0 0 739

Nested Loop (cost=111.17..172.28 rows=1 width=15) (actual time=0.020..0.051 rows=0 loops=739)

29. 0.542 36.950 ↓ 0.0 0 739

Hash Join (cost=110.75..171.77 rows=1 width=14) (actual time=0.019..0.050 rows=0 loops=739)

  • Hash Cond: (posp_5.price_order_supplier_id = px.id)
30. 1.038 36.211 ↑ 403.8 4 739

Nested Loop (cost=78.65..133.61 rows=1,615 width=18) (actual time=0.011..0.049 rows=4 loops=739)

31. 0.739 5.173 ↓ 0.0 0 739

HashAggregate (cost=78.08..78.09 rows=1 width=4) (actual time=0.007..0.007 rows=0 loops=739)

  • Group Key: nsi_price_order_supplier_positions.price_order_position_id
32. 4.434 4.434 ↓ 0.0 0 739

Index Scan using nsi_price_order_supplier_positions_order_supplier_id_fkey on nsi_price_order_supplier_positions (cost=0.56..78.06 rows=9 width=4) (actual time=0.006..0.006 rows=0 loops=739)

  • Index Cond: (price_order_supplier_id = pos.id)
  • Filter: deliverable
  • Rows Removed by Filter: 20
33. 30.000 30.000 ↓ 2.7 19 150

Index Scan using nsi_price_order_supplier_positions_order_position_id_fkey on nsi_price_order_supplier_positions posp_5 (cost=0.56..55.45 rows=7 width=14) (actual time=0.030..0.200 rows=19 loops=150)

  • Index Cond: (price_order_position_id = nsi_price_order_supplier_positions.price_order_position_id)
  • Filter: deliverable
  • Rows Removed by Filter: 721
34. 0.002 0.197 ↑ 1.5 2 1

Hash (cost=32.06..32.06 rows=3 width=4) (actual time=0.197..0.197 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
35. 0.195 0.195 ↑ 1.5 2 1

Index Scan using nsi_price_order_supplier_order_id_idx on nsi_price_order_supplier px (cost=0.43..32.06 rows=3 width=4) (actual time=0.012..0.195 rows=2 loops=1)

  • Index Cond: (price_order_id = 87813)
  • Filter: (status = ANY ('{2,6}'::integer[]))
  • Rows Removed by Filter: 738
36. 0.690 0.690 ↑ 1.0 1 230

Index Scan using nsi_price_order_positions_pkey on nsi_price_order_positions pop_3 (cost=0.42..0.50 rows=1 width=9) (actual time=0.002..0.003 rows=1 loops=230)

  • Index Cond: (id = posp_5.price_order_position_id)
37. 0.739 6.651 ↑ 1.0 1 739

Subquery Scan on p (cost=154.18..154.20 rows=1 width=32) (actual time=0.009..0.009 rows=1 loops=739)

38. 0.739 5.912 ↑ 1.0 1 739

Aggregate (cost=154.18..154.19 rows=1 width=11) (actual time=0.008..0.008 rows=1 loops=739)

39. 0.439 5.173 ↓ 0.0 0 739

Nested Loop (cost=0.99..154.13 rows=9 width=11) (actual time=0.006..0.007 rows=0 loops=739)

40. 4.434 4.434 ↓ 0.0 0 739

Index Scan using nsi_price_order_supplier_positions_order_supplier_id_fkey on nsi_price_order_supplier_positions posp_6 (cost=0.56..78.06 rows=9 width=10) (actual time=0.006..0.006 rows=0 loops=739)

  • Index Cond: (price_order_supplier_id = pos.id)
  • Filter: deliverable
  • Rows Removed by Filter: 20
41. 0.300 0.300 ↑ 1.0 1 150

Index Scan using nsi_price_order_positions_pkey on nsi_price_order_positions pop_4 (cost=0.42..8.44 rows=1 width=9) (actual time=0.002..0.002 rows=1 loops=150)

  • Index Cond: (id = posp_6.price_order_position_id)
42. 1.478 7.390 ↑ 1.0 1 739

Aggregate (cost=13.50..13.51 rows=1 width=0) (actual time=0.010..0.010 rows=1 loops=739)

43. 0.739 5.912 ↓ 0.0 0 739

Bitmap Heap Scan on nsi_price_order_positions pop_5 (cost=9.48..13.50 rows=1 width=0) (actual time=0.008..0.008 rows=0 loops=739)

  • Recheck Cond: ((price_order_id = 87813) AND (winner_supplier_id = pos.id))
44. 0.739 5.173 ↓ 0.0 0 739

BitmapAnd (cost=9.48..9.48 rows=1 width=0) (actual time=0.007..0.007 rows=0 loops=739)

45. 2.956 2.956 ↑ 1.1 20 739

Bitmap Index Scan on nsi_price_order_positions_order_id_idx_btree (cost=0.00..4.60 rows=23 width=0) (actual time=0.004..0.004 rows=20 loops=739)

  • Index Cond: (price_order_id = 87813)
46. 1.478 1.478 ↓ 0.0 0 739

Bitmap Index Scan on nsi_price_order_positions_winner_supplier_id_idx (cost=0.00..4.63 rows=28 width=0) (actual time=0.002..0.002 rows=0 loops=739)

  • Index Cond: (winner_supplier_id = pos.id)