Effet aléatoire

Rupture hasardeuse de Nahal

      Le Grand Chef Oumpfor demande à son scribe, le gobelin mineur Margiono, de lui envoyer un géant au hasard. Margiono s’exécute :

select version(); version ----------------------------------------------------------------------------------------------------- PostgreSQL 10devel on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-17) 6.3.0 20170510, 64-bit (1 ligne) create table geants( idg integer generated always as identity primary key, idu uuid, genre char(1), taille smallint, masse smallint, actif boolean, devise varchar(128), pw smallint, heureux boolean, couleur varchar(8), veteran boolean, clan smallint, gabarit varchar(8), revenu integer, pm smallint, berserk boolean, tutelaire smallint, ere varchar(10), cyclope boolean); CREATE TABLE CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; CREATE EXTENSION WITH serie(i, r1) AS (SELECT generate_series(1,100000,1), random()) insert into geants(idu, genre, taille, masse, actif, devise, pw, heureux, couleur, veteran, clan, gabarit, revenu, pm, berserk, tutelaire, ere, cyclope) select uuid_generate_v4(), case when random() < 0.45 then 'M' else 'F' end, 200 + (trunc(random() * 200 + 1)), 300 + (trunc(random() * 200 + 1)), case when random() < 0.01 then false when random() > 0.5 and random() < 0.99 then false else true end, upper(md5(random()::text)), (trunc(random()*100 + 1)), case when random() < 0.1 then false else true end, case when r1 <= 0.29998 then 'GRIS' when r1 <= 0.59998 then 'NOIR' else 'BLEU' end, case when random() < 0.9 then false else true end, (trunc(random()*1000 + 1)), case when r1 <= 0.29999 then 'PETIT' when r1 <= 0.59999 then 'MOYEN' else 'GRAND' end, (trunc(random()*1000000 + 1)), (trunc(random()*10 + 1)), case when r1 <= 0.3 then true when r1 <= 0.6 then false else null end, (trunc(random()*10 + 1)), case when r1 <= 0.30001 then 'TAUREAU' when r1 <= 0.60001 then 'LICORNE' else 'DRAGON' end, case when r1 <= 0.30002 then true when r1 <= 0.60002 then false else null end from serie; INSERT 0 100000 select idg, idu from geants where idg = (trunc(random() * 100000 + 1))::integer; idg | idu -------+-------------------------------------- 23302 | d3414ac8-71cb-40b0-adf8-70b45f5f9008 50816 | e214ff39-c968-4967-8941-882270137adb 97732 | 62941aa3-a8f1-4163-b5d9-24729566e0e2 (3 lignes) explain select idg, idu from geants where idg = (trunc(random() * 100000 + 1))::integer; QUERY PLAN ------------------------------------------------------------------------------------------------------- Seq Scan on geants (cost=0.00..4140.00 rows=1 width=20) Filter: (idg = (trunc(((random() * '100000'::double precision) + '1'::double precision)))::integer) (2 lignes)

      Trois lignes renvoyées en testant une égalité sur la clé primaire ?!? Comment est-ce possible ? Le plan d’exécution nous donne une indication. La table geants est entièrement balayée alors que nous disposons d’un index unique et qu’il n’y a pas d’incompatiblité de type rendant impossible le passage par cet index.
      En fait, nous avons comparé chaque idg avec une valeur aléatoire différente donc nous aurions aussi pu obtenir 0 ligne, 1 ligne, 2 lignes... Pour appeler UN géant au hasard il faut déterminer une seule valeur aléatoire entre min(idg) et max(idg) puis tester l’égalité avec idg. Démonstration :

select idg, idu from geants where idg = (select (trunc(random() * 100000 + 1))::integer); idg | idu ------+-------------------------------------- 7466 | e914d355-ac1e-4f85-b988-b9b7e60d5fa6 (1 ligne) explain select idg, idu from geants where idg = (select (trunc(random() * 100000 + 1))::integer); QUERY PLAN --------------------------------------------------------------------------- Index Scan using geants_pkey on geants (cost=0.32..8.33 rows=1 width=20) Index Cond: (idg = $0) InitPlan 1 (returns $0) -> Result (cost=0.00..0.02 rows=1 width=4) (4 lignes) with aleatoire(valeur) as (values((trunc(random() * 100000 + 1))::integer)) select idg, idu from geants where idg = (select valeur from aleatoire); idg | idu -------+-------------------------------------- 86717 | c8b1563d-e6d8-45fc-ae6d-de650b352abb (1 ligne) explain with aleatoire(valeur) as (values((trunc(random() * 100000 + 1))::integer)) select idg, idu from geants where idg = (select valeur from aleatoire); QUERY PLAN --------------------------------------------------------------------------- Index Scan using geants_pkey on geants (cost=0.32..8.34 rows=1 width=20) Index Cond: (idg = $1) CTE aleatoire -> Values Scan on "*VALUES*" (cost=0.00..0.01 rows=1 width=4) InitPlan 2 (returns $1) -> CTE Scan on aleatoire (cost=0.00..0.02 rows=1 width=4) (6 lignes)

      Avec ces 2 écritures une seule valeur aléatoire est générée, l’index unique est utilisé pour tester l’égalité avec la colonne idg et une seule ligne est retournée.

Mise à jour : 16/05/2017