explain.depesz.com

PostgreSQL's explain analyze made readable

Result: o2p

Settings
# exclusive inclusive rows x rows loops node
1. 4.110 139.187 ↓ 775.5 4,653 1

Hash Left Join (cost=1,698.90..1,839.16 rows=6 width=17) (actual time=22.709..139.187 rows=4,653 loops=1)

  • Output: dds.day, d.amount, CASE WHEN (d.amount > '0'::numeric) THEN (LEAST(LEAST(LEAST((COALESCE(dsdl.max_stock, 32767))::bigint, ab.count), (COALESCE(mds.max_discounted_products, 32767))::bigint), (COALESCE(dds.stock, 32767))::bigint) - dldr.sold) ELSE (LEAST(LEAST((COALESCE(dsdl.max_stock, 32767))::bigint, ab.count), (COALESCE(dds.stock, 32767))::bigint) - dldr.sold) END
  • Inner Unique: true
  • Hash Cond: ((dds.day = dldr.day) AND (dd.id = dldr.discount_definition_id) AND (d.amount = dldr.discount_amount))
2. 1.297 131.831 ↓ 775.5 4,653 1

Merge Join (cost=1,303.85..1,443.86 rows=6 width=37) (actual time=19.445..131.831 rows=4,653 loops=1)

  • Output: dd.id, d.amount, dds.day, dds.stock, mds.max_discounted_products, dsdl.max_stock, ab.count
  • Inner Unique: true
  • Merge Cond: (cp.product_id = pp.id)
3. 2.513 130.446 ↓ 775.5 4,653 1

Nested Loop (cost=1,292.60..1,710.12 rows=6 width=53) (actual time=19.358..130.446 rows=4,653 loops=1)

  • Output: cp.product_id, dd.id, d.amount, dds.day, dds.stock, mds.max_discounted_products, dsdl.max_stock, ab.count, ppc.product_id
  • Inner Unique: true
4. 2.770 113.974 ↓ 310.2 4,653 1

Merge Join (cost=1,292.32..1,614.39 rows=15 width=69) (actual time=19.338..113.974 rows=4,653 loops=1)

  • Output: ccp.campaign_id, cc.content_partner_location_id, cp.product_id, dd.id, d.amount, dds.day, dds.stock, mds.max_discounted_products, mds.day, ab.count, ppc.product_id
  • Merge Cond: (cp.product_id = ppc.product_id)
  • Join Filter: ((ppc.date_from <= dds.day) AND ((ppc.date_to > dds.day) OR (ppc.date_to IS NULL)))
5. 45.942 110.232 ↓ 119.3 4,653 1

Nested Loop (cost=1,292.17..2,261.25 rows=39 width=61) (actual time=19.268..110.232 rows=4,653 loops=1)

  • Output: ccp.campaign_id, cc.content_partner_location_id, cp.product_id, dd.id, d.amount, dds.day, dds.stock, mds.max_discounted_products, mds.day, ab.count
  • Join Filter: (dbb.barcode_definition_id = ab.barcode_definition_id)
  • Rows Removed by Join Filter: 558360
6. 4.596 17.760 ↓ 81.6 4,653 1

Nested Loop (cost=4.33..880.40 rows=57 width=61) (actual time=0.183..17.760 rows=4,653 loops=1)

  • Output: ccp.campaign_id, cc.content_partner_location_id, cp.product_id, dd.id, d.amount, dds.day, dds.stock, mds.max_discounted_products, mds.day, dbb.barcode_definition_id
  • Inner Unique: true
  • Join Filter: (cp.discount_definition_id = mds.discount_definition_id)
7. 1.380 3.858 ↓ 16.6 4,653 1

Nested Loop (cost=4.05..70.05 rows=281 width=69) (actual time=0.175..3.858 rows=4,653 loops=1)

  • Output: ccp.campaign_id, cc.content_partner_location_id, cp.discount_definition_id, cp.product_id, dd.id, d.amount, d.discount_definition_id, dds.day, dds.stock, dbb.barcode_definition_id
8. 0.041 0.523 ↓ 3.8 23 1

Nested Loop (cost=3.63..37.97 rows=6 width=77) (actual time=0.159..0.523 rows=23 loops=1)

  • Output: ccp.campaign_id, cc.content_partner_location_id, cp.discount_definition_id, cp.product_id, dd.id, d.amount, d.discount_definition_id, d.id, dbb.discount_id, dbb.barcode_definition_id
9. 0.032 0.390 ↓ 3.3 23 1

Nested Loop (cost=3.36..35.36 rows=7 width=61) (actual time=0.154..0.390 rows=23 loops=1)

  • Output: ccp.campaign_id, cc.content_partner_location_id, cp.discount_definition_id, cp.product_id, dd.id, d.amount, d.discount_definition_id, d.id
  • Join Filter: (cp.discount_definition_id = d.discount_definition_id)
10. 0.021 0.292 ↓ 5.5 11 1

Nested Loop (cost=3.08..33.95 rows=2 width=40) (actual time=0.147..0.292 rows=11 loops=1)

  • Output: ccp.campaign_id, cc.content_partner_location_id, cp.discount_definition_id, cp.product_id, dd.id
  • Inner Unique: true
11. 0.020 0.227 ↓ 5.5 11 1

Nested Loop (cost=2.94..33.23 rows=2 width=32) (actual time=0.138..0.227 rows=11 loops=1)

  • Output: ccp.campaign_id, cc.content_partner_location_id, cp.discount_definition_id, cp.product_id
  • Join Filter: (cc.id = cp.contract_id)
  • Rows Removed by Join Filter: 231
12. 0.086 0.086 ↓ 1.0 121 1

Index Scan using uk_contract_product_product_id_and_discount_definition_id on public.contract_product cp (cost=0.14..25.94 rows=120 width=24) (actual time=0.010..0.086 rows=121 loops=1)

  • Output: cp.contract_id, cp.discount_definition_id, cp.product_id
13. 0.057 0.121 ↓ 2.0 2 121

Materialize (cost=2.79..5.49 rows=1 width=24) (actual time=0.001..0.001 rows=2 loops=121)

  • Output: ccp.campaign_id, cc.id, cc.content_partner_location_id
14. 0.025 0.064 ↓ 2.0 2 1

Hash Join (cost=2.79..5.49 rows=1 width=24) (actual time=0.060..0.064 rows=2 loops=1)

  • Output: ccp.campaign_id, cc.id, cc.content_partner_location_id
  • Inner Unique: true
  • Hash Cond: (cc.campaign_content_partner_id = ccp.id)
15. 0.019 0.019 ↑ 1.0 54 1

Seq Scan on public.contract cc (cost=0.00..2.54 rows=54 width=24) (actual time=0.012..0.019 rows=54 loops=1)

  • Output: cc.id, cc.created, cc.removed, cc.updated, cc.uuid, cc.has_booking_cost, cc.has_visit_guarantee, cc.name, cc.note, cc.sort_order, cc.status, cc.campaign_content_partner_id, cc.content_partner_location_id
16. 0.003 0.020 ↑ 1.0 1 1

Hash (cost=2.78..2.78 rows=1 width=16) (actual time=0.020..0.020 rows=1 loops=1)

  • Output: ccp.id, ccp.campaign_id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
17. 0.017 0.017 ↑ 1.0 1 1

Seq Scan on public.campaign_content_partner ccp (cost=0.00..2.78 rows=1 width=16) (actual time=0.012..0.017 rows=1 loops=1)

  • Output: ccp.id, ccp.campaign_id
  • Filter: ((ccp.campaign_id = 750) AND (ccp.content_partner_id = 33031700))
  • Rows Removed by Filter: 36
18. 0.044 0.044 ↑ 1.0 1 11

Index Only Scan using discount_definition_pkey on public.discount_definition dd (cost=0.14..0.36 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=11)

  • Output: dd.id
  • Index Cond: (dd.id = cp.discount_definition_id)
  • Heap Fetches: 11
19. 0.066 0.066 ↑ 2.0 2 11

Index Scan using idx_discount_discount_definition_id on public.discount d (cost=0.28..0.65 rows=4 width=21) (actual time=0.004..0.006 rows=2 loops=11)

  • Output: d.id, d.created, d.removed, d.updated, d.uuid, d.amount, d.discount_definition_id
  • Index Cond: (d.discount_definition_id = dd.id)
20. 0.092 0.092 ↑ 1.0 1 23

Index Scan using idx_discount_barcode_discount_id on public.discount_barcode dbb (cost=0.28..0.36 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=23)

  • Output: dbb.id, dbb.created, dbb.removed, dbb.updated, dbb.uuid, dbb.barcode_definition_id, dbb.discount_id
  • Index Cond: (dbb.discount_id = d.id)
21. 1.955 1.955 ↓ 2.4 202 23

Index Only Scan using uk_discount_day_stock_discount_id_and_day_and_stock on public.discount_day_stock dds (cost=0.42..4.52 rows=83 width=16) (actual time=0.007..0.085 rows=202 loops=23)

  • Output: dds.discount_id, dds.day, dds.stock
  • Index Cond: ((dds.discount_id = d.id) AND (dds.day >= CURRENT_DATE))
  • Heap Fetches: 1421
22. 9.306 9.306 ↑ 1.0 1 4,653

Index Scan using uk_max_discounted_daily_stock_discount_definition_id_and_day on public.discount_max_discounted_daily_stock mds (cost=0.29..2.87 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=4,653)

  • Output: mds.id, mds.created, mds.removed, mds.updated, mds.uuid, mds.day, mds.max_discounted_products, mds.discount_definition_id
  • Index Cond: ((mds.discount_definition_id = d.discount_definition_id) AND (mds.day = dds.day))
23. 27.428 46.530 ↓ 1.1 121 4,653

Materialize (cost=1,287.84..1,290.49 rows=106 width=16) (actual time=0.004..0.010 rows=121 loops=4,653)

  • Output: ab.count, ab.barcode_definition_id
24. 0.014 19.102 ↓ 1.1 121 1

Subquery Scan on ab (cost=1,287.84..1,289.96 rows=106 width=16) (actual time=19.070..19.102 rows=121 loops=1)

  • Output: ab.count, ab.barcode_definition_id
25. 0.093 19.088 ↓ 1.1 121 1

HashAggregate (cost=1,287.84..1,288.90 rows=106 width=16) (actual time=19.069..19.088 rows=121 loops=1)

  • Output: bgr.barcode_definition_id, (count(*))
  • Group Key: bgr.barcode_definition_id, (count(*))
26. 0.010 18.995 ↓ 1.1 121 1

Append (cost=1,279.45..1,287.31 rows=106 width=16) (actual time=18.925..18.995 rows=121 loops=1)

27. 4.826 18.938 ↑ 1.3 73 1

HashAggregate (cost=1,279.45..1,280.38 rows=93 width=16) (actual time=18.925..18.938 rows=73 loops=1)

  • Output: bgr.barcode_definition_id, count(*)
  • Group Key: bgr.barcode_definition_id
28. 5.380 14.112 ↓ 1.2 25,107 1

Hash Join (cost=9.20..1,176.19 rows=20,652 width=8) (actual time=0.135..14.112 rows=25,107 loops=1)

  • Output: bgr.barcode_definition_id
  • Inner Unique: true
  • Hash Cond: (bb.barcode_generation_request_id = bgr.id)
29. 8.651 8.651 ↑ 1.0 26,387 1

Seq Scan on public.barcode bb (cost=0.00..1,094.03 rows=27,095 width=8) (actual time=0.048..8.651 rows=26,387 loops=1)

  • Output: bb.id, bb.created, bb.removed, bb.updated, bb.uuid, bb.code, bb.content_partner_location_id, bb.last_status_change, bb.status, bb.barcode_generation_request_id
  • Filter: ((bb.status)::text = ANY ('{OPEN,REPLACED,RELEASED}'::text[]))
  • Rows Removed by Filter: 2924
30. 0.018 0.081 ↑ 1.0 125 1

Hash (cost=7.64..7.64 rows=125 width=16) (actual time=0.080..0.081 rows=125 loops=1)

  • Output: bgr.barcode_definition_id, bgr.id
  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
31. 0.063 0.063 ↑ 1.0 125 1

Seq Scan on public.barcode_generation_request bgr (cost=0.00..7.64 rows=125 width=16) (actual time=0.008..0.063 rows=125 loops=1)

  • Output: bgr.barcode_definition_id, bgr.id
  • Filter: bgr.state
  • Rows Removed by Filter: 51
32. 0.007 0.047 ↓ 3.7 48 1

Subquery Scan on *SELECT* 2 (cost=0.00..6.00 rows=13 width=16) (actual time=0.017..0.047 rows=48 loops=1)

  • Output: "*SELECT* 2".id, 32767
33. 0.040 0.040 ↓ 3.7 48 1

Seq Scan on public.barcode_definition bd (cost=0.00..5.84 rows=13 width=12) (actual time=0.015..0.040 rows=48 loops=1)

  • Output: bd.id, 32767
  • Filter: ((bd.type)::text = 'API'::text)
  • Rows Removed by Filter: 148
34. 0.972 0.972 ↓ 22.9 4,827 1

Index Scan using idx_product_price_product_id on public.product_price ppc (cost=0.14..22.66 rows=211 width=24) (actual time=0.013..0.972 rows=4,827 loops=1)

  • Output: ppc.id, ppc.created, ppc.removed, ppc.updated, ppc.uuid, ppc.date_from, ppc.date_to, ppc.price, ppc.product_id
35. 13.959 13.959 ↑ 1.0 1 4,653

Index Scan using uk_discount_daily_limit_content_partner_id_and_campaign_id_and_ on public.discount_stock_daily_limit dsdl (cost=0.29..6.38 rows=1 width=24) (actual time=0.003..0.003 rows=1 loops=4,653)

  • Output: dsdl.id, dsdl.created, dsdl.removed, dsdl.updated, dsdl.uuid, dsdl.day, dsdl.max_stock, dsdl.campaign_id, dsdl.content_partner_location_id
  • Index Cond: ((dsdl.content_partner_location_id = cc.content_partner_location_id) AND (dsdl.campaign_id = 750) AND (dsdl.day = mds.day))
36. 0.053 0.088 ↑ 1.1 139 1

Sort (cost=11.24..11.63 rows=156 width=8) (actual time=0.072..0.088 rows=139 loops=1)

  • Output: pp.id
  • Sort Key: pp.id
  • Sort Method: quicksort Memory: 32kB
37. 0.035 0.035 ↓ 1.1 166 1

Seq Scan on public.product pp (cost=0.00..5.56 rows=156 width=8) (actual time=0.010..0.035 rows=166 loops=1)

  • Output: pp.id
38. 0.127 3.246 ↑ 1.3 419 1

Hash (cost=385.59..385.59 rows=541 width=24) (actual time=3.246..3.246 rows=419 loops=1)

  • Output: dldr.sold, dldr.day, dldr.discount_definition_id, dldr.discount_amount
  • Buckets: 1024 Batches: 1 Memory Usage: 34kB
39. 0.050 3.119 ↑ 1.3 419 1

Subquery Scan on dldr (cost=374.77..385.59 rows=541 width=24) (actual time=2.964..3.119 rows=419 loops=1)

  • Output: dldr.sold, dldr.day, dldr.discount_definition_id, dldr.discount_amount
40. 0.367 3.069 ↑ 1.3 419 1

HashAggregate (cost=374.77..380.18 rows=541 width=24) (actual time=2.963..3.069 rows=419 loops=1)

  • Output: ro.day, rp.discount_definition_id, rp.discount_amount, sum(rp.quantity)
  • Group Key: ro.day, rp.discount_definition_id, rp.discount_amount
41. 0.592 2.702 ↓ 1.1 579 1

Hash Join (cost=91.12..369.36 rows=541 width=20) (actual time=0.546..2.702 rows=579 loops=1)

  • Output: ro.day, rp.discount_definition_id, rp.discount_amount, rp.quantity
  • Inner Unique: true
  • Hash Cond: (rp.order_reservation_id = ro.id)
42. 1.589 1.589 ↓ 1.0 6,361 1

Seq Scan on public.reservation_product rp (cost=0.00..261.73 rows=6,273 width=24) (actual time=0.007..1.589 rows=6,361 loops=1)

  • Output: rp.discount_definition_id, rp.discount_amount, rp.quantity, rp.order_reservation_id
43. 0.023 0.521 ↑ 1.2 129 1

Hash (cost=89.26..89.26 rows=149 width=12) (actual time=0.521..0.521 rows=129 loops=1)

  • Output: ro.day, ro.id
  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
44. 0.498 0.498 ↑ 1.2 129 1

Seq Scan on public.reservation_order ro (cost=0.00..89.26 rows=149 width=12) (actual time=0.028..0.498 rows=129 loops=1)

  • Output: ro.day, ro.id
  • Filter: (((ro.status)::text = ANY ('{ACTIVE,COMPLETED}'::text[])) AND (ro.day >= CURRENT_DATE))
  • Rows Removed by Filter: 1584