explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4fnc

Settings
# exclusive inclusive rows x rows loops node
1. 0.015 9,256.633 ↑ 2.6 17 1

Sort (cost=419,393.44..419,393.56 rows=45 width=68) (actual time=9,256.632..9,256.633 rows=17 loops=1)

  • Sort Key: (COALESCE((sum(p1.sum_quantity)), '0'::numeric)) DESC
  • Sort Method: quicksort Memory: 25kB
2. 0.011 9,256.618 ↑ 2.6 17 1

Merge Full Join (cost=419,261.84..419,392.21 rows=45 width=68) (actual time=9,255.702..9,256.618 rows=17 loops=1)

  • Merge Cond: (p1.emp_id = p1_1.emp_id)
3. 0.409 8,680.259 ↑ 4.1 11 1

GroupAggregate (cost=392,056.40..392,185.22 rows=45 width=36) (actual time=8,679.731..8,680.259 rows=11 loops=1)

  • Group Key: p1.emp_id
4. 0.468 8,679.850 ↑ 8.3 2,071 1

Sort (cost=392,056.40..392,099.15 rows=17,101 width=8) (actual time=8,679.684..8,679.850 rows=2,071 loops=1)

  • Sort Key: p1.emp_id
  • Sort Method: quicksort Memory: 194kB
5. 277.467 8,679.382 ↑ 8.3 2,071 1

Merge Join (cost=375,894.83..390,854.04 rows=17,101 width=8) (actual time=7,084.232..8,679.382 rows=2,071 loops=1)

  • Merge Cond: (((p2.barcode)::text = (p1.barcode)::text) AND ((p2.store_id)::text = (p1.store_id)::text))
6. 8,059.817 8,362.524 ↑ 1.0 1,909,289 1

Sort (cost=290,661.10..295,547.22 rows=1,954,449 width=24) (actual time=7,041.012..8,362.524 rows=1,909,289 loops=1)

  • Sort Key: p2.barcode, p2.store_id
  • Sort Method: external merge Disk: 64040kB
7. 302.707 302.707 ↑ 1.0 1,917,521 1

Seq Scan on productids_summaries p2 (cost=0.00..46,355.49 rows=1,954,449 width=24) (actual time=0.015..302.707 rows=1,917,521 loops=1)

8. 1.828 39.391 ↑ 11.4 2,070 1

Sort (cost=85,232.60..85,291.67 rows=23,627 width=31) (actual time=39.204..39.391 rows=2,070 loops=1)

  • Sort Key: p1.barcode, p1.store_id
  • Sort Method: quicksort Memory: 141kB
9. 37.563 37.563 ↑ 20.1 1,178 1

Index Scan using pk_products_summaries on products_summaries p1 (cost=0.56..83,516.32 rows=23,627 width=31) (actual time=35.220..37.563 rows=1,178 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))
10. 0.006 576.348 ↑ 2.2 10 1

Materialize (cost=27,205.44..27,206.15 rows=22 width=36) (actual time=575.967..576.348 rows=10 loops=1)

11. 0.311 576.342 ↑ 2.2 10 1

GroupAggregate (cost=27,205.44..27,205.88 rows=22 width=36) (actual time=575.965..576.342 rows=10 loops=1)

  • Group Key: p1_1.emp_id
12. 0.392 576.031 ↓ 69.5 1,529 1

Sort (cost=27,205.44..27,205.49 rows=22 width=8) (actual time=575.943..576.031 rows=1,529 loops=1)

  • Sort Key: p1_1.emp_id
  • Sort Method: quicksort Memory: 120kB
13. 1.025 575.639 ↓ 69.5 1,529 1

Nested Loop (cost=1.11..27,204.95 rows=22 width=8) (actual time=15.208..575.639 rows=1,529 loops=1)

14. 43.636 43.636 ↓ 26.1 782 1

Index Scan using pk_products_summaries on products_summaries p1_1 (cost=0.56..26,364.27 rows=30 width=31) (actual time=14.748..43.636 rows=782 loops=1)

  • Index Cond: (((store_id)::text = ANY ('{82682-82069}'::text[])) AND (date >= '2018-11-07 00:00:00+01'::timestamp with time zone) AND (date <= '2018-11-07 23:59:59+01'::timestamp with time zone))
15. 530.978 530.978 ↓ 2.0 2 782

Index Only Scan using pk_productids_summaries on productids_summaries p2_1 (cost=0.55..28.01 rows=1 width=24) (actual time=0.335..0.679 rows=2 loops=782)

  • Index Cond: ((store_id = (p1_1.store_id)::text) AND (barcode = (p1_1.barcode)::text))
  • Heap Fetches: 1529
Planning time : 5.179 ms
Execution time : 9,272.056 ms