explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GeIc

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=66,882,512.80..68,921,615.08 rows=23,304,026 width=32) (actual rows= loops=)

  • Group Key: products_stores.store_id, products_stores.product_id, products.category_id, product_stock.soh, product_stock.soo
2. 0.000 0.000 ↓ 0.0

Sort (cost=66,882,512.80..66,940,772.87 rows=23,304,026 width=32) (actual rows= loops=)

  • Sort Key: products_stores.store_id, products_stores.product_id, products.category_id, product_stock.soh, product_stock.soo
3. 0.000 0.000 ↓ 0.0

Merge Join (cost=60,749,452.31..63,704,215.05 rows=23,304,026 width=32) (actual rows= loops=)

  • Merge Cond: (product_suppliers.product_id = products.id)
4. 0.000 0.000 ↓ 0.0

Index Only Scan using product_suppliers_product_id_supplier_id_index on product_suppliers (cost=0.29..24,136.48 rows=23,328 width=4) (actual rows= loops=)

  • Filter: (supplier_id = ANY ('{1074,1068,1069,1070,344,497,530,638,743,740,841,866,946,948,961,976,91,85,50,53,83,40,63,97,88,35,92,95,82,93,762,70,801,20,72,78,89,37,104,86,33,19,129,122,134,526,153,109,115,130,188,132,200,201,169,176,198,137,162,147,193,197,181,211,207,174,111,208,146,206,154,183,177,145,159,199,185,121,194,186,156,118,189,184,212,203,205,143,112,202,150,293,213,312,311,277,289,262,282,214,231,228,269,259,287,223,295,280,238,310,257,216,833,230,235,261,222,227,288,242,240,215,274,225,290,296,232,221,294,226,276,306,249,305,234,308,236,229,251,301,233,292,239,283,278,299,258,217,284,245,286,219,527,356,350,322,382,385,359,346,417,318,368,323,349,411,410,387,377,345,373,380,392,375,404,339,403,324,391,379,399,395,421,402,397,361,341,413,407,354,422,415,400,412,371,414,338,329,409,352,396,388,406,320,340,332,390,360,348,347,343,401,420,389,435,446,423,501,502,503,504,505,506,507,464,508,509,510,448,511,512,500,513,443,514,515,516,444,427,449,462,517,518,474,519,440,520,432,521,522,523,524,525,437,499,436,447,460,479,489,496,441,495,531,532,582,533,634,534,535,536,537,538,539,541,542,543,545,546,547,548,552,553,554,556,557,559,564,565,567,569,572,575,576,577,579,580,581,586,587,588,593,594,595,597,598,601,603,607,608,614,617,618,619,621,622,623,624,625,626,630,631,632,633,635,844,544,549,550,551,555,558,560,561,562,563,566,568,570,573,578,583,584,585,589,590,591,592,596,599,600,602,604,605,606,609,610,611,612,613,616,627,628,629,636,641,674,642,643,644,645,646,647,648,651,652,655,658,660,661,662,663,664,665,666,667,668,671,672,683,684,685,686,687,688,689,690,691,692,693,694,695,696,697,698,700,702,704,705,707,708,710,711,712,714,717,718,719,721,725,729,732,733,735,738,106,723,649,650,653,654,657,659,675,682,699,701,703,706,713,715,716,720,722,724,727,728,730,731,734,736,737,237,637,876,901,926,994,1018,744,745,816,746,747,748,749,751,752,754,755,756,757,759,760,764,765,768,771,772,774,776,781,787,788,794,796,800,803,805,807,809,811,814,815,818,826,830,831,839,750,753,758,761,763,766,767,769,770,773,775,777,778,779,780,782,783,784,785,786,789,791,792,793,795,797,798,799,802,804,806,808,810,812,813,817,819,820,821,822,823,824,825,827,828,829,832,834,835,836,837,838,1023,842,929,932,937,845,846,848,856,864,871,880,894,898,900,904,905,924,931,935,938,840,928,930,933,847,849,850,851,852,853,854,855,857,858,859,860,861,862,863,865,867,868,869,870,872,873,874,875,877,878,879,881,882,883,884,885,886,887,888,889,890,891,892,893,895,896,897,899,902,903,906,907,908,909,910,911,912,913,914,915,916,917,918,919,920,921,922,923,925,934,936,939,940,983,996,1003,941,944,949,972,995,1006,1027,1028,1029,945,947,950,951,952,953,954,955,956,957,958,959,960,962,963,964,965,966,967,968,969,970,971,973,974,975,977,978,979,981,982,984,985,986,987,988,989,990,991,992,993,997,998,999,1000,1001,1002,1004,1005,1007,1008,1009,1010,1011,1012,1013,1014,1015,1016,1017,1019,1020,1021,1022,1024,1025,1026,943,1030,1031,1033,1034,1035,1036,1045,681,1044,540,528,620,327,574,640,670,709,790,325,741,726,739,220,843,927,1059,529,571,615,180,639,656,669,673,374,676,677,678,679,680,742,942,439,476,1049,490,1048,1043,1042,1041,1040,1047,1039,1038,1037,450,1057,1058,1063,1065,1066,1088,1092,1095,1096,1097,1098,1099,1089}'::integer[]))
5. 0.000 0.000 ↓ 0.0

Materialize (cost=60,749,452.02..63,385,777.96 rows=22,127,237 width=40) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=60,749,452.02..63,330,459.86 rows=22,127,237 width=40) (actual rows= loops=)

  • Merge Cond: ((products_stores.product_id = filtered_sale_id.product_id) AND (sales.parent_store_id = filtered_sale_id.parent_store_id))
  • Filter: (CASE WHEN (filtered_sale_id.sale_id IS NULL) THEN (products_stores.product_id IS NOT NULL) ELSE (sales.id = filtered_sale_id.sale_id) END AND CASE WHEN (filtered_sale_id.sale_id IS NULL) THEN (products_stores.product_id IS NOT NULL) ELSE (sales.sale_date >= ((now() - '84 days'::interval))::date) END)
7. 0.000 0.000 ↓ 0.0

Sort (cost=52,940,657.24..53,161,929.61 rows=88,508,948 width=52) (actual rows= loops=)

  • Sort Key: sales.product_id, sales.parent_store_id
8. 0.000 0.000 ↓ 0.0

Hash Join (cost=23,573,181.25..39,485,867.75 rows=88,508,948 width=52) (actual rows= loops=)

  • Hash Cond: (sales.product_id = products.id)
  • Join Filter: ((stores.nominate_store_id = products_stores.store_id) OR (stores.id IS NULL))
9. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=1.20..5,020,825.44 rows=117,120,352 width=36) (actual rows= loops=)

  • Hash Cond: (sales.parent_store_id = stores.id)
10. 0.000 0.000 ↓ 0.0

Seq Scan on sales (cost=0.00..3,410,419.40 rows=117,120,352 width=28) (actual rows= loops=)

  • Filter: (company_id = 1)
11. 0.000 0.000 ↓ 0.0

Hash (cost=1.09..1.09 rows=9 width=8) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Seq Scan on stores (cost=0.00..1.09 rows=9 width=8) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Hash (cost=23,571,369.34..23,571,369.34 rows=144,856 width=24) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Hash Join (cost=22,551,254.46..23,571,369.34 rows=144,856 width=24) (actual rows= loops=)

  • Hash Cond: (products_stores.product_id = products.id)
15. 0.000 0.000 ↓ 0.0

Merge Right Join (cost=22,548,714.09..23,566,786.60 rows=158,348 width=16) (actual rows= loops=)

  • Merge Cond: ((product_stock.store_id = products_stores.store_id) AND (product_stock.product_id = products_stores.product_id))
16. 0.000 0.000 ↓ 0.0

Unique (cost=22,548,713.67..23,559,155.07 rows=173,112 width=24) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Sort (cost=22,548,713.67..22,885,527.47 rows=134,725,520 width=24) (actual rows= loops=)

  • Sort Key: product_stock.store_id, product_stock.product_id, product_stock.created_at DESC
18. 0.000 0.000 ↓ 0.0

Seq Scan on product_stock (cost=0.00..2,738,812.20 rows=134,725,520 width=24) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Index Only Scan using products_stores_store_id_product_id_unique on products_stores (cost=0.42..4,186.51 rows=158,348 width=8) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Hash (cost=2,263.50..2,263.50 rows=22,150 width=8) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Seq Scan on products (cost=0.00..2,263.50 rows=22,150 width=8) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Materialize (cost=7,808,794.78..7,826,198.01 rows=3,480,646 width=12) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Sort (cost=7,808,794.78..7,817,496.39 rows=3,480,646 width=12) (actual rows= loops=)

  • Sort Key: filtered_sale_id.product_id, filtered_sale_id.parent_store_id
24. 0.000 0.000 ↓ 0.0

Subquery Scan on filtered_sale_id (cost=6,891,071.16..7,395,764.71 rows=3,480,646 width=12) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=6,891,071.16..7,360,958.25 rows=3,480,646 width=16) (actual rows= loops=)

  • Group Key: sales_1.sale_date, sales_1.product_id, sales_1.parent_store_id
26. 0.000 0.000 ↓ 0.0

Sort (cost=6,891,071.16..6,978,087.28 rows=34,806,451 width=16) (actual rows= loops=)

  • Sort Key: sales_1.sale_date, sales_1.product_id, sales_1.parent_store_id
27. 0.000 0.000 ↓ 0.0

Index Scan using sales_sale_date_index on sales sales_1 (cost=0.57..2,182,661.24 rows=34,806,451 width=16) (actual rows= loops=)