1

I have tables as follows:

Source - 5 million records

Target - 5 million + daily incremental records

Both the source and target table have composite binary index on id and timestamp. Basically a source query called (Raw) is built first and then on top of that there are some roll up operations like union and rank in the form of ff as temp tables and others.

While trying to do upsert my query is taking more then 2 hours.Any help on this would be really helpful.

Execution Plan:

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Update on "mo_reddoorz_booking_detail_PK"  (cost=16851197298.35..6935116172952.36 rows=481460192051720 width=2094)
   ->  Nested Loop  (cost=16851197298.35..6935116172952.36 rows=481460192051720 width=2094)
         ->  Subquery Scan on x  (cost=16851197298.35..916863772294.99 rows=6878002743596 width=2084)
               ->  Hash Join  (cost=16851197298.35..848083744859.03 rows=6878002743596 width=1032)
                     Hash Cond: (a.id = x_1.id)
                     CTE raw
                       ->  Merge Right Join  (cost=3731098.88..24403472.46 rows=751275429 width=247)
                             Merge Cond: ((upper((pc.promo_code)::text)) = (upper((rb.promo_code)::text)))
                             ->  Sort  (cost=3605.83..3684.18 rows=31340 width=19)
                                   Sort Key: (upper((pc.promo_code)::text))
                                   ->  Seq Scan on rl_promo_campaigns pc  (cost=0.00..1265.40 rows=31340 width=19)
                             ->  Materialize  (cost=3727493.05..3751464.83 rows=4794355 width=242)
                                   ->  Sort  (cost=3727493.05..3739478.94 rows=4794355 width=242)
                                         Sort Key: (upper((rb.promo_code)::text))
                                         ->  Hash Join  (cost=729773.76..1524015.72 rows=4794355 width=242)
                                               Hash Cond: (rb.country_id = c_1.id)
                                               ->  Hash Join  (cost=729772.67..1458092.25 rows=4794355 width=236)
                                                     Hash Cond: ((rb.property_code)::text = (p.code)::text)
                                                     ->  Hash Join  (cost=729663.23..1392060.43 rows=4794355 width=195)
                                                           Hash Cond: (bo.bookable_id = rb.id)
                                                           ->  Hash Right Join  (cost=185753.99..561603.03 rows=4794355 width=50)
                                                                 Hash Cond: (gd.id = bo.guest_detail_id)
                                                                 ->  Seq Scan on guest_details gd  (cost=0.00..172046.53 rows=4874753 width=50)
                                                                 ->  Hash  (cost=107096.55..107096.55 rows=4794355 width=8)
                                                                       ->  Seq Scan on bookings bo  (cost=0.00..107096.55 rows=4794355 width=8)
                                                           ->  Hash  (cost=380960.11..380960.11 rows=4794811 width=149)
                                                                 ->  Seq Scan on mo_reddoorz_bookings rb  (cost=0.00..380960.11 rows=4794811 width=149)
                                                     ->  Hash  (cost=75.86..75.86 rows=2686 width=49)
                                                           ->  Seq Scan on be_properties p  (cost=0.00..75.86 rows=2686 width=49)
                                               ->  Hash  (cost=1.04..1.04 rows=4 width=14)
                                                     ->  Seq Scan on mo_countries c_1  (cost=0.00..1.04 rows=4 width=14)
                     CTE noshow_cancelled
                       ->  WindowAgg  (cost=974821986.54..976324537.40 rows=75127543 width=440)
                             ->  Sort  (cost=974821986.54..975009805.40 rows=75127543 width=432)
                                   Sort Key: raw.email, raw.created_at
                                   ->  Group  (cost=857614664.75..906447567.64 rows=75127543 width=432)
                                         Group Key: raw.id, raw.booking_id, raw.property_code, raw.name, raw.property_type, raw.created_at, raw.updated_at, raw.booking_date, raw.check_in_date, raw.check_out_date, raw.rooms, raw.nights, raw.amount_per_room, raw.gross_amount, raw.total_receivable, raw.status, raw.payment_model, raw.email, raw.booking_source, raw.booking_channel, raw.payment_channel, raw.city, raw.country, raw.club_member_discount, raw.currency
                                         ->  Sort  (cost=857614664.75..859492853.33 rows=751275429 width=432)
                                               Sort Key: raw.id, raw.booking_id, raw.property_code, raw.name, raw.property_type, raw.created_at, raw.updated_at, raw.booking_date, raw.check_in_date, raw.check_out_date, raw.rooms, raw.nights, raw.amount_per_room, raw.gross_amount, raw.total_receivable, raw.status, raw.payment_model, raw.email, raw.booking_source, raw.booking_channel, raw.payment_channel, raw.city, raw.country, raw.club_member_discount, raw.currency
                                               ->  CTE Scan on raw  (cost=0.00..15025508.58 rows=751275429 width=432)
                     CTE ff
                       ->  Unique  (cost=98393023.76..103882765.43 rows=78424881 width=444)
                             ->  Sort  (cost=98393023.76..98589085.96 rows=78424881 width=444)
                                   Sort Key: raw_1.id, raw_1.booking_id, raw_1.property_code, raw_1.name, raw_1.property_type, raw_1.created_at, raw_1.updated_at, raw_1.booking_date, raw_1.check_in_date, raw_1.check_out_date, raw_1.rooms, raw_1.nights, raw_1.amount_per_room, raw_1.gross_amount, raw_1.total_receivable, raw_1.status, raw_1.payment_model, raw_1.email, raw_1.booking_source, raw_1.booking_channel, raw_1.payment_channel, raw_1.city, raw_1.country, raw_1.club_member_discount, raw_1.currency, (CASE WHEN (raw_1.email IS NULL) THEN '1'::bigint ELSE sum(1) OVER (?) END), (1)
                                   ->  Append  (cost=22300850.44..24848928.49 rows=78424881 width=444)
                                         ->  WindowAgg  (cost=22300850.44..22374309.96 rows=3672976 width=444)
                                               ->  Sort  (cost=22300850.44..22310032.88 rows=3672976 width=432)
                                                     Sort Key: raw_1.email, raw_1.created_at
                                                     ->  Group  (cost=19509418.62..19753583.13 rows=3672976 width=432)
                                                           Group Key: raw_1.id, raw_1.booking_id, raw_1.property_code, raw_1.name, raw_1.property_type, raw_1.created_at, raw_1.updated_at, raw_1.booking_date, raw_1.check_in_date, raw_1.check_out_date, raw_1.rooms, raw_1.nights, raw_1.amount_per_room, raw_1.gross_amount, raw_1.total_receivable, raw_1.status, raw_1.payment_model, raw_1.email, raw_1.booking_source, raw_1.booking_channel, raw_1.payment_channel, raw_1.city, raw_1.country, raw_1.club_member_discount, raw_1.currency
                                                           ->  Sort  (cost=19509418.62..19518809.56 rows=3756377 width=432)
                                                                 Sort Key: raw_1.id, raw_1.booking_id, raw_1.property_code, raw_1.name, raw_1.property_type, raw_1.created_at, raw_1.updated_at, raw_1.booking_date, raw_1.check_in_date, raw_1.check_out_date, raw_1.rooms, raw_1.nights, raw_1.amount_per_room, raw_1.gross_amount, raw_1.total_receivable, raw_1.payment_model, raw_1.email, raw_1.booking_source, raw_1.booking_channel, raw_1.payment_channel, raw_1.city, raw_1.country, raw_1.club_member_discount, raw_1.currency
                                                                 ->  CTE Scan on raw raw_1  (cost=0.00..16903697.15 rows=3756377 width=432)
:

My query strucure:

update  public."mo_test_booking_detail_PK"
set booking_id=x.booking_id, property_code=x.property_code, hotel_name=x.hotel_name ,
property_type =x.property_type,
created_at  = x.created_at,
booking_date = x.booking_date,
check_in_date = x.check_in_date,
check_out_date = x.check_out_date,
rooms = x.rooms,
nights = x.nights,
amount_per_room= x.amount_per_room,
gross_amount = x.gross_amountfrom

(with raw as (select rb.id, rb.booking_id, rb.property_code,p.name, 1 property_type, rb.created_at, rb.updated_at,

rb.booking_date, rb.check_in_date,  rb.check_out_date,

rooms, nights, amount_per_room , gross_amount, total_receivable, rb.status, rb.payment_model ,

case when gd.email is null then LOWER(checkin_desk_email) else LOWER(gd.email) end email,

case when pc.promo_category = 'Corporate' then 'Corporate' else booking_source end booking_source

, booking_channel, payment_channel, p.city , c.name country,

club_member_discount,rb.currency

--,case when gd.email is null then 1  else sum(1) over (PARTITION BY gd.email ORDER BY rb.booking_date asc rows between unbounded preceding and current row) end rank

--,case when gd.email is null then 1 when status = 'Confirmed' then ROW_NUMBER() OVER (PARTITION BY gd.email ORDER BY rb.booking_date DESC) end rank

--,rb.*

from public.mo_test_bookings rb

inner join public.be_properties p on p.code = rb.property_code

left join public.rl_promo_campaigns pc on upper(pc.promo_code) = upper(rb.promo_code)

inner join public.mo_countries c on c.id = rb.country_id

inner join bookings bo on bo.bookable_id=rb.id

left join guest_details gd on bo.guest_detail_id=gd.id
--group by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22

),

noshow_cancelled as (

select raw.* , case when email is null then 1  else sum(1) over (PARTITION BY email ORDER BY created_at asc rows between unbounded preceding and current row) end ranks

from raw

group by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25

)

,

ff as (select raw.* , case when email is null then 1  else sum(1) over (PARTITION BY email ORDER BY created_at asc rows between unbounded preceding and current row) end ranks, 1 confirmed_count

from raw where status = 'Confirmed'

group by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25

union

select noshow_cancelled.*, 0  from noshow_cancelled

where status != 'Confirmed')

,final as (select  ff.*,

case when email is null or email in ('NA','N@A','NA@gmail.com','NA') then 1  else sum(confirmed_count) over (PARTITION BY email ORDER BY created_at asc rows between unbounded preceding and current row) end rank2

from ff)

, rcash as (Select rb.booking_id,

SUM(case when (bri.testcash_used >1) then coalesce(bri.testcash_used_value,0) else 0 end) AS burnt

From  bookings_testcash_infos bri

inner Join mo_test_bookings rb ON bri.test_booking_id=rb.id

Where rb.status IN ('Confirmed') and rb.booking_source='Direct'

Group by 1)

,

final_raw as (select

id, final.booking_id, property_code,final.name hotel_name, property_type, final.created_at,

booking_date, check_in_date,  check_out_date,

rooms, nights, amount_per_room , gross_amount, total_receivable, status, email,

payment_model,

booking_source,

case

when booking_source = 'Direct' and  booking_channel = 1 then 'Mobile'

when booking_source = 'Direct' and  booking_channel = 2 then 'Desktop'

when booking_source = 'Direct' and  booking_channel = 3 then 'Android'

when booking_source = 'Direct' and  booking_channel = 4 then 'Ios'

when booking_source ilike 'walk in%' then 'Walkins'

when booking_source = 'Corporate' then 'Corporate'

else 'OTA' end booking_channel,

pc.name payment_channel, upper(city) city , upper(country) country ,

coalesce(club_member_discount,0) club_member_discount,

coalesce(r.burnt,0) testcash,

final.currency,

case when email is null or email in ('na') then 'New'

when status = 'Confirmed' and rank2 >1 then 'Repeat'

when status != 'Confirmed' and rank2 >0 then 'Repeat'

when rank2=0 then null

else 'New' end repeat_flag

,rate sgd_rate,

case when status = 'Confirmed' then rank2 else null end rank2,

updated_at

from final

left join rcash r on r.booking_id = final.booking_id

inner join public.cur_conversion con on con.currency = final.currency and rate_date = booking_date

left join public.payment_channels pc on pc.code = final.payment_channel)

select l.* from

(select

a.*

,case when a.booking_date - b.booking_date >=30 then 'New' else 'Repeat' end "repeat_1m" from final_raw a

inner join final_raw b on a.status = 'Confirmed' and a.rank2 = b.rank2+1 and a.email = b.email

union all

select c.* , '' from final_raw c

where c.status != 'Confirmed'

union all

select d.* , 'New' from final_raw d

where d.rank2 = 1 ) l

left join public."mo_test_booking_detail_PK" x on l.id=x.id  

where   x.id is NOT null and l.updated_at>=(select max(updated_at) from  public."mo_test_booking_detail_PK" )) x;
  • Please format the query so that it is readable and add the EXPLAIN (ANALYZE, BUFFERS) output to the question. – Laurenz Albe Oct 31 at 15:06
  • @LaurenzAlbe i added the execution plan and edited the query – user7422128 Oct 31 at 16:50
  • The query is still unreadable, and it looks syntactically incorrect to me; perhaps a missing space in x.gross_amountfrom, but it's impossible to tell. From the execution plan I would recommend that you scrap the whole thing and write something cleaner, simpler. Perhaps some procedural code might be an alternative. – Laurenz Albe Oct 31 at 17:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.