-- fans of fans of lb, broken (slide 16)
WITH lb_fans AS
( SELECT bf_fanid fanid
   FROM bandfans
   JOIN bands ON bandid = bf_bandid
   WHERE bands.name = 'limp bizkit'
)
SELECT bands.name
FROM bandfans
JOIN lb_fans ON bf_fanid = fanid
JOIN bands ON bf_bandid = bandid


--fix (slide 17)
WITH lb_fans AS
( SELECT bf_fanid fanid
   FROM bandfans
   JOIN bands ON bandid = bf_bandid
   WHERE bands.name = 'limp bizkit'
)
SELECT DISTINCT bands.name
FROM bandfans
JOIN lb_fans ON bf_fanid = fanid
JOIN bands ON bf_bandid = bandid
WHERE bands.name != 'limp bizkit'

-- recursion

WITH RECURSIVE t(n) AS 
( VALUES (1) 
UNION
SELECT n+1 
FROM t WHERE n < 100 
) 
SELECT sum(n) FROM t; 


-- recursion
WITH recursive lb_fan_bands as (
    SELECT bf_fanid, bf_bandid
    FROM bandfans
    JOIN bands on bf_bandid = bandid
    WHERE bands.name = 'limp bizkit'
UNION
    SELECT bandfans.bf_fanid, bandfans.bf_bandid
    FROM bandfans JOIN lb_fan_bands
    ON (lb_fan_bands.bf_fanid = bandfans.bf_fanid
        OR lb_fan_bands.bf_bandid = bandfans.bf_bandid)
)
SELECT distinct name FROM lb_fan_bands
JOIN bands ON bf_bandid = bandid
WHERE name != 'limp bizkit'



drop index bf_index;
drop index band_name;

create index bf_pkey on bandfans(bf_fanid, bf_bandid);
cluster bandfans using bf_pkey;
vacuum analyze;
drop index bf_pkey;

\timing on

set enable_bitmapscan to off;
SET max_parallel_workers_per_gather = 0;



create index bf_index on bandfans(bf_bandid);

explain 
select count(*) from bandfans join bands on bf_bandid = bandid where name = 'limp bizkit';
select count(*) from bandfans join bands on bf_bandid = bandid where name = 'limp bizkit';


create index band_name on bands(name);

explain 
select count(*) from bandfans join bands on bf_bandid = bandid where name = 'limp bizkit';

select count(*) from bandfans join bands on bf_bandid = bandid where name = 'limp bizkit';




cluster bandfans using bf_index;
vacuum analyze;

explain 
select count(*) from bandfans join bands on bf_bandid = bandid where name = 'limp bizkit';

select count(*) from bandfans join bands on bf_bandid = bandid where name = 'limp bizkit';
