explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mUWs

Settings
# exclusive inclusive rows x rows loops node
1. 0.069 853.989 ↓ 153.0 153 1

Sort (cost=119,108.31..119,108.32 rows=1 width=564) (actual time=853.980..853.989 rows=153 loops=1)

  • Sort Key: (COALESCE(((sum((p1.sum_sale_amount_ttc - p1.sum_discount_amount_ttc))))::double precision, '0'::double precision)) DESC
  • Sort Method: quicksort Memory: 46kB
2.          

CTE productidentifiant

3. 1.784 98.070 ↓ 3,386.0 6,772 1

Group (cost=32,746.90..32,746.92 rows=2 width=37) (actual time=95.261..98.070 rows=6,772 loops=1)

  • Group Key: x.barcode, x.libelle, x.category_id_from_store
4. 15.535 96.286 ↓ 3,386.0 6,772 1

Sort (cost=32,746.90..32,746.90 rows=2 width=37) (actual time=95.260..96.286 rows=6,772 loops=1)

  • Sort Key: x.barcode, x.libelle, x.category_id_from_store
  • Sort Method: quicksort Memory: 858kB
5. 8.944 80.751 ↓ 3,386.0 6,772 1

Merge Join (cost=32,633.11..32,746.89 rows=2 width=37) (actual time=69.967..80.751 rows=6,772 loops=1)

  • Merge Cond: (((x.barcode)::text = (y.barcode)::text) AND (x.date = y.date))
6. 31.740 37.760 ↑ 1.1 7,158 1

Sort (cost=16,215.39..16,234.45 rows=7,624 width=45) (actual time=36.837..37.760 rows=7,158 loops=1)

  • Sort Key: x.barcode, x.date
  • Sort Method: quicksort Memory: 1028kB
7. 6.020 6.020 ↑ 1.1 7,158 1

Index Scan using pk_productids_summaries on productids_summaries x (cost=0.56..15,723.78 rows=7,624 width=45) (actual time=0.042..6.020 rows=7,158 loops=1)

  • Index Cond: ((store_id)::text = ANY ('{82682-82069}'::text[]))
8. 23.558 34.047 ↑ 1.1 6,772 1

Sort (cost=16,417.72..16,436.58 rows=7,544 width=20) (actual time=33.122..34.047 rows=6,772 loops=1)

  • Sort Key: y.barcode, y.date
  • Sort Method: quicksort Memory: 680kB
9. 0.935 10.489 ↑ 1.2 6,518 1

Subquery Scan on y (cost=15,780.96..15,931.84 rows=7,544 width=20) (actual time=7.688..10.489 rows=6,518 loops=1)

10. 6.706 9.554 ↑ 1.2 6,518 1

HashAggregate (cost=15,780.96..15,856.40 rows=7,544 width=32) (actual time=7.688..9.554 rows=6,518 loops=1)

  • Group Key: productids_summaries.store_id, productids_summaries.barcode
11. 2.848 2.848 ↑ 1.1 7,158 1

Index Scan using pk_productids_summaries on productids_summaries (cost=0.56..15,723.78 rows=7,624 width=32) (actual time=0.021..2.848 rows=7,158 loops=1)

  • Index Cond: ((store_id)::text = ANY ('{82682-82069}'::text[]))
12. 0.198 853.920 ↓ 153.0 153 1

Merge Full Join (cost=86,361.24..86,361.38 rows=1 width=564) (actual time=853.453..853.920 rows=153 loops=1)

  • Merge Cond: ((p2.libelle)::text = (p2_1.libelle)::text)
13. 0.181 511.755 ↓ 95.0 95 1

GroupAggregate (cost=53,945.38..53,945.43 rows=1 width=612) (actual time=511.593..511.755 rows=95 loops=1)

  • Group Key: p2.libelle
14. 0.146 511.574 ↓ 95.0 95 1

Sort (cost=53,945.38..53,945.38 rows=1 width=533) (actual time=511.565..511.574 rows=95 loops=1)

  • Sort Key: p2.libelle
  • Sort Method: quicksort Memory: 35kB
15. 263.485 511.428 ↓ 95.0 95 1

Nested Loop (cost=0.69..53,945.37 rows=1 width=533) (actual time=146.359..511.428 rows=95 loops=1)

  • Join Filter: ((p1.barcode)::text = (p2.barcode)::text)
  • Rows Removed by Join Filter: 1989725
16. 101.055 101.055 ↓ 854.0 1,708 1

CTE Scan on productidentifiant p2 (cost=0.00..0.04 rows=2 width=662) (actual time=95.275..101.055 rows=1,708 loops=1)

  • Filter: ((category_id_from_store)::text = ANY ('{54,4500}'::text[]))
  • Rows Removed by Filter: 5064
17. 97.126 146.888 ↑ 4.6 1,165 1,708

Materialize (cost=0.69..53,798.14 rows=5,352 width=29) (actual time=0.027..0.086 rows=1,165 loops=1,708)

18. 49.762 49.762 ↑ 4.6 1,165 1

Index Scan using pk_products_summaries on products_summaries p1 (cost=0.69..53,771.38 rows=5,352 width=29) (actual time=45.944..49.762 rows=1,165 loops=1)

  • Index Cond: (((store_id)::text = ANY ('{82682-82069}'::text[])) AND (date >= '2019-10-05 00:00:00+02'::timestamp with time zone) AND (date <= '2019-10-05 23:59:59+02'::timestamp with time zone))
19. 0.011 341.967 ↓ 65.0 65 1

Materialize (cost=32,415.86..32,415.92 rows=1 width=612) (actual time=341.842..341.967 rows=65 loops=1)

20. 0.137 341.956 ↓ 65.0 65 1

GroupAggregate (cost=32,415.86..32,415.91 rows=1 width=612) (actual time=341.838..341.956 rows=65 loops=1)

  • Group Key: p2_1.libelle
21. 0.108 341.819 ↓ 70.0 70 1

Sort (cost=32,415.86..32,415.86 rows=1 width=533) (actual time=341.810..341.819 rows=70 loops=1)

  • Sort Key: p2_1.libelle
  • Sort Method: quicksort Memory: 33kB
22. 200.330 341.711 ↓ 70.0 70 1

Nested Loop (cost=0.69..32,415.85 rows=1 width=533) (actual time=55.077..341.711 rows=70 loops=1)

  • Join Filter: ((p1_1.barcode)::text = (p2_1.barcode)::text)
  • Rows Removed by Join Filter: 1596910
23. 1.325 1.325 ↓ 854.0 1,708 1

CTE Scan on productidentifiant p2_1 (cost=0.00..0.04 rows=2 width=662) (actual time=0.004..1.325 rows=1,708 loops=1)

  • Filter: ((category_id_from_store)::text = ANY ('{54,4500}'::text[]))
  • Rows Removed by Filter: 5064
24. 85.391 140.056 ↓ 40.7 935 1,708

Materialize (cost=0.69..32,415.17 rows=23 width=29) (actual time=0.012..0.082 rows=935 loops=1,708)

25. 54.665 54.665 ↓ 40.7 935 1

Index Scan using pk_products_summaries on products_summaries p1_1 (cost=0.69..32,415.06 rows=23 width=29) (actual time=20.327..54.665 rows=935 loops=1)

  • Index Cond: (((store_id)::text = ANY ('{82682-82069}'::text[])) AND (date >= '2018-11-08 00:00:00+01'::timestamp with time zone) AND (date <= '2018-11-08 23:59:59+01'::timestamp with time zone))
Planning time : 1.559 ms
Execution time : 854.407 ms