explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 88pW

Settings
# exclusive inclusive rows x rows loops node
1. 0.223 41,611.637 ↑ 41,280.0 1 1

GroupAggregate (cost=4,374,142.68..4,376,413.08 rows=41,280 width=168) (actual time=41,611.637..41,611.637 rows=1 loops=1)

  • Output: (COALESCE(a.sku, listings.seller_sku)), sum((COALESCE(a.quantity, '0'::bigint))), sum((COALESCE(a.item_price, '0'::numeric))), sum(CASE WHEN ((COALESCE(a_1.quantity, '0'::bigint)) > 0) THEN (COALESCE(a.quantity, '0'::bigint)) ELSE NULL::bigint END), sum(CASE WHEN ((COALESCE(a_1.quantity, '0'::bigint)) > 0) THEN (COALESCE(a.item_price, '0'::numeric)) ELSE NULL::numeric END), ((count(CASE WHEN ((COALESCE(a_1.quantity, '0'::bigint)) > 0) THEN 1 ELSE NULL::integer END))::double precision / (count((COALESCE((a.snapshot_date)::timestamp with time zone, dates.dates))))::double precision)
  • Group Key: (COALESCE(a.sku, listings.seller_sku))
  • Buffers: shared hit=1,025 read=18,735, temp read=134,699 written=134,959
2. 0.396 41,611.414 ↑ 113.1 365 1

Sort (cost=4,374,142.68..4,374,245.88 rows=41,280 width=88) (actual time=41,611.376..41,611.414 rows=365 loops=1)

  • Output: (COALESCE(a.sku, listings.seller_sku)), (COALESCE(a.quantity, '0'::bigint)), (COALESCE(a.item_price, '0'::numeric)), (COALESCE(a_1.quantity, '0'::bigint)), (COALESCE((a.snapshot_date)::timestamp with time zone, dates.dates))
  • Sort Key: (COALESCE(a.sku, listings.seller_sku))
  • Sort Method: quicksort Memory: 53kB
  • Buffers: shared hit=1,025 read=18,735, temp read=134,699 written=134,959
3. 1,797.304 41,611.018 ↑ 113.1 365 1

Merge Full Join (cost=1,176,583.42..4,369,000.42 rows=41,280 width=88) (actual time=40,313.659..41,611.018 rows=365 loops=1)

  • Output: (COALESCE(a.sku, listings.seller_sku)), (COALESCE(a.quantity, '0'::bigint)), (COALESCE(a.item_price, '0'::numeric)), (COALESCE(a_1.quantity, '0'::bigint)), (COALESCE((a.snapshot_date)::timestamp with time zone, dates.dates))
  • Merge Cond: ((((COALESCE((a.snapshot_date)::timestamp with time zone, dates.dates))::date) = ((COALESCE((a_1.snapshot_date)::timestamp with time zone, dates_1.dates))::date)) AND (((COALESCE(a.sku, listings.seller_sku))::text) = ((COALESCE(a_1.sku, listings_1.seller_sku))::text)))
  • Filter: ((((COALESCE(a.sku, listings.seller_sku)))::text = 'ED-5TRW-48IF'::text) AND ((COALESCE((a.snapshot_date)::timestamp with time zone, dates.dates)) < now()) AND ((COALESCE((a.snapshot_date)::timestamp with time zone, dates.dates)) > (now() - '1 year'::interval)))
  • Rows Removed by Filter: 2,277,461
  • Buffers: shared hit=1,025 read=18,735, temp read=134,699 written=134,959
4. 7,700.465 29,001.235 ↓ 1.3 2,273,178 1

Sort (cost=520,643.18..524,953.18 rows=1,724,000 width=118) (actual time=27,275.211..29,001.235 rows=2,273,178 loops=1)

  • Output: a.sku, a.snapshot_date, dates.dates, listings.seller_sku, (COALESCE(a.sku, listings.seller_sku)), (COALESCE(a.quantity, '0'::bigint)), (COALESCE(a.item_price, '0'::numeric)), (COALESCE((a.snapshot_date)::timestamp with time zone, dates.dates)), ((COALESCE((a.snapshot_date)::timestamp with time zone, dates.dates))::date), ((COALESCE(a.sku, listings.seller_sku))::text)
  • Sort Key: ((COALESCE((a.snapshot_date)::timestamp with time zone, dates.dates))::date), ((COALESCE(a.sku, listings.seller_sku))::text)
  • Sort Method: external merge Disk: 221,400kB
  • Buffers: shared hit=769 read=12,962, temp read=76,589 written=76,691
5. 19,972.896 21,300.770 ↓ 1.3 2,273,178 1

Hash Full Join (cost=75,938.45..129,924.81 rows=1,724,000 width=118) (actual time=599.391..21,300.770 rows=2,273,178 loops=1)

  • Output: a.sku, a.snapshot_date, dates.dates, listings.seller_sku, COALESCE(a.sku, listings.seller_sku), COALESCE(a.quantity, '0'::bigint), COALESCE(a.item_price, '0'::numeric), COALESCE((a.snapshot_date)::timestamp with time zone, dates.dates), (COALESCE((a.snapshot_date)::timestamp with time zone, dates.dates))::date, COALESCE(a.sku, listings.seller_sku)
  • Inner Unique: true
  • Hash Cond: ((listings.seller_sku)::text = (a.sku)::text)
  • Join Filter: (dates.dates = a.snapshot_date)
  • Rows Removed by Join Filter: 86,570,289
  • Buffers: shared hit=769 read=12,962, temp read=12,674 written=12,682
6. 504.675 730.789 ↓ 1.3 2,271,948 1

Nested Loop (cost=81.57..21,680.36 rows=1,724,000 width=21) (actual time=1.730..730.789 rows=2,271,948 loops=1)

  • Output: dates.dates, listings.seller_sku
  • Buffers: shared hit=60
7. 0.966 0.966 ↓ 1.5 1,462 1

Function Scan on pg_catalog.generate_series dates (cost=0.02..10.02 rows=1,000 width=8) (actual time=0.545..0.966 rows=1,462 loops=1)

  • Output: dates.dates
  • Function Call: generate_series((((now() - '4 years'::interval))::date)::timestamp with time zone, ((now())::date)::timestamp with time zone, '1 day'::interval)
8. 223.642 225.148 ↑ 1.1 1,554 1,462

Materialize (cost=81.55..124.65 rows=1,724 width=13) (actual time=0.001..0.154 rows=1,554 loops=1,462)

  • Output: listings.seller_sku
  • Buffers: shared hit=60
9. 0.984 1.506 ↑ 1.1 1,554 1

HashAggregate (cost=81.55..98.79 rows=1,724 width=13) (actual time=1.178..1.506 rows=1,554 loops=1)

  • Output: listings.seller_sku
  • Group Key: listings.seller_sku
  • Buffers: shared hit=60
10. 0.522 0.522 ↑ 1.1 1,554 1

Seq Scan on public.listings (cost=0.00..77.24 rows=1,724 width=13) (actual time=0.017..0.522 rows=1,554 loops=1)

  • Output: listings.id, listings.item_name, listings.item_description, listings.listing_id, listings.seller_sku, listings.price, listings.quantity, listings.open_date, listings.image_url, listings.item_is_marketplace, listings.product_id_type, listings.zshop_shipping_fee, listings.item_note, listings.item_condition, listings.zshop_category1, listings.zshop_browse_path, listings.zshop_storefront_feature, listings.asin1, listings.asin2, listings.asin3, listings.will_ship_internationally, listings.expedited_shipping, listings.zshop_boldface, listings.product_id, listings.bid_for_featured_placement, listings.add_delete, listings.pending_quantity, listings.fulfillment_channel, listings.merchant_shipping_group, listings.status, listings.mws_credential_id, listings.created_at, listings.updated_at, listings.optional_payment_type_exclusion
  • Buffers: shared hit=60
11. 21.037 597.085 ↑ 4.9 63,301 1

Hash (cost=68,613.36..68,613.36 rows=311,642 width=57) (actual time=597.085..597.085 rows=63,301 loops=1)

  • Output: a.sku, a.snapshot_date, a.quantity, a.item_price
  • Buckets: 65,536 Batches: 16 Memory Usage: 840kB
  • Buffers: shared hit=709 read=12,962, temp read=1,632 written=2,053
12. 9.585 576.048 ↑ 4.9 63,301 1

Subquery Scan on a (cost=56,860.02..68,613.36 rows=311,642 width=57) (actual time=425.712..576.048 rows=63,301 loops=1)

  • Output: a.sku, a.snapshot_date, a.quantity, a.item_price
  • Buffers: shared hit=709 read=12,962, temp read=1,632 written=1,640
13. 95.393 566.463 ↑ 4.9 63,301 1

GroupAggregate (cost=56,860.02..65,496.94 rows=311,642 width=57) (actual time=425.710..566.463 rows=63,301 loops=1)

  • Output: ((all_orders.purchase_date)::date), all_orders.sku, sum(all_orders.quantity), sum(all_orders.item_price)
  • Group Key: ((all_orders.purchase_date)::date), all_orders.sku
  • Buffers: shared hit=709 read=12,962, temp read=1,632 written=1,640
14. 262.786 471.070 ↓ 1.0 325,065 1

Sort (cost=56,860.02..57,652.48 rows=316,983 width=27) (actual time=425.685..471.070 rows=325,065 loops=1)

  • Output: ((all_orders.purchase_date)::date), all_orders.sku, all_orders.quantity, all_orders.item_price
  • Sort Key: ((all_orders.purchase_date)::date), all_orders.sku
  • Sort Method: external merge Disk: 13,056kB
  • Buffers: shared hit=709 read=12,962, temp read=1,632 written=1,640
15. 208.284 208.284 ↓ 1.0 325,065 1

Seq Scan on public.all_orders (cost=0.00..20,312.71 rows=316,983 width=27) (actual time=0.017..208.284 rows=325,065 loops=1)

  • Output: (all_orders.purchase_date)::date, all_orders.sku, all_orders.quantity, all_orders.item_price
  • Filter: (((all_orders.sales_channel)::text = 'Amazon.com'::text) AND ((all_orders.order_status)::text = 'Shipped'::text) AND ((all_orders.item_status)::text = 'Shipped'::text))
  • Rows Removed by Filter: 8,765
  • Buffers: shared hit=709 read=12,962
16. 470.990 10,812.479 ↓ 1.3 2,276,899 1

Materialize (cost=655,940.24..664,560.24 rows=1,724,000 width=46) (actual time=9,223.511..10,812.479 rows=2,276,899 loops=1)

  • Output: a_1.sku, a_1.snapshot_date, dates_1.dates, listings_1.seller_sku, (COALESCE(a_1.quantity, '0'::bigint)), ((COALESCE((a_1.snapshot_date)::timestamp with time zone, dates_1.dates))::date), ((COALESCE(a_1.sku, listings_1.seller_sku))::text)
  • Buffers: shared hit=256 read=5,773, temp read=58,110 written=58,268
17. 4,853.244 10,341.489 ↓ 1.3 2,276,899 1

Sort (cost=655,940.24..660,250.24 rows=1,724,000 width=46) (actual time=9,223.505..10,341.489 rows=2,276,899 loops=1)

  • Output: a_1.sku, a_1.snapshot_date, dates_1.dates, listings_1.seller_sku, (COALESCE(a_1.quantity, '0'::bigint)), ((COALESCE((a_1.snapshot_date)::timestamp with time zone, dates_1.dates))::date), ((COALESCE(a_1.sku, listings_1.seller_sku))::text)
  • Sort Key: ((COALESCE((a_1.snapshot_date)::timestamp with time zone, dates_1.dates))::date), ((COALESCE(a_1.sku, listings_1.seller_sku))::text)
  • Sort Method: external merge Disk: 133,208kB
  • Buffers: shared hit=256 read=5,773, temp read=58,110 written=58,268
18. 1,148.189 5,488.245 ↓ 1.3 2,276,899 1

Merge Full Join (cost=352,184.23..371,285.87 rows=1,724,000 width=46) (actual time=3,717.329..5,488.245 rows=2,276,899 loops=1)

  • Output: a_1.sku, a_1.snapshot_date, dates_1.dates, listings_1.seller_sku, COALESCE(a_1.quantity, '0'::bigint), (COALESCE((a_1.snapshot_date)::timestamp with time zone, dates_1.dates))::date, COALESCE(a_1.sku, listings_1.seller_sku)
  • Merge Cond: (((listings_1.seller_sku)::text = (a_1.sku)::text) AND (dates_1.dates = a_1.snapshot_date))
  • Buffers: shared hit=256 read=5,773, temp read=19,638 written=19,702
19. 2,997.076 3,736.678 ↓ 1.3 2,271,948 1

Sort (cost=270,977.73..275,287.73 rows=1,724,000 width=21) (actual time=3,127.237..3,736.678 rows=2,271,948 loops=1)

  • Output: dates_1.dates, listings_1.seller_sku
  • Sort Key: listings_1.seller_sku, dates_1.dates
  • Sort Method: external merge Disk: 69,024kB
  • Buffers: shared hit=67, temp read=17,746 written=17,804
20. 506.426 739.602 ↓ 1.3 2,271,948 1

Nested Loop (cost=81.57..21,680.36 rows=1,724,000 width=21) (actual time=1.142..739.602 rows=2,271,948 loops=1)

  • Output: dates_1.dates, listings_1.seller_sku
  • Buffers: shared hit=60
21. 0.718 0.718 ↓ 1.5 1,462 1

Function Scan on pg_catalog.generate_series dates_1 (cost=0.02..10.02 rows=1,000 width=8) (actual time=0.377..0.718 rows=1,462 loops=1)

  • Output: dates_1.dates
  • Function Call: generate_series((((now() - '4 years'::interval))::date)::timestamp with time zone, ((now())::date)::timestamp with time zone, '1 day'::interval)
22. 231.439 232.458 ↑ 1.1 1,554 1,462

Materialize (cost=81.55..124.65 rows=1,724 width=13) (actual time=0.001..0.159 rows=1,554 loops=1,462)

  • Output: listings_1.seller_sku
  • Buffers: shared hit=60
23. 0.771 1.019 ↑ 1.1 1,554 1

HashAggregate (cost=81.55..98.79 rows=1,724 width=13) (actual time=0.761..1.019 rows=1,554 loops=1)

  • Output: listings_1.seller_sku
  • Group Key: listings_1.seller_sku
  • Buffers: shared hit=60
24. 0.248 0.248 ↑ 1.1 1,554 1

Seq Scan on public.listings listings_1 (cost=0.00..77.24 rows=1,724 width=13) (actual time=0.007..0.248 rows=1,554 loops=1)

  • Output: listings_1.id, listings_1.item_name, listings_1.item_description, listings_1.listing_id, listings_1.seller_sku, listings_1.price, listings_1.quantity, listings_1.open_date, listings_1.image_url, listings_1.item_is_marketplace, listings_1.product_id_type, listings_1.zshop_shipping_fee, listings_1.item_note, listings_1.item_condition, listings_1.zshop_category1, listings_1.zshop_browse_path, listings_1.zshop_storefront_feature, listings_1.asin1, listings_1.asin2, listings_1.asin3, listings_1.will_ship_internationally, listings_1.expedited_shipping, listings_1.zshop_boldface, listings_1.product_id, listings_1.bid_for_featured_placement, listings_1.add_delete, listings_1.pending_quantity, listings_1.fulfillment_channel, listings_1.merchant_shipping_group, listings_1.status, listings_1.mws_credential_id, listings_1.created_at, listings_1.updated_at, listings_1.optional_payment_type_exclusion
  • Buffers: shared hit=60
25. 112.426 603.378 ↑ 1.0 70,183 1

Sort (cost=81,206.50..81,382.83 rows=70,533 width=25) (actual time=590.080..603.378 rows=70,183 loops=1)

  • Output: a_1.sku, a_1.snapshot_date, a_1.quantity
  • Sort Key: a_1.sku, a_1.snapshot_date
  • Sort Method: external sort Disk: 3,176kB
  • Buffers: shared hit=189 read=5,773, temp read=1,892 written=1,898
26. 15.917 490.952 ↑ 1.0 70,183 1

Subquery Scan on a_1 (cost=53,171.65..75,526.48 rows=70,533 width=25) (actual time=275.971..490.952 rows=70,183 loops=1)

  • Output: a_1.sku, a_1.snapshot_date, a_1.quantity
  • Buffers: shared hit=189 read=5,773, temp read=1,099 written=1,104
27. 48.923 475.035 ↑ 1.0 70,183 1

Finalize GroupAggregate (cost=53,171.65..74,821.15 rows=70,533 width=33) (actual time=275.970..475.035 rows=70,183 loops=1)

  • Output: (daily_inventory.snapshot_date)::date, daily_inventory.sku, sum(daily_inventory.quantity), daily_inventory.snapshot_date
  • Group Key: daily_inventory.snapshot_date, daily_inventory.sku
  • Buffers: shared hit=189 read=5,773, temp read=1,099 written=1,104
28. 57.708 426.112 ↑ 1.1 127,838 1

Gather Merge (cost=53,171.65..72,881.49 rows=141,066 width=29) (actual time=275.960..426.112 rows=127,838 loops=1)

  • Output: daily_inventory.sku, daily_inventory.snapshot_date, (PARTIAL sum(daily_inventory.quantity))
  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=838 read=15,910, temp read=3,072 written=3,087
29. 59.358 368.404 ↑ 1.7 42,613 3 / 3

Partial GroupAggregate (cost=52,171.63..55,598.95 rows=70,533 width=29) (actual time=266.477..368.404 rows=42,613 loops=3)

  • Output: daily_inventory.sku, daily_inventory.snapshot_date, PARTIAL sum(daily_inventory.quantity)
  • Group Key: daily_inventory.snapshot_date, daily_inventory.sku
  • Buffers: shared hit=838 read=15,910, temp read=3,072 written=3,087
  • Worker 0: actual time=261.663..360.577 rows=43,394 loops=1
  • Buffers: shared hit=314 read=5,032, temp read=978 written=983
  • Worker 1: actual time=262.287..362.413 rows=43,768 loops=1
  • Buffers: shared hit=335 read=5,105, temp read=995 written=1,000
30. 210.436 309.046 ↑ 1.2 217,885 3 / 3

Sort (cost=52,171.63..52,852.13 rows=272,199 width=25) (actual time=266.462..309.046 rows=217,885 loops=3)

  • Output: daily_inventory.sku, daily_inventory.snapshot_date, daily_inventory.quantity
  • Sort Key: daily_inventory.snapshot_date, daily_inventory.sku
  • Sort Method: external merge Disk: 8,792kB
  • Worker 0: Sort Method: external merge Disk: 7,824kB
  • Worker 1: Sort Method: external merge Disk: 7,960kB
  • Buffers: shared hit=838 read=15,910, temp read=3,072 written=3,087
  • Worker 0: actual time=261.643..302.479 rows=208,173 loops=1
  • Buffers: shared hit=314 read=5,032, temp read=978 written=983
  • Worker 1: actual time=262.269..303.808 rows=211,666 loops=1
  • Buffers: shared hit=335 read=5,105, temp read=995 written=1,000
31. 98.610 98.610 ↑ 1.2 217,885 3 / 3

Parallel Seq Scan on public.daily_inventory (cost=0.00..21,086.31 rows=272,199 width=25) (actual time=0.073..98.610 rows=217,885 loops=3)

  • Output: daily_inventory.sku, daily_inventory.snapshot_date, daily_inventory.quantity
  • Filter: (((daily_inventory.fulfillment_center_id)::text <> '*XFR'::text) AND ((daily_inventory.detailed_disposition)::text = 'SELLABLE'::text))
  • Rows Removed by Filter: 17,221
  • Buffers: shared hit=768 read=15,910
  • Worker 0: actual time=0.085..95.756 rows=208,173 loops=1
  • Buffers: shared hit=279 read=5,032
  • Worker 1: actual time=0.098..96.754 rows=211,666 loops=1
  • Buffers: shared hit=300 read=5,105
Planning time : 1.658 ms
Execution time : 41,674.490 ms