explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YI4K

Settings
# exclusive inclusive rows x rows loops node
1. 0.037 213,967.884 ↓ 70.0 70 1

Nested Loop (cost=197,591.86..197,596.21 rows=1 width=514) (actual time=213,907.008..213,967.884 rows=70 loops=1)

2. 1.339 213,967.777 ↓ 70.0 70 1

Subquery Scan on cs (cost=197,591.58..197,593.90 rows=1 width=141) (actual time=213,906.991..213,967.777 rows=70 loops=1)

  • Filter: ((cs.date >= '2019-04-01'::date) AND (cs.date <= '2019-06-30'::date) AND (cs.pharmacy_id = 6573))
  • Rows Removed by Filter: 31070
3. 8.666 213,966.438 ↓ 1,004.5 31,140 1

WindowAgg (cost=197,591.58..197,593.36 rows=31 width=141) (actual time=213,900.985..213,966.438 rows=31,140 loops=1)

4. 52.139 213,957.772 ↓ 1,004.5 31,140 1

GroupAggregate (cost=197,591.58..197,592.97 rows=31 width=133) (actual time=213,900.977..213,957.772 rows=31,140 loops=1)

  • Group Key: props.discriminator, cs_1.pharmacy_id, cs_1.date, cs_1.cashier_id
5. 91.819 213,905.633 ↓ 2,353.7 72,964 1

Sort (cost=197,591.58..197,591.66 rows=31 width=55) (actual time=213,900.963..213,905.633 rows=72,964 loops=1)

  • Sort Key: props.discriminator, cs_1.pharmacy_id, cs_1.date, cs_1.cashier_id
  • Sort Method: quicksort Memory: 11307kB
6. 134,072.369 213,813.814 ↓ 2,353.7 72,964 1

Gather (cost=13,332.17..197,590.81 rows=31 width=55) (actual time=992.594..213,813.814 rows=72,964 loops=1)

  • Workers Planned: 3
  • Workers Launched: 3
7. 3,312.719 79,741.445 ↓ 1,824.1 18,241 4

Nested Loop (cost=12,332.17..196,587.71 rows=10 width=55) (actual time=439.305..79,741.445 rows=18,241 loops=4)

  • Join Filter: (cs_1.pharmacy_id = pi.pharmacy_id)
  • Rows Removed by Join Filter: 18427809
8. 76,203.579 76,428.715 ↓ 636.8 211,422 4

Nested Loop (cost=12,332.17..196,449.97 rows=332 width=71) (actual time=30.210..76,428.715 rows=211,422 loops=4)

9. 3.451 33.631 ↓ 199.5 399 4

Nested Loop (cost=12,331.60..12,333.17 rows=2 width=62) (actual time=29.356..33.631 rows=399 loops=4)

10. 0.610 30.174 ↓ 199.5 399 4

Merge Join (cost=12,331.31..12,331.81 rows=2 width=49) (actual time=29.316..30.174 rows=399 loops=4)

  • Merge Cond: (props.initiative_id = i.id)
11. 0.352 29.508 ↑ 1.2 399 4

Sort (cost=12,326.58..12,327.83 rows=498 width=33) (actual time=29.267..29.508 rows=399 loops=4)

  • Sort Key: props.initiative_id
  • Sort Method: quicksort Memory: 136kB
  • Worker 0: Sort Method: quicksort Memory: 43kB
  • Worker 1: Sort Method: quicksort Memory: 33kB
  • Worker 2: Sort Method: quicksort Memory: 35kB
12. 29.156 29.156 ↑ 1.2 399 4

Parallel Seq Scan on mktg_initiative_proplines props (cost=0.00..12,304.27 rows=498 width=33) (actual time=1.544..29.156 rows=399 loops=4)

  • Filter: ((discriminator)::text = ANY ('{PRODUCT_OF_THE_DAY,PHARMACIST_MOTIVATION,PRIVATE_LABEL}'::text[]))
  • Rows Removed by Filter: 167355
13. 0.028 0.056 ↑ 1.3 31 4

Sort (cost=2.51..2.61 rows=41 width=16) (actual time=0.045..0.056 rows=31 loops=4)

  • Sort Key: i.id
  • Sort Method: quicksort Memory: 26kB
  • Worker 0: Sort Method: quicksort Memory: 26kB
  • Worker 1: Sort Method: quicksort Memory: 26kB
  • Worker 2: Sort Method: quicksort Memory: 26kB
14. 0.028 0.028 ↑ 1.0 41 4

Seq Scan on product_of_the_day_initiative i (cost=0.00..1.41 rows=41 width=16) (actual time=0.023..0.028 rows=41 loops=4)

15. 0.006 0.006 ↑ 1.0 1 1,595

Index Scan using promotional_product_snapshot_pkey on promotional_product_snapshot snap (cost=0.29..0.68 rows=1 width=29) (actual time=0.006..0.006 rows=1 loops=1,595)

  • Index Cond: (id = props.promotional_product_snapshot_id)
16. 191.505 191.505 ↑ 1.0 530 1,595

Index Scan using cashier_sale_date_pharmacy_id_details_cashier_id_idx on cashier_sale cs_1 (cost=0.56..92,053.00 rows=540 width=33) (actual time=11.946..191.505 rows=530 loops=1,595)

  • Index Cond: ((date >= i.start_date) AND (date <= i.expiration_date) AND (details = snap.promotional_product_details_id))
17. 0.011 0.011 ↓ 21.8 87 845,687

Index Scan using pharmacy_initiative_marketing_action_id_idx on pharmacy_initiative pi (cost=0.00..0.36 rows=4 width=16) (actual time=0.001..0.011 rows=87 loops=845,687)

  • Index Cond: (marketing_action_id = props.initiative_id)
18. 0.070 0.070 ↑ 1.0 1 70

Index Scan using pharmacy_pkey on pharmacy p (cost=0.28..2.30 rows=1 width=373) (actual time=0.001..0.001 rows=1 loops=70)

  • Index Cond: (id = 6573)
Planning time : 18.039 ms
Execution time : 213,968.066 ms