explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BpNm

Settings
# exclusive inclusive rows x rows loops node
1. 2.039 23,183.782 ↑ 1.0 30,000 1

Limit (cost=33,717,837.33..33,717,987.33 rows=30,000 width=197) (actual time=23,166.878..23,183.782 rows=30,000 loops=1)

2. 7.747 23,181.743 ↑ 17.3 30,000 1

Unique (cost=33,717,837.33..33,720,428.45 rows=518,225 width=197) (actual time=23,166.877..23,181.743 rows=30,000 loops=1)

3. 486.245 23,173.996 ↑ 9.7 53,286 1

Sort (cost=33,717,837.33..33,719,132.89 rows=518,225 width=197) (actual time=23,166.876..23,173.996 rows=53,286 loops=1)

  • Sort Key: anon_1.customer_id DESC, product_price_history.price DESC
  • Sort Method: quicksort Memory: 79,044kB
4. 10,566.104 22,687.751 ↓ 1.3 697,180 1

Gather (cost=15,415,031.04..33,668,649.43 rows=518,225 width=197) (actual time=6,439.244..22,687.751 rows=697,180 loops=1)

  • Workers Planned: 3
  • Workers Launched: 3
5. 304.859 12,121.647 ↓ 1.0 174,295 4 / 4

Nested Loop Anti Join (cost=15,414,031.04..33,607,928.17 rows=167,169 width=197) (actual time=6,633.332..12,121.647 rows=174,295 loops=4)

6. 2,451.039 9,022.844 ↑ 1.2 174,622 4 / 4

Hash Join (cost=15,414,031.04..33,170,099.75 rows=203,001 width=136) (actual time=6,633.209..9,022.844 rows=174,622 loops=4)

  • Hash Cond: ((product_price_history.their_id)::text = anon_1.their_product_id)
  • Join Filter: (((product_price_history.store_id)::text = (anon_1.store_id)::text) OR (product_price_history.store_id IS NULL))
7. 6.338 6.673 ↓ 19.3 5,571 4 / 4

Parallel Bitmap Heap Scan on product_price_history (cost=442.60..54,918.49 rows=288 width=104) (actual time=0.401..6.673 rows=5,571 loops=4)

  • Recheck Cond: ((dt_updated > '2020-06-22 00:18:58.159085+00'::timestamp with time zone) AND (dt_updated <= '2020-06-22 12:19:27.646034+00'::timestamp with time zone))
  • Filter: ((change < '0'::numeric) AND (account_id = 992) AND (floor(((('-1'::numeric * change) / (price + ('-1'::numeric * change))) * 100.0)) >= '15'::numeric))
  • Rows Removed by Filter: 97
  • Heap Blocks: exact=917
8. 0.335 0.335 ↓ 1.1 22,673 1 / 4

Bitmap Index Scan on idx_product_price_history_dt_updated (cost=0.00..442.38 rows=20,995 width=0) (actual time=1.340..1.340 rows=22,673 loops=1)

  • Index Cond: ((dt_updated > '2020-06-22 00:18:58.159085+00'::timestamp with time zone) AND (dt_updated <= '2020-06-22 12:19:27.646034+00'::timestamp with time zone))
9. 1,646.890 6,565.132 ↑ 60.2 6,563,139 4 / 4

Hash (cost=7,000,906.19..7,000,906.19 rows=395,164,500 width=43) (actual time=6,565.132..6,565.132 rows=6,563,139 loops=4)

  • Buckets: 4,194,304 Batches: 256 Memory Usage: 34,069kB
10. 848.200 4,918.242 ↑ 60.2 6,563,139 4 / 4

Subquery Scan on anon_1 (cost=0.00..7,000,906.19 rows=395,164,500 width=43) (actual time=256.230..4,918.242 rows=6,563,139 loops=4)

11. 1,867.567 4,070.042 ↑ 60.2 6,563,139 4 / 4

ProjectSet (cost=0.00..3,049,261.19 rows=395,164,500 width=43) (actual time=256.229..4,070.042 rows=6,563,139 loops=4)

12. 334.555 2,202.475 ↑ 1.0 3,855,173 4 / 4

Result (cost=0.00..1,043,801.35 rows=3,951,645 width=51) (actual time=256.218..2,202.475 rows=3,855,173 loops=4)

13. 274.409 1,867.920 ↑ 1.0 3,855,173 4 / 4

Append (cost=0.00..1,004,284.90 rows=3,951,645 width=51) (actual time=256.216..1,867.920 rows=3,855,173 loops=4)

14. 0.011 0.011 ↓ 0.0 0 4 / 4

Seq Scan on events events_1 (cost=0.00..0.00 rows=1 width=122) (actual time=0.011..0.011 rows=0 loops=4)

  • Filter: ((customer_id IS NOT NULL) AND (dt_occurred >= '2020-05-11 00:19:27.646034+00'::timestamp with time zone) AND (dt_occurred < '2020-06-22 12:19:27.646034+00'::timestamp with time zone) AND (account_id = 992) AND ((event_type)::text = 'visit'::text))
15. 1,366.597 1,593.500 ↑ 1.0 3,855,173 4 / 4

Bitmap Heap Scan on events_visits (cost=114,916.02..1,004,284.90 rows=3,951,644 width=51) (actual time=256.204..1,593.500 rows=3,855,173 loops=4)

  • Recheck Cond: ((account_id = 992) AND (dt_occurred >= '2020-05-11 00:19:27.646034+00'::timestamp with time zone) AND (dt_occurred < '2020-06-22 12:19:27.646034+00'::timestamp with time zone))
  • Filter: ((customer_id IS NOT NULL) AND ((event_type)::text = 'visit'::text))
  • Heap Blocks: exact=140,919
16. 226.903 226.903 ↑ 1.0 3,855,173 4 / 4

Bitmap Index Scan on idx_events_visits_account_dt_occurred (cost=0.00..113,928.11 rows=3,951,644 width=0) (actual time=226.903..226.903 rows=3,855,173 loops=4)

  • Index Cond: ((account_id = 992) AND (dt_occurred >= '2020-05-11 00:19:27.646034+00'::timestamp with time zone) AND (dt_occurred < '2020-06-22 12:19:27.646034+00'::timestamp with time zone))
17. 0.000 2,793.944 ↓ 0.0 0 698,486 / 4

Append (cost=0.00..2.12 rows=2 width=10) (actual time=0.016..0.016 rows=0 loops=698,486)

18. 0.000 0.000 ↓ 0.0 0 698,486 / 4

Seq Scan on events (cost=0.00..0.00 rows=1 width=520) (actual time=0.000..0.000 rows=0 loops=698,486)

  • Filter: ((customer_id IS NOT NULL) AND (dt_occurred >= '2020-07-22 12:19:27.646034+00'::timestamp with time zone) AND (account_id = 992) AND ((event_type)::text = 'order_item'::text) AND (customer_id = anon_1.customer_id) AND ((product_id)::text = anon_1.their_product_id))
19. 2,793.944 2,793.944 ↓ 0.0 0 698,486 / 4

Index Scan using idx_events_orders_items_customer_id on events_orders_items (cost=0.56..2.12 rows=1 width=10) (actual time=0.016..0.016 rows=0 loops=698,486)

  • Index Cond: ((customer_id = anon_1.customer_id) AND (customer_id IS NOT NULL))
  • Filter: ((dt_occurred >= '2020-07-22 12:19:27.646034+00'::timestamp with time zone) AND (account_id = 992) AND ((event_type)::text = 'order_item'::text) AND ((product_id)::text = anon_1.their_product_id))
  • Rows Removed by Filter: 17