Skip to content

a sql with recursive CTE and window function is 10x slower in threads=8 in version 1.1 #2

@l1t1

Description

@l1t1

and fast in threads=1 in version 1.1.0,
while fast in both threads=8 and threads=1 in version 0.7.1
I looked into the explain and find window function costs the most time

with recursive house as
  (select unnest(range(1,length(L)+1))id,unnest(L)cash from (select
  [118,871,258,694,451,792,117,515,471,445,576,126,403,644,663,504,921,947,704,710,948,27,505,721,980,837,408,939,939,810,662,616,685,758,312,779,911,635,429,62,682,572,81,111,974,700,730,868,548,304,600,678,479,951,348,614,147,971,337,331,9,904,893,542,803,439,291,673,149,289,773,644,554,619,687,621,738,897,501,265,234,511,549,250,722,271,404,365,715,668,205,712,106,946,128,472,960,122,332,957]
  L)
  ),
  t(lv,lastid,tot,path) as(select 1 lv,id,cash,1::int128<<id from house where id<=2
  union all
  select * exclude(rn)from (
  select lv+1,id,tot+cash,path+(1::int128<<id), row_number()over(partition by id order by tot+cash desc)rn from house,t
  where lv<50 and id>=lastid+2 and path&(1::int128<<id)=0 )where rn=1)
  select * from t where not (path&2=2 and lastid=100) order by tot desc limit 1;

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions