explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aYyB

Settings
# exclusive inclusive rows x rows loops node
1. 0.490 19,018.893 ↑ 1,837.9 979 1

Sort (cost=3,218,430.52..3,222,928.78 rows=1,799,301 width=16) (actual time=19,018.791..19,018.893 rows=979 loops=1)

  • Sort Key: report_pos_order.date DESC
  • Sort Method: quicksort Memory: 70kB
  • Functions: 265
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 68.554 ms, Inlining 672.995 ms, Optimization 3835.071 ms, Emission 2236.630 ms, Total 6813.250 ms
2. 0.246 19,018.403 ↑ 1,837.9 979 1

Subquery Scan on report_pos_order (cost=356,309.67..3,013,480.80 rows=1,799,301 width=16) (actual time=3,643.140..19,018.403 rows=979 loops=1)

3. 14,098.999 19,018.157 ↑ 1,837.9 979 1

Finalize GroupAggregate (cost=356,309.67..2,995,487.79 rows=1,799,301 width=254) (actual time=3,643.138..19,018.157 rows=979 loops=1)

  • Group Key: s.date_order, s.partner_id, s.state, pt.categ_id, s.user_id, s.location_id, s.company_id, s.sale_journal, s.pricelist_id, s.invoice_id, l.product_id, s.create_date, pt.pos_categ_id, p.product_tmpl_id, ps.config_id, pc.stock_location_id
  • Filter: ((sum((l.qty * u.factor)) <> '0'::numeric) AND (min(l.id) = ANY ('{354340,385073,421575,461025,499380,525969,544658,562132,579251,597022,637795,678148,715705,752756,791683,830058,879598,932368,1008291,1077891,1154009,1253328,1338538,1420840,1498807,1579615,1692624,1795305,1906574,447,2846,7352,20707,61643,104293,132350,139867,161231,201609,223473,245885,265609,291107,326907,356179,394831,428920,471986,501510,526872,545344,561729,584185,593958,632016,673392,710501,753071,790430,829314,879082,932965,995847,1072394,1151692,1245359,1338733,1421951,1495905,1577849,1689518,1794054,1906821,60,1793,6055,19396,57344,103499,127946,140221,160544,203262,222911,245230,268151,295137,321822,359975,387444,423742,486769,501135,525846,544471,564065,577283,594649,631853,675366,728086,807029,883686,933040,997910,1079622,1151828,1258804,1344101,1432190,1497398,1581294,1695855,1802150,1909359,40,1025,1801,4068,19250,57298,103355,128275,139882,158226,201264,222384,245096,264851,290794,321089,354990,385740,420903,460914,499279,526016,543588,561060,577198,593781,631876,672901,710270,752796,790436,829313,877587,932206,993189,1073036,1144482,1242985,1338376,1420645,1495503,1552256,1689516,1793899,1905855,540857,1,1000,1133,3842,20027,57234,103346,128967,139811,158476,201472,222370,245449,264881,290484,321087,355047,385698,420935,460819,499284,526163,544379,562068,577257,593672,631875,672961,710327,753025,790511,829391,877618,932210,995137,1073738,1151684,1247503,1339878,1420735,1495649,1541814,1689496,1793980,1905944,495,1690,6708,21061,57233,106005,146303,163185,205202,223890,245513,265114,298205,330306,355180,385766,424248,507094,526983,559097,616957,642416,681545,715308,753192,802949,831414,879982,933489,1005097,1077888,1152240,1249548,1343654,1423235,1511491,1582891,1702550,1800904,1910405,11,1017,1520,3770,19261,57087,103364,127930,139685,158247,201455,222367,245085,264629,289703,321090,354418,385015,420995,460803,499019,525860,543447,561205,577318,593442,632181,672889,710276,752773,790354,829266,877427,932256,993057,1072445,1144642,1242909,1338364,1420642,1495408,1541774,1689325,1793798,1905579,3723,12827,25352,68998,218398,429892,527968,1546129,414,2065,4367,23111,59605,108044,134897,142766,161746,203860,224011,247123,265902,293123,322785,354750,385248,425997,480403,505417,531685,544388,562054,579065,603004,631832,673521,718004,753484,790435,831153,890888,934145,998068,1078300,1152733,1281841,1338550,1433183,1499818,1517437,1734712,1823751,1937074,6115,24317,57285,103412,131869,142210,158837,202711,224183,245596,264723,290829,325639,356053,398878,422269,461838,503425,528385,543880,567649,579019,594376,658536,689363,710669,753608,792651,833476,877946,937355,1000342,1087498,1154265,1247955,1339284,1426124,1496069,1581317,1693274,1799170,1908784,57,2684,5249,19278,57111,103327,128526,139618,158384,201320,222472,246052,265811,290951,321113,354792,385051,420861,460872,499153,526430,543505,562874,577821,594011,640486,678399,711305,753618,793308,829553,877695,932421,995918,1078059,1151585,1245963,1338399,1422088,1495404,1576501,1689866,1793858,1907230,32,1596,3738,19266,57088,103321,127914,139614,158387,201173,222358,245070,264830,289708,321369,354368,385031,420864,460990,499022,525828,543463,561202,577669,593443,632400,672928,710387,752789,790468,829285,877416,932255,993056,1072714,1144707,1243343,1338367,1420596,1495406,1552255,1689476,1793961,1905887,10160,22376,57255,103356,127929,140183,167224,208129,224312,245062,267991,293676,325385,371737,388828,424827,462192,502033,543462,590835,876105,884154,979286,1005899,1077912,1153492,1248078,1342140,1423597,1496385,1579866,1695092,1796685,1907539,66002,104844,134253,151949,167167,213582,319544,333613,1021845,2415,8273,67812,132684,143983,165989,205830,248947,278833,325400,383144,391936,511140,586397,616004,660016,720785,1104197,1523727,1585034,1699783,1874922,156,1581,5295,19501,57491,103464,127951,139928,158501,201342,223233,245110,264704,289697,322000,354734,385510,421168,461376,500073,528871,543446,566276,577840,615216,631494,672944,710473,752866,790535,830409,877534,934557,997017,1078382,1152996,1246507,1338471,1420855,1495508,1551480,1689320,1794261,1905873,46163,36,1734,3856,19392,57124,103362,127996,139875,158474,201278,222330,246520,264904,290381,321794,354444,385219,420860,460823,499793,525978,543464,562311,577909,593302,632213,672945,710668,753615,790537,830407,881375,932687,997871,1081066,1153046,1248724,1339566,1422152,1495410,1576817,1689510,1794628,1906174,45132,58000,148295,174298,388035,153497,68,1144,3758,20313,57118,103431,128085,139670,159049,201240,223057,245148,264735,290297,321200,356688,385672,420874,460837,499287,525971,543864,561083,577445,594377,634851,673663,711307,754282,790483,829621,877658,933651,998069,1078053,1151678,1246789,1340335,1420702,1496154,1579506,1689427,1800556,1907441,124,3869,19372,57543,103358,128527,140264,160112,203081,222558,245482,264740,289886,321099,355565,387844,422619,461882,499769,527036,554036,566702,666708,678401,711862,756641,813316,882605,932208,1011629,1095497,1180619,1311110,1362371,1509997,1582562,1692324,1793912,1905879,46303,31,998,1260,3771,19242,57246,103349,127950,139605,158257,201159,222363,245072,264747,289704,321118,354528,385106,420905,460802,499243,525866,543528,561008,577242,593175,631776,672940,710307,752879,790416,829749,877897,932190,995175,1077886,1144484,1242915,1338392,1420648,1495504,1544532,1689484,1793867,1905869,9297,20825,69024,108639,129687,157937,195481,211646,222570,248480,268834,290298,321483,365869,392375,425031,461204,608888,700932,730993,780091,823612,843949,884300,945366,1019248,1078856,1484748,1559629,1633830,1704603,1843698,363,3402,4257,19420,57212,103850,129404,140252,159231,201324,222388,245382,264809,289757,321332,354429,385951,420973,462768,499863,526012,544597,561292,577252,593183,632199,673226,710333,752821,790786,836976,877710,932189,997118,1079406,1152752,1246901,1338436,1428872,1497188,1546937,1692107,1798522,1906136,2881,6354,19867,57939,104520,128207,140469,158896,202537,225314,245601,267780,293405,321568,358897,385233,421752,461573,499376,526039,543537,564711,585079,596685,632732,688639,714761,764698,793908,829361,879669,935593,1000993,1078072,1161167,1253334,1362153,1420920,1503810,1552200,1705302,1864245,1907247,23581,91445,106706,172278,235431,249387,286869,329247,418085,539157,544742,572315,683852,729345,876227,877857,1001170,1355858,1505433,2678,4158,19262,57390,103368,128342,139789,158482,201307,222445,245292,264718,289927,321461,355133,385091,420933,460841,499068,526074,543739,561018,578039,593431,631548,674383,711524,753354,791583,829672,879177,934290,995593,1078022,1151724,1246910,1338585,1421080,1499738,1576574,1692167,1795004,1905828,3644,11365,21720,58255,104712,135371,146299,161499,201956,224579,246790,266775,292339,324117,360214,430394,463711,500357,527146,553767,561209,578357,606800,652910,677339,742065,758892,801594,848057,879218,998858,1150913,1220053,1329813,1409880,1441843,1501267,1648239,1938648,46190,2090,3878,19369,57346,103636,128020,139648,158366,201367,222375,245088,264648,290003,321229,354575,385024,420916}'::integer[])))
  • Rows Removed by Filter: 1,789,644
4. 141.100 4,919.158 ↑ 1.0 1,793,873 1

Gather Merge (cost=356,309.67..601,588.15 rows=1,820,456 width=109) (actual time=3,642.559..4,919.158 rows=1,793,873 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
5. 1,065.262 4,778.058 ↑ 1.3 358,775 5 / 5

Partial GroupAggregate (cost=355,309.61..383,754.23 rows=455,114 width=109) (actual time=3,553.440..4,778.058 rows=358,775 loops=5)

  • Group Key: s.date_order, s.partner_id, s.state, pt.categ_id, s.user_id, s.location_id, s.company_id, s.sale_journal, s.pricelist_id, s.invoice_id, l.product_id, s.create_date, pt.pos_categ_id, p.product_tmpl_id, ps.config_id, pc.stock_location_id
6. 707.350 3,712.796 ↑ 1.3 364,091 5 / 5

Sort (cost=355,309.61..356,447.39 rows=455,114 width=114) (actual time=3,553.387..3,712.796 rows=364,091 loops=5)

  • Sort Key: s.date_order, s.partner_id, s.state, pt.categ_id, s.user_id, s.location_id, s.company_id, s.sale_journal, s.pricelist_id, s.invoice_id, l.product_id, s.create_date, pt.pos_categ_id, p.product_tmpl_id, ps.config_id, pc.stock_location_id
  • Sort Method: external merge Disk: 36,512kB
  • Worker 0: Sort Method: external merge Disk: 34,840kB
  • Worker 1: Sort Method: external merge Disk: 31,416kB
  • Worker 2: Sort Method: external merge Disk: 31,968kB
  • Worker 3: Sort Method: external merge Disk: 36,512kB
7. 104.196 3,005.446 ↑ 1.3 364,091 5 / 5

Hash Left Join (cost=239,312.68..296,136.24 rows=455,114 width=114) (actual time=2,298.405..3,005.446 rows=364,091 loops=5)

  • Hash Cond: (ps.config_id = pc.id)
8. 97.052 2,901.099 ↑ 1.3 364,091 5 / 5

Hash Left Join (cost=239,294.55..294,907.46 rows=455,114 width=110) (actual time=2,298.236..2,901.099 rows=364,091 loops=5)

  • Hash Cond: (s.session_id = ps.id)
9. 96.054 2,799.505 ↑ 1.3 364,091 5 / 5

Hash Left Join (cost=238,761.35..293,179.19 rows=455,114 width=110) (actual time=2,293.647..2,799.505 rows=364,091 loops=5)

  • Hash Cond: (pt.uom_id = u.id)
10. 156.115 2,703.399 ↑ 1.3 364,091 5 / 5

Parallel Hash Left Join (cost=238,759.40..291,862.02 rows=455,114 width=82) (actual time=2,293.569..2,703.399 rows=364,091 loops=5)

  • Hash Cond: (l.order_id = s.id)
11. 178.489 1,102.354 ↑ 1.3 364,091 5 / 5

Parallel Hash Left Join (cost=215,743.42..267,651.35 rows=455,114 width=33) (actual time=846.852..1,102.354 rows=364,091 loops=5)

  • Hash Cond: (l.product_id = p.id)
12. 78.810 78.810 ↑ 1.3 364,091 5 / 5

Parallel Seq Scan on pos_order_line l (cost=0.00..48,733.14 rows=455,114 width=17) (actual time=0.024..78.810 rows=364,091 loops=5)

13. 88.802 845.055 ↑ 1.6 163,530 5 / 5

Parallel Hash (cost=212,446.45..212,446.45 rows=263,758 width=20) (actual time=845.054..845.055 rows=163,530 loops=5)

  • Buckets: 1,048,576 Batches: 1 Memory Usage: 46,656kB
14. 222.300 756.253 ↑ 1.6 163,530 5 / 5

Nested Loop Left Join (cost=0.42..212,446.45 rows=263,758 width=20) (actual time=0.137..756.253 rows=163,530 loops=5)

15. 43.363 43.363 ↑ 1.6 163,530 5 / 5

Parallel Seq Scan on product_product p (cost=0.00..26,186.58 rows=263,758 width=8) (actual time=0.039..43.363 rows=163,530 loops=5)

16. 490.590 490.590 ↑ 1.0 1 817,650 / 5

Index Scan using product_template_pkey on product_template pt (cost=0.42..0.71 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=817,650)

  • Index Cond: (id = p.product_tmpl_id)
17. 63.695 1,444.930 ↑ 1.6 102,782 5 / 5

Parallel Hash (cost=20,943.77..20,943.77 rows=165,777 width=57) (actual time=1,444.929..1,444.930 rows=102,782 loops=5)

  • Buckets: 524,288 Batches: 1 Memory Usage: 52,416kB
18. 1,381.235 1,381.235 ↑ 1.6 102,782 5 / 5

Parallel Seq Scan on pos_order s (cost=0.00..20,943.77 rows=165,777 width=57) (actual time=1,350.213..1,381.235 rows=102,782 loops=5)

19. 0.013 0.052 ↑ 1.0 42 5 / 5

Hash (cost=1.42..1.42 rows=42 width=36) (actual time=0.052..0.052 rows=42 loops=5)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
20. 0.039 0.039 ↑ 1.0 42 5 / 5

Seq Scan on product_uom u (cost=0.00..1.42 rows=42 width=36) (actual time=0.033..0.039 rows=42 loops=5)

21. 2.376 4.542 ↑ 1.0 12,720 5 / 5

Hash (cost=374.20..374.20 rows=12,720 width=8) (actual time=4.542..4.542 rows=12,720 loops=5)

  • Buckets: 16,384 Batches: 1 Memory Usage: 625kB
22. 2.166 2.166 ↑ 1.0 12,720 5 / 5

Seq Scan on pos_session ps (cost=0.00..374.20 rows=12,720 width=8) (actual time=0.023..2.166 rows=12,720 loops=5)

23. 0.066 0.151 ↑ 1.0 317 5 / 5

Hash (cost=14.17..14.17 rows=317 width=8) (actual time=0.150..0.151 rows=317 loops=5)

  • Buckets: 1,024 Batches: 1 Memory Usage: 21kB
24. 0.085 0.085 ↑ 1.0 317 5 / 5

Seq Scan on pos_config pc (cost=0.00..14.17 rows=317 width=8) (actual time=0.017..0.085 rows=317 loops=5)

Planning time : 7.162 ms
Execution time : 19,128.571 ms