Produits Entr'ouvert » Chrono

Vue sur la prod.

chrono=# explain analyze 
SELECT
  DISTINCT ON (extra_data_keys) jsonb_object_keys("agendas_booking"."extra_data") AS "extra_data_keys" 
FROM "agendas_booking" 
  INNER JOIN "agendas_event" ON ("agendas_booking"."event_id" = "agendas_event"."id") 
  INNER JOIN "agendas_agenda" ON ("agendas_event"."agenda_id" = "agendas_agenda"."id") 
  LEFT OUTER JOIN "agendas_virtualmember" ON ("agendas_agenda"."id" = "agendas_virtualmember"."real_agenda_id") 
WHERE (
  "agendas_event"."agenda_id" IN (SELECT U0."id" FROM "agendas_agenda" U0 WHERE U0."slug" = 'armentieres-david-baudin') 
OR "agendas_virtualmember"."virtual_agenda_id" IN (SELECT U0."id" FROM "agendas_agenda" U0 WHERE U0."slug" = 'armentieres-david-baudin')
) ORDER BY "extra_data_keys" ASC;

Les OR, c'est inbon. Les OR sur les jointures, c'est plusinbon.

L'écriture suivante est bien meilleure, et plus rapide d'un facteur 100 sur au moins un des tenants (12ms vs 1,3s).

chrono=# explain analyze
SELECT * FROM (
        SELECT
                DISTINCT ON (extra_data_keys) jsonb_object_keys("agendas_booking"."extra_data") AS "extra_data_keys" 
        FROM "agendas_booking" 
                INNER JOIN "agendas_event" ON ("agendas_booking"."event_id" = "agendas_event"."id")
                INNER JOIN "agendas_agenda" ON ("agendas_event"."agenda_id" = "agendas_agenda"."id")
        WHERE 
                agendas_agenda.slug = 'armentieres-david-baudin'
UNION
        SELECT
                DISTINCT ON (extra_data_keys) jsonb_object_keys("agendas_booking"."extra_data") AS "extra_data_keys" 
        FROM "agendas_booking" 
                INNER JOIN "agendas_event" ON ("agendas_booking"."event_id" = "agendas_event"."id")
                INNER JOIN "agendas_virtualmember" ON ("agendas_event"."agenda_id" = "agendas_virtualmember"."real_agenda_id")
        WHERE
                "agendas_virtualmember"."virtual_agenda_id" IN (SELECT U0."id" FROM "agendas_agenda" U0 WHERE U0."slug" = 'armentieres-david-baudin')
) foo ORDER BY  "extra_data_keys" ASC;

En effet, le OR sur la jointure impose une lecture complète des tables agendas_event et agendas_booking, alors qu'avec l'UNION on peut faire deux index scan à la place.