explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dWpG

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.049 47,945.343 ↓ 53.0 53 1

Sort (cost=98,950.16..98,950.17 rows=1 width=564) (actual time=47,945.341..47,945.343 rows=53 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: 32kB
2.          

CTE productidentifiant

3. 2.440 96.027 ↓ 3,386.5 6,773 1

Group (cost=32,746.90..32,746.92 rows=2 width=37) (actual time=91.615..96.027 rows=6,773 loops=1)

  • Group Key: x.barcode, x.libelle, x.category_id_from_store
4. 16.209 93.587 ↓ 3,386.5 6,773 1

Sort (cost=32,746.90..32,746.90 rows=2 width=37) (actual time=91.612..93.587 rows=6,773 loops=1)

  • Sort Key: x.barcode, x.libelle, x.category_id_from_store
  • Sort Method: quicksort Memory: 858kB
5. 8.776 77.378 ↓ 3,386.5 6,773 1

Merge Join (cost=32,633.11..32,746.89 rows=2 width=37) (actual time=66.822..77.378 rows=6,773 loops=1)

  • Merge Cond: (((x.barcode)::text = (y.barcode)::text) AND (x.date = y.date))
6. 30.288 35.305 ↑ 1.1 7,159 1

Sort (cost=16,215.39..16,234.45 rows=7,624 width=45) (actual time=34.375..35.305 rows=7,159 loops=1)

  • Sort Key: x.barcode, x.date
  • Sort Method: quicksort Memory: 1028kB
7. 5.017 5.017 ↑ 1.1 7,159 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.034..5.017 rows=7,159 loops=1)

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

Sort (cost=16,417.72..16,436.58 rows=7,544 width=20) (actual time=32.440..33.297 rows=6,773 loops=1)

  • Sort Key: y.barcode, y.date
  • Sort Method: quicksort Memory: 680kB
9. 0.949 10.322 ↑ 1.2 6,519 1

Subquery Scan on y (cost=15,780.96..15,931.84 rows=7,544 width=20) (actual time=7.670..10.322 rows=6,519 loops=1)

10. 6.464 9.373 ↑ 1.2 6,519 1

HashAggregate (cost=15,780.96..15,856.40 rows=7,544 width=32) (actual time=7.669..9.373 rows=6,519 loops=1)

  • Group Key: productids_summaries.store_id, productids_summaries.barcode
11. 2.909 2.909 ↑ 1.1 7,159 1

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

  • Index Cond: ((store_id)::text = ANY ('{82682-82069}'::text[]))
12. 0.094 47,945.294 ↓ 53.0 53 1

Merge Full Join (cost=66,203.08..66,203.23 rows=1 width=564) (actual time=47,945.123..47,945.294 rows=53 loops=1)

  • Merge Cond: ((p2.libelle)::text = (p2_1.libelle)::text)
13. 0.111 22,707.793 ↓ 31.0 31 1

GroupAggregate (cost=33,787.53..33,787.57 rows=1 width=612) (actual time=22,707.737..22,707.793 rows=31 loops=1)

  • Group Key: p2.libelle
14. 0.148 22,707.682 ↓ 31.0 31 1

Sort (cost=33,787.53..33,787.53 rows=1 width=533) (actual time=22,707.677..22,707.682 rows=31 loops=1)

  • Sort Key: p2.libelle
  • Sort Method: quicksort Memory: 28kB
15. 3.053 22,707.534 ↓ 31.0 31 1

Nested Loop (cost=0.69..33,787.52 rows=1 width=533) (actual time=332.966..22,707.534 rows=31 loops=1)

16. 100.459 100.459 ↓ 567.0 567 1

CTE Scan on productidentifiant p2 (cost=0.00..0.04 rows=1 width=662) (actual time=91.802..100.459 rows=567 loops=1)

  • Filter: ((category_id_from_store)::text = ANY ('{54}'::text[]))
  • Rows Removed by Filter: 6206
17. 22,604.022 22,604.022 ↓ 0.0 0 567

Index Scan using pk_products_summaries on products_summaries p1 (cost=0.69..33,787.46 rows=1 width=29) (actual time=39.715..39.866 rows=0 loops=567)

  • 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) AND ((barcode)::text = (p2.ba
18. 0.010 25,237.407 ↓ 24.0 24 1

Materialize (cost=32,415.56..32,415.62 rows=1 width=612) (actual time=25,237.361..25,237.407 rows=24 loops=1)

19. 0.086 25,237.397 ↓ 24.0 24 1

GroupAggregate (cost=32,415.56..32,415.61 rows=1 width=612) (actual time=25,237.355..25,237.397 rows=24 loops=1)

  • Group Key: p2_1.libelle
20. 0.115 25,237.311 ↓ 24.0 24 1

Sort (cost=32,415.56..32,415.56 rows=1 width=533) (actual time=25,237.309..25,237.311 rows=24 loops=1)

  • Sort Key: p2_1.libelle
  • Sort Method: quicksort Memory: 27kB
21. 45.963 25,237.196 ↓ 24.0 24 1

Nested Loop (cost=0.69..32,415.55 rows=1 width=533) (actual time=193.693..25,237.196 rows=24 loops=1)

  • Join Filter: ((p1_1.barcode)::text = (p2_1.barcode)::text)
  • Rows Removed by Join Filter: 530121
22. 3.392 3.392 ↓ 567.0 567 1

CTE Scan on productidentifiant p2_1 (cost=0.00..0.04 rows=1 width=662) (actual time=0.034..3.392 rows=567 loops=1)

  • Filter: ((category_id_from_store)::text = ANY ('{54}'::text[]))
  • Rows Removed by Filter: 6206
23. 25,187.841 25,187.841 ↓ 40.7 935 567

Index Scan using pk_products_summaries on products_summaries p1_1 (cost=0.69..32,415.22 rows=23 width=29) (actual time=16.678..44.423 rows=935 loops=567)

  • 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.453 ms
Execution time : 47,945.892 ms