explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SpZd

Settings
# exclusive inclusive rows x rows loops node
1. 103.613 91,711.855 ↑ 1.0 1 1

Aggregate (cost=75,302.54..75,302.55 rows=1 width=64) (actual time=91,711.855..91,711.855 rows=1 loops=1)

  • Output: max(CASE dd.discount_type WHEN 'FIXED_AMOUNT'::text THEN d.amount WHEN 'FIXED_PERCENTAGE'::text THEN round(((ppc.price * d.amount) / '100'::numeric), 2) ELSE NULL::numeric END), max(CASE dd.discount_type WHEN 'FIXED_AMOUNT'::text THEN round(((d.amount * '100'::numeric) / ppc.price), 2) WHEN 'FIXED_PERCENTAGE'::text THEN d.amount ELSE NULL::numeric END)
2. 86.691 91,608.242 ↓ 15.5 139,320 1

Hash Join (cost=14,078.45..75,033.59 rows=8,965 width=25) (actual time=10,590.617..91,608.242 rows=139,320 loops=1)

  • Output: dd.discount_type, d.amount, ppc.price
  • Hash Cond: (cp.product_id = ppc.product_id)
  • Join Filter: ((ppc.date_from <= (dds.day + CURRENT_TIME)) AND ((ppc.date_to > (dds.day + CURRENT_TIME)) OR (ppc.date_to IS NULL)))
3. 161.676 91,521.266 ↓ 5.9 139,320 1

Hash Left Join (cost=14,041.05..73,977.42 rows=23,554 width=39) (actual time=10,590.313..91,521.266 rows=139,320 loops=1)

  • Output: cp.product_id, pp.id, dd.discount_type, d.amount, dds.day
  • Hash Cond: ((dds.day = dldr.day) AND (dd.id = dldr.discount_definition_id) AND (d.amount = dldr.discount_amount))
  • Filter: (CASE WHEN (dsdl.max_stock <= (dldr.sold_discounted + dldr.sold_zero)) THEN '0'::bigint ELSE CASE WHEN (d.amount > '0'::numeric) THEN CASE WHEN (mds.max_discounted_products <= dldr.sold_discounted) THEN '0'::bigint ELSE (LEAST(LEAST(LEAST((COALESCE(dsdl.max_stock, 32767))::bigint, ab.count), (COALESCE(mds.max_discounted_products, 32767))::bigint), (COALESCE(dds.stock, 32767))::bigint) - COALESCE(dldr.sold_by_discount_level, '0'::bigint)) END ELSE (LEAST(LEAST((COALESCE(dsdl.max_stock, 32767))::bigint, ab.count), (COALESCE(dds.stock, 32767))::bigint) - COALESCE(dldr.sold_zero, '0'::bigint)) END END > 0)
  • Rows Removed by Filter: 251550
4. 2,928.519 91,357.956 ↓ 5.5 390,870 1

Hash Join (cost=13,469.63..72,611.01 rows=70,661 width=67) (actual time=10,588.625..91,357.956 rows=390,870 loops=1)

  • Output: cp.product_id, pp.id, dd.discount_type, dd.id, d.amount, dds.day, dds.stock, mds.max_discounted_products, dsdl.max_stock, ab.count
  • Inner Unique: true
  • Hash Cond: (dbb.barcode_definition_id = ab.barcode_definition_id)
5. 6,066.868 88,429.285 ↓ 414.2 32,833,080 1

Hash Left Join (cost=13,450.12..72,382.26 rows=79,276 width=67) (actual time=200.811..88,429.285 rows=32,833,080 loops=1)

  • Output: cp.product_id, pp.id, dd.discount_type, dd.id, d.amount, dds.day, dds.stock, mds.max_discounted_products, dbb.barcode_definition_id, dsdl.max_stock
  • Hash Cond: (pt.id = ptw.ticket_id)
  • Filter: (((pp.type)::text <> 'TICKET'::text) OR (date_part('dow'::text, (dds.day)::timestamp without time zone) = (CASE ptw.week_day WHEN 'MONDAY'::text THEN 1 WHEN 'TUESDAY'::text THEN 2 WHEN 'WEDNESDAY'::text THEN 3 WHEN 'THURSDAY'::text THEN 4 WHEN 'FRIDAY'::text THEN 5 WHEN 'SATURDAY'::text THEN 6 WHEN 'SUNDAY'::text THEN 0 ELSE NULL::integer END)::double precision))
6. 7,628.393 82,361.702 ↓ 1,129.6 32,833,080 1

Hash Join (cost=13,391.29..64,602.52 rows=29,066 width=85) (actual time=200.080..82,361.702 rows=32,833,080 loops=1)

  • Output: cp.product_id, pp.id, pp.type, pt.id, dd.discount_type, dd.id, d.amount, dds.day, dds.stock, mds.max_discounted_products, dbb.barcode_definition_id, dsdl.max_stock
  • Inner Unique: true
  • Hash Cond: (cp.discount_definition_id = dd.id)
7. 23,619.597 74,733.002 ↓ 946.7 32,833,080 1

Hash Join (cost=13,349.20..64,468.79 rows=34,681 width=87) (actual time=199.766..74,733.002 rows=32,833,080 loops=1)

  • Output: cp.product_id, cp.discount_definition_id, pp.id, pp.type, pt.id, d.amount, d.discount_definition_id, dds.day, dds.stock, mds.discount_definition_id, mds.max_discounted_products, dbb.barcode_definition_id, dsdl.max_stock
  • Inner Unique: true
  • Hash Cond: ((cp.discount_definition_id = mds.discount_definition_id) AND (dds.day = mds.day))
8. 27,888.018 51,031.066 ↓ 326.6 32,833,080 1

Hash Join (cost=5,417.20..52,585.08 rows=100,516 width=79) (actual time=116.796..51,031.066 rows=32,833,080 loops=1)

  • Output: cp.product_id, cp.discount_definition_id, pp.id, pp.type, pt.id, d.amount, d.discount_definition_id, dds.day, dds.stock, dbb.barcode_definition_id, dsdl.day, dsdl.max_stock
  • Inner Unique: true
  • Hash Cond: ((dds.day = dsdl.day) AND (cc.content_partner_location_id = dsdl.content_partner_location_id))
9. 4,973.817 23,099.253 ↓ 105.8 32,833,080 1

Nested Loop (cost=573.15..37,162.14 rows=310,262 width=87) (actual time=72.924..23,099.253 rows=32,833,080 loops=1)

  • Output: ccp.campaign_id, cc.content_partner_location_id, cp.product_id, cp.discount_definition_id, pp.id, pp.type, pt.id, d.amount, d.discount_definition_id, dds.day, dds.stock, dbb.barcode_definition_id
10. 64.818 137.676 ↓ 92.9 108,360 1

Hash Join (cost=572.72..692.94 rows=1,167 width=95) (actual time=72.894..137.676 rows=108,360 loops=1)

  • Output: ccp.campaign_id, cc.content_partner_location_id, cp.product_id, cp.discount_definition_id, pp.id, pp.type, pt.id, d.amount, d.discount_definition_id, d.id, dbb.discount_id, dbb.barcode_definition_id
  • Hash Cond: (dbb.discount_id = d.id)
11. 0.789 0.789 ↑ 1.0 3,822 1

Seq Scan on qup_core.discount_barcode dbb (cost=0.00..94.22 rows=3,822 width=16) (actual time=0.006..0.789 rows=3,822 loops=1)

  • Output: dbb.id, dbb.created, dbb.removed, dbb.updated, dbb.uuid, dbb.barcode_definition_id, dbb.discount_id
12. 41.434 72.069 ↓ 85.8 108,360 1

Hash (cost=556.93..556.93 rows=1,263 width=79) (actual time=72.069..72.069 rows=108,360 loops=1)

  • Output: ccp.campaign_id, cc.content_partner_location_id, cp.product_id, cp.discount_definition_id, pp.id, pp.type, pt.id, d.amount, d.discount_definition_id, d.id
  • Buckets: 32768 (originally 2048) Batches: 4 (originally 1) Memory Usage: 3841kB
13. 23.037 30.635 ↓ 85.8 108,360 1

Hash Join (cost=412.43..556.93 rows=1,263 width=79) (actual time=7.341..30.635 rows=108,360 loops=1)

  • Output: ccp.campaign_id, cc.content_partner_location_id, cp.product_id, cp.discount_definition_id, pp.id, pp.type, pt.id, d.amount, d.discount_definition_id, d.id
  • Hash Cond: (d.discount_definition_id = cp.discount_definition_id)
14. 0.620 0.620 ↓ 1.0 4,247 1

Seq Scan on qup_core.discount d (cost=0.00..116.36 rows=4,136 width=21) (actual time=0.007..0.620 rows=4,247 loops=1)

  • Output: d.id, d.created, d.removed, d.updated, d.uuid, d.amount, d.discount_definition_id
15. 2.108 6.978 ↓ 38.0 7,224 1

Hash (cost=410.06..410.06 rows=190 width=58) (actual time=6.978..6.978 rows=7,224 loops=1)

  • Output: ccp.campaign_id, cc.content_partner_location_id, cp.product_id, cp.discount_definition_id, pp.id, pp.type, pt.id
  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 678kB
16. 1.271 4.870 ↓ 38.0 7,224 1

Nested Loop (cost=135.94..410.06 rows=190 width=58) (actual time=2.507..4.870 rows=7,224 loops=1)

  • Output: ccp.campaign_id, cc.content_partner_location_id, cp.product_id, cp.discount_definition_id, pp.id, pp.type, pt.id
17. 0.331 0.331 ↓ 1.1 86 1

Seq Scan on qup_core.content_partner_location cpl (cost=0.00..138.44 rows=76 width=8) (actual time=0.047..0.331 rows=86 loops=1)

  • Output: cpl.id, cpl.created, cpl.removed, cpl.updated, cpl.uuid, cpl.address, cpl.description, cpl.logo_uuid, cpl.name, cpl.opening_hours, cpl.phone, cpl.pricing_information, cpl.same_as_content_partner_category_country, cpl.same_as_content_partner_description, cpl.status, cpl.website, cpl.category_id, cpl.content_partner_id, cpl.country_id, cpl.banner_uuid, cpl.latitude, cpl.longitude, cpl.debtor_number, cpl.scanning_method, cpl.city, cpl.vat, cpl.alias
  • Filter: ((cpl.content_partner_id = 2013711900) AND ((cpl.status)::text = 'ACTIVE'::text))
  • Rows Removed by Filter: 277
18. 0.397 3.268 ↓ 28.0 84 86

Materialize (cost=135.94..268.77 rows=3 width=66) (actual time=0.029..0.038 rows=84 loops=86)

  • Output: ccp.campaign_id, cpr.id, cc.content_partner_location_id, cp.product_id, cp.discount_definition_id, pp.id, pp.type, pt.id
19. 0.020 2.871 ↓ 28.0 84 1

Nested Loop Left Join (cost=135.94..268.76 rows=3 width=66) (actual time=2.452..2.871 rows=84 loops=1)

  • Output: ccp.campaign_id, cpr.id, cc.content_partner_location_id, cp.product_id, cp.discount_definition_id, pp.id, pp.type, pt.id
  • Inner Unique: true
20. 0.023 2.767 ↓ 28.0 84 1

Nested Loop (cost=135.79..268.15 rows=3 width=58) (actual time=2.437..2.767 rows=84 loops=1)

  • Output: ccp.campaign_id, cpr.id, cc.content_partner_location_id, cp.product_id, cp.discount_definition_id, pp.id, pp.type
  • Inner Unique: true
21. 0.014 2.576 ↓ 28.0 84 1

Nested Loop (cost=135.52..266.71 rows=3 width=40) (actual time=2.421..2.576 rows=84 loops=1)

  • Output: ccp.campaign_id, cpr.id, cc.content_partner_location_id, cp.product_id, cp.discount_definition_id
22. 0.014 0.014 ↑ 1.0 1 1

Index Scan using content_partner_pkey on qup_core.content_partner cpr (cost=0.14..8.16 rows=1 width=8) (actual time=0.013..0.014 rows=1 loops=1)

  • Output: cpr.id, cpr.created, cpr.removed, cpr.updated, cpr.uuid, cpr.description, cpr.logo_uuid, cpr.name, cpr.status, cpr.category_id, cpr.country_id, cpr.debtor_number, cpr.vat, cpr.alias
  • Index Cond: (cpr.id = 2013711900)
  • Filter: ((cpr.status)::text = 'ACTIVE'::text)
23. 0.078 2.548 ↓ 28.0 84 1

Hash Join (cost=135.37..258.52 rows=3 width=40) (actual time=2.405..2.548 rows=84 loops=1)

  • Output: ccp.content_partner_id, ccp.campaign_id, cc.content_partner_location_id, cp.product_id, cp.discount_definition_id
  • Hash Cond: (acp.contract_product_id = cp.id)
24. 0.050 2.198 ↑ 1.0 430 1

Subquery Scan on acp (cost=116.47..237.94 rows=440 width=8) (actual time=2.119..2.198 rows=430 loops=1)

  • Output: acp.contract_product_id
25. 0.140 2.148 ↑ 1.0 430 1

HashSetOp Except (cost=116.47..233.54 rows=440 width=12) (actual time=2.118..2.148 rows=430 loops=1)

  • Output: "*SELECT* 1".contract_product_id, (0)
26. 0.033 2.008 ↑ 1.0 444 1

Append (cost=116.47..232.39 rows=460 width=12) (actual time=1.409..2.008 rows=444 loops=1)

27. 0.046 1.517 ↑ 1.0 437 1

Subquery Scan on *SELECT* 1 (cost=116.47..125.28 rows=440 width=12) (actual time=1.409..1.517 rows=437 loops=1)

  • Output: "*SELECT* 1".contract_product_id, 0
28. 1.047 1.471 ↑ 1.0 437 1

HashAggregate (cost=116.47..120.88 rows=440 width=8) (actual time=1.408..1.471 rows=437 loops=1)

  • Output: cms.contract_product_id
  • Group Key: cms.contract_product_id
29. 0.424 0.424 ↑ 1.0 4,011 1

Seq Scan on qup_core.contract_commission cms (cost=0.00..106.38 rows=4,038 width=8) (actual time=0.005..0.424 rows=4,011 loops=1)

  • Output: cms.id, cms.created, cms.removed, cms.updated, cms.uuid, cms.commission, cms.effective_price, cms.contract_product_id
30. 0.003 0.458 ↑ 2.9 7 1

Subquery Scan on *SELECT* 2 (cost=106.81..107.11 rows=20 width=12) (actual time=0.452..0.458 rows=7 loops=1)

  • Output: "*SELECT* 2".contract_product_id, 1
31. 0.006 0.455 ↑ 2.9 7 1

Group (cost=106.81..106.91 rows=20 width=8) (actual time=0.451..0.455 rows=7 loops=1)

  • Output: cms_1.contract_product_id
  • Group Key: cms_1.contract_product_id
32. 0.022 0.449 ↓ 1.5 30 1

Sort (cost=106.81..106.86 rows=20 width=8) (actual time=0.448..0.449 rows=30 loops=1)

  • Output: cms_1.contract_product_id
  • Sort Key: cms_1.contract_product_id
  • Sort Method: quicksort Memory: 26kB
33. 0.427 0.427 ↓ 1.5 30 1

Seq Scan on qup_core.contract_commission cms_1 (cost=0.00..106.38 rows=20 width=8) (actual time=0.286..0.427 rows=30 loops=1)

  • Output: cms_1.contract_product_id
  • Filter: (cms_1.commission IS NULL)
  • Rows Removed by Filter: 3981
34. 0.020 0.272 ↓ 28.3 85 1

Hash (cost=18.86..18.86 rows=3 width=48) (actual time=0.272..0.272 rows=85 loops=1)

  • Output: ccp.content_partner_id, ccp.campaign_id, cc.content_partner_location_id, cp.id, cp.product_id, cp.discount_definition_id
  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
35. 0.063 0.252 ↓ 28.3 85 1

Nested Loop (cost=4.56..18.86 rows=3 width=48) (actual time=0.052..0.252 rows=85 loops=1)

  • Output: ccp.content_partner_id, ccp.campaign_id, cc.content_partner_location_id, cp.id, cp.product_id, cp.discount_definition_id
36. 0.015 0.104 ↓ 42.5 85 1

Nested Loop (cost=4.29..17.58 rows=2 width=32) (actual time=0.036..0.104 rows=85 loops=1)

  • Output: ccp.content_partner_id, ccp.campaign_id, cc.id, cc.content_partner_location_id
37. 0.036 0.036 ↑ 1.0 1 1

Seq Scan on qup_core.campaign_content_partner ccp (cost=0.00..7.19 rows=1 width=24) (actual time=0.009..0.036 rows=1 loops=1)

  • Output: ccp.id, ccp.created, ccp.removed, ccp.updated, ccp.uuid, ccp.banner_uuid, ccp.discount_sticker_type, ccp.header_content, ccp.main_image_uuid, ccp.sort_order, ccp.campaign_id, ccp.content_partner_id
  • Filter: ((ccp.content_partner_id = 2013711900) AND (ccp.campaign_id = 17752))
  • Rows Removed by Filter: 145
38. 0.037 0.053 ↓ 42.5 85 1

Bitmap Heap Scan on qup_core.contract cc (cost=4.29..10.37 rows=2 width=24) (actual time=0.023..0.053 rows=85 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
  • Recheck Cond: (cc.campaign_content_partner_id = ccp.id)
  • Filter: ((cc.status)::text = 'ACTIVE'::text)
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=9
39. 0.016 0.016 ↓ 43.0 86 1

Bitmap Index Scan on uk_contract_campaign_content_partner_id_and_content_partner_loc (cost=0.00..4.29 rows=2 width=0) (actual time=0.016..0.016 rows=86 loops=1)

  • Index Cond: (cc.campaign_content_partner_id = ccp.id)
40. 0.085 0.085 ↑ 1.0 1 85

Index Scan using idx_contract_product_contract_id on qup_core.contract_product cp (cost=0.27..0.63 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=85)

  • Output: cp.contract_id, cp.id, cp.product_id, cp.discount_definition_id
  • Index Cond: (cp.contract_id = cc.id)
  • Filter: ((cp.removed IS NULL) AND cp.include_in_sticker_calculation)
41. 0.168 0.168 ↑ 1.0 1 84

Index Scan using product_pkey on qup_core.product pp (cost=0.27..0.48 rows=1 width=18) (actual time=0.002..0.002 rows=1 loops=84)

  • Output: pp.id, pp.type
  • Index Cond: (pp.id = cp.product_id)
  • Filter: ((pp.status)::text = 'ACTIVE'::text)
42. 0.084 0.084 ↓ 0.0 0 84

Index Only Scan using product_ticket_pkey on qup_core.product_ticket pt (cost=0.15..0.20 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=84)

  • Output: pt.id
  • Index Cond: (pt.id = pp.id)
  • Heap Fetches: 0
43. 17,987.760 17,987.760 ↓ 1.0 303 108,360

Index Scan using idx_discount_day_stock_discount_id on qup_core.discount_day_stock dds (cost=0.43..28.23 rows=302 width=16) (actual time=0.010..0.166 rows=303 loops=108,360)

  • Output: dds.id, dds.created, dds.removed, dds.updated, dds.uuid, dds.day, dds.stock, dds.discount_id
  • Index Cond: (dds.discount_id = d.id)
  • Filter: ((dds.day >= '2019-03-09'::date) AND (dds.day <= '2020-03-09'::date))
  • Rows Removed by Filter: 9
44. 19.539 43.795 ↑ 1.0 77,943 1

Hash (cost=3,186.96..3,186.96 rows=79,406 width=24) (actual time=43.795..43.795 rows=77,943 loops=1)

  • Output: dsdl.campaign_id, dsdl.content_partner_location_id, dsdl.day, dsdl.max_stock
  • Buckets: 65536 Batches: 2 Memory Usage: 2486kB
45. 24.256 24.256 ↑ 1.0 77,943 1

Seq Scan on qup_core.discount_stock_daily_limit dsdl (cost=0.00..3,186.96 rows=79,406 width=24) (actual time=0.741..24.256 rows=77,943 loops=1)

  • Output: dsdl.campaign_id, dsdl.content_partner_location_id, dsdl.day, dsdl.max_stock
  • Filter: (dsdl.campaign_id = 17752)
  • Rows Removed by Filter: 31169
46. 42.397 82.339 ↑ 1.0 178,120 1

Hash (cost=4,390.20..4,390.20 rows=178,120 width=16) (actual time=82.339..82.339 rows=178,120 loops=1)

  • Output: mds.discount_definition_id, mds.day, mds.max_discounted_products
  • Buckets: 131072 Batches: 4 Memory Usage: 2954kB
47. 39.942 39.942 ↑ 1.0 178,120 1

Seq Scan on qup_core.discount_max_discounted_daily_stock mds (cost=0.00..4,390.20 rows=178,120 width=16) (actual time=0.101..39.942 rows=178,120 loops=1)

  • Output: mds.discount_definition_id, mds.day, mds.max_discounted_products
48. 0.138 0.307 ↓ 1.0 654 1

Hash (cost=34.26..34.26 rows=626 width=22) (actual time=0.307..0.307 rows=654 loops=1)

  • Output: dd.discount_type, dd.id
  • Buckets: 1024 Batches: 1 Memory Usage: 46kB
49. 0.169 0.169 ↓ 1.0 654 1

Seq Scan on qup_core.discount_definition dd (cost=0.00..34.26 rows=626 width=22) (actual time=0.006..0.169 rows=654 loops=1)

  • Output: dd.discount_type, dd.id
50. 0.357 0.715 ↑ 1.0 1,815 1

Hash (cost=36.15..36.15 rows=1,815 width=16) (actual time=0.715..0.715 rows=1,815 loops=1)

  • Output: ptw.ticket_id, ptw.week_day
  • Buckets: 2048 Batches: 1 Memory Usage: 102kB
51. 0.358 0.358 ↑ 1.0 1,815 1

Seq Scan on qup_core.product_ticket_available_weekday ptw (cost=0.00..36.15 rows=1,815 width=16) (actual time=0.006..0.358 rows=1,815 loops=1)

  • Output: ptw.ticket_id, ptw.week_day
52. 0.095 0.152 ↑ 1.0 689 1

Hash (cost=10.89..10.89 rows=689 width=16) (actual time=0.152..0.152 rows=689 loops=1)

  • Output: ab.barcode_definition_id, ab.count
  • Buckets: 1024 Batches: 1 Memory Usage: 41kB
53. 0.057 0.057 ↑ 1.0 689 1

Seq Scan on qup_core.available_barcodes_v ab (cost=0.00..10.89 rows=689 width=16) (actual time=0.008..0.057 rows=689 loops=1)

  • Output: ab.barcode_definition_id, ab.count
54. 0.079 1.634 ↓ 6.5 291 1

Hash (cost=570.64..570.64 rows=45 width=40) (actual time=1.634..1.634 rows=291 loops=1)

  • Output: dldr.day, dldr.discount_definition_id, dldr.discount_amount, dldr.sold_discounted, dldr.sold_zero, dldr.sold_by_discount_level
  • Buckets: 1024 Batches: 1 Memory Usage: 31kB
55. 0.039 1.555 ↓ 6.5 291 1

Subquery Scan on dldr (cost=569.40..570.64 rows=45 width=40) (actual time=1.434..1.555 rows=291 loops=1)

  • Output: dldr.day, dldr.discount_definition_id, dldr.discount_amount, dldr.sold_discounted, dldr.sold_zero, dldr.sold_by_discount_level
56. 0.070 1.516 ↓ 6.5 291 1

Unique (cost=569.40..570.19 rows=45 width=40) (actual time=1.433..1.516 rows=291 loops=1)

  • Output: ro.day, rp.discount_definition_id, rp.discount_amount, (sum(rp.quantity) FILTER (WHERE (rp.discount_amount = '0'::numeric)) OVER (?)), (sum(rp.quantity) FILTER (WHERE (rp.discount_amount > '0'::numeric)) OVER (?)), (sum(rp.quantity) OVER (?))
57. 0.100 1.446 ↓ 6.5 293 1

Sort (cost=569.40..569.51 rows=45 width=40) (actual time=1.432..1.446 rows=293 loops=1)

  • Output: ro.day, rp.discount_definition_id, rp.discount_amount, (sum(rp.quantity) FILTER (WHERE (rp.discount_amount = '0'::numeric)) OVER (?)), (sum(rp.quantity) FILTER (WHERE (rp.discount_amount > '0'::numeric)) OVER (?)), (sum(rp.quantity) OVER (?))
  • Sort Key: ro.day, rp.discount_definition_id, rp.discount_amount, (sum(rp.quantity) FILTER (WHERE (rp.discount_amount = '0'::numeric)) OVER (?)), (sum(rp.quantity) FILTER (WHERE (rp.discount_amount > '0'::numeric)) OVER (?)), (sum(rp.quantity) OVER (?))
  • Sort Method: quicksort Memory: 47kB
58. 0.233 1.346 ↓ 6.5 293 1

WindowAgg (cost=567.15..568.16 rows=45 width=40) (actual time=1.107..1.346 rows=293 loops=1)

  • Output: ro.day, rp.discount_definition_id, rp.discount_amount, (sum(rp.quantity) FILTER (WHERE (rp.discount_amount = '0'::numeric)) OVER (?)), (sum(rp.quantity) FILTER (WHERE (rp.discount_amount > '0'::numeric)) OVER (?)), sum(rp.quantity) OVER (?)
59. 0.171 1.113 ↓ 6.5 293 1

Sort (cost=567.15..567.26 rows=45 width=36) (actual time=1.098..1.113 rows=293 loops=1)

  • Output: ro.day, rp.discount_definition_id, rp.discount_amount, rp.quantity, (sum(rp.quantity) FILTER (WHERE (rp.discount_amount = '0'::numeric)) OVER (?)), (sum(rp.quantity) FILTER (WHERE (rp.discount_amount > '0'::numeric)) OVER (?))
  • Sort Key: ro.day, rp.discount_definition_id, rp.discount_amount
  • Sort Method: quicksort Memory: 47kB
60. 0.209 0.942 ↓ 6.5 293 1

WindowAgg (cost=564.68..565.91 rows=45 width=36) (actual time=0.738..0.942 rows=293 loops=1)

  • Output: ro.day, rp.discount_definition_id, rp.discount_amount, rp.quantity, sum(rp.quantity) FILTER (WHERE (rp.discount_amount = '0'::numeric)) OVER (?), sum(rp.quantity) FILTER (WHERE (rp.discount_amount > '0'::numeric)) OVER (?)
61. 0.096 0.733 ↓ 6.5 293 1

Sort (cost=564.68..564.79 rows=45 width=20) (actual time=0.716..0.733 rows=293 loops=1)

  • Output: ro.day, rp.discount_definition_id, rp.discount_amount, rp.quantity
  • Sort Key: ro.day, rp.discount_definition_id
  • Sort Method: quicksort Memory: 47kB
62. 0.033 0.637 ↓ 6.5 293 1

Nested Loop (cost=21.54..563.44 rows=45 width=20) (actual time=0.094..0.637 rows=293 loops=1)

  • Output: ro.day, rp.discount_definition_id, rp.discount_amount, rp.quantity
63. 0.346 0.394 ↓ 1.6 35 1

Bitmap Heap Scan on qup_core.reservation_order ro (cost=17.42..303.39 rows=22 width=12) (actual time=0.081..0.394 rows=35 loops=1)

  • Output: ro.day, ro.id
  • Recheck Cond: (ro.day >= CURRENT_DATE)
  • Filter: ((ro.status)::text = ANY ('{ACTIVE,COMPLETED}'::text[]))
  • Rows Removed by Filter: 672
  • Heap Blocks: exact=159
64. 0.048 0.048 ↓ 1.0 711 1

Bitmap Index Scan on idx_reservation_order_day (cost=0.00..17.42 rows=684 width=0) (actual time=0.048..0.048 rows=711 loops=1)

  • Index Cond: (ro.day >= CURRENT_DATE)
65. 0.140 0.210 ↓ 4.0 8 35

Bitmap Heap Scan on qup_core.reservation_product rp (cost=4.12..11.80 rows=2 width=24) (actual time=0.004..0.006 rows=8 loops=35)

  • Output: rp.discount_definition_id, rp.discount_amount, rp.quantity, rp.order_reservation_id
  • Recheck Cond: (rp.order_reservation_id = ro.id)
  • Heap Blocks: exact=43
66. 0.070 0.070 ↓ 4.0 8 35

Bitmap Index Scan on idx_reservation_product_order_reservation_id (cost=0.00..4.12 rows=2 width=0) (actual time=0.002..0.002 rows=8 loops=35)

  • Index Cond: (rp.order_reservation_id = ro.id)
67. 0.132 0.285 ↑ 1.0 616 1

Hash (cost=29.40..29.40 rows=640 width=30) (actual time=0.285..0.285 rows=616 loops=1)

  • Output: ppc.price, ppc.product_id, ppc.date_from, ppc.date_to
  • Buckets: 1024 Batches: 1 Memory Usage: 43kB
68. 0.153 0.153 ↑ 1.0 616 1

Seq Scan on qup_core.product_price ppc (cost=0.00..29.40 rows=640 width=30) (actual time=0.006..0.153 rows=616 loops=1)

  • Output: ppc.price, ppc.product_id, ppc.date_from, ppc.date_to
Planning time : 59.125 ms