-- Coût de « Mes réservations » : la requête de l'application (toutes les réservations, avec créneau, profil, paiement)
-- contre la même requête filtrée sur la cliente connectée. Données fictives générées.
\timing off
create or replace function gen(n int) returns void language plpgsql as $$
begin
  truncate public.payments, public.bookings, public.slots cascade; delete from auth.users;
  insert into auth.users(id,email,raw_user_meta_data)
    select gen_random_uuid(), 'client'||i||'@example.com', jsonb_build_object('full_name','Client '||i,'phone','06'||lpad(i::text,8,'0')) from generate_series(1,500) i;
  insert into public.slots(title,discipline,instructor,starts_at,ends_at,capacity,price_cents)
    select 'Cours '||i,'yoga','Léa',now()+(i||' hours')::interval,now()+(i||' hours')::interval+interval '1 hour',12,1800 from generate_series(1,greatest(n/10,1)) i;
  insert into public.bookings(user_id,slot_id,status)
    select (select id from public.profiles order by random()+i*0 limit 1), (select id from public.slots order by random()+i*0 limit 1),'confirmed' from generate_series(1,n) i;
  insert into public.payments(booking_id,user_id,amount_cents,status) select id,user_id,1800,'succeeded' from public.bookings;
  analyze;
end $$;
create or replace function mesure(filtre boolean, out ms numeric, out octets bigint) language plpgsql as $$
declare t0 timestamptz; u uuid; j text;
begin
  select user_id into u from public.bookings limit 1;
  t0:=clock_timestamp();
  select coalesce(json_agg(x)::text,'[]') into j from (
    select b.*, to_jsonb(s) as slot, to_jsonb(p) as profile, (select jsonb_agg(pa) from public.payments pa where pa.booking_id=b.id) as payment
    from public.bookings b join public.slots s on s.id=b.slot_id join public.profiles p on p.id=b.user_id
    where (not filtre or b.user_id=u) order by b.created_at desc) x;
  ms:=round(extract(epoch from clock_timestamp()-t0)*1000,1); octets:=octet_length(j);
end $$;
\pset footer off
\echo n_reservations | requete_application_ms | octets_envoyes | requete_filtree_ms | octets_filtres
select gen(100); select 100 n, a.ms, pg_size_pretty(a.octets), b.ms, pg_size_pretty(b.octets) from mesure(false) a, mesure(true) b;
select gen(1000); select 1000 n, a.ms, pg_size_pretty(a.octets), b.ms, pg_size_pretty(b.octets) from mesure(false) a, mesure(true) b;
select gen(10000); select 10000 n, a.ms, pg_size_pretty(a.octets), b.ms, pg_size_pretty(b.octets) from mesure(false) a, mesure(true) b;
select gen(50000); select 50000 n, a.ms, pg_size_pretty(a.octets), b.ms, pg_size_pretty(b.octets) from mesure(false) a, mesure(true) b;