Skip to content

Instantly share code, notes, and snippets.

@luylucas10

luylucas10/teste_postgresql

Last active June 24, 2020 21:54
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save luylucas10/fd865ab52ee349ec7cf29e961717e38b to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save luylucas10/fd865ab52ee349ec7cf29e961717e38b to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. luylucas10 revised this gist Jun 24, 2020. 1 changed file with 8 additions and 9 deletions.
    17 changes: 8 additions & 9 deletions teste_postgresql
    View file
    Original file line number Diff line number Diff line change
    @@ -53,16 +53,15 @@ FROM (SELECT (SELECT concat('Marie-', string_agg(x, '-')) as name_first_female
    generator as id
    FROM generate_series(1, 1000000) as generator) main_sub;

    explain analyse select *
    from usuario
    where nome ilike '%Eudes%';
    explain analyse select * from usuario where nome ilike '%Eudes%';
    explain analyse select * from usuario where codigo ilike '%4bc%';
    explain analyse select * from usuario where nome ilike '%eudes%' and codigo ilike '%4bc%';

    CREATE EXTENSION pg_trgm;

    CREATE INDEX ix_usuario_nome
    ON usuario
    USING gin (nome gin_trgm_ops);
    CREATE INDEX ix_usuario_nome ON usuario USING gin (nome gin_trgm_ops);
    CREATE INDEX ix_usuario_codigo ON usuario USING gin (codigo gin_trgm_ops);

    explain analyse select *
    from usuario
    where nome ilike '%Eudes%';
    explain analyse select * from usuario where nome ilike '%Eudes%';
    explain analyse select * from usuario where codigo ilike '%4bc%';
    explain analyse select * from usuario where nome ilike '%eudes%' and codigo ilike '%4bc%';
  2. luylucas10 created this gist Jun 24, 2020.
    68 changes: 68 additions & 0 deletions teste_postgresql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,68 @@
    create table usuario
    (
    id serial primary key,
    nome varchar(100) not null,
    codigo varchar(11) not null,
    idade integer not null
    );

    CREATE OR REPLACE FUNCTION random_text_md5_v2(INTEGER)
    RETURNS TEXT
    LANGUAGE SQL
    AS $$
    select upper(
    substring(
    (SELECT string_agg(md5(random() :: TEXT), '') FROM generate_series(
    1,
    CEIL($1 / 32.) :: integer)), 1, $1));
    $$;

    CREATE OR REPLACE FUNCTION generate_age(low INT, high INT)
    RETURNS INT AS
    $$
    BEGIN
    RETURN floor(random() * (high - low + 1) + low);
    END;
    $$
    language 'plpgsql'
    STRICT;

    insert into usuario (nome, codigo, idade)
    SELECT name_first_male || ' ' || last_name_part1, random_text_md5_v2(11), generate_age(18, 65)
    FROM (SELECT (SELECT concat('Marie-', string_agg(x, '-')) as name_first_female
    FROM (select start_arr [ 1 + ((random() * 100) :: int) % 32 ]
    FROM (select '{Claude,Thérèse,Géraldine,Sylvie,Sophie,Solange,Vivianne,Cunégonde,Albertine,Charlotte,Caroline,Célestine,Bérangère,Sylvianne,Alphonsine,Claire,Françoise,Chantal,Geneviève,Christine,Louise,Adeline,Céleste,Angélique,Clothilde,Jeanne,Pierre,Valentine,Paule,Noëlle,Lise,Cécile}' :: text [] as start_arr) syllarr,
    -- need 3 syllabes, and force generator interpretation with the '*0' (else 3 same syllabes)
    generate_series(1,
    1 + (((generator :: int % 3) * (random() * 0.5)) :: int % 3))) AS con_name_female (x)),
    (SELECT concat('Jean-', string_agg(y, '-')) as name_first_male
    FROM (select start_arr [ 1 + ((random() * 100) :: int) % 33 ]
    FROM (select '{François,Pierre,Michel,Sylvain,Sébastien,Eudes,Raymond,Claude,Régis,Charles,Maxence,Célestin,Baptiste,Grégoire,Hubert,Antonin,Stanislas,Gérald,Albert,Marie,Guy,Yves,Louis,Gabriel,Jérémie,Bartholomé,Mathieu,Vincent,Nicolas,Joachim,Olivier,Daniel,Philippe}' :: text [] as start_arr) syllarr,
    -- need 3 syllabes, and force generator interpretation with the '*0' (else 3 same syllabes)
    generate_series(1,
    1 + (((generator :: int % 3) * (random() * 0.5)) :: int % 3))) AS con_name_male (y)),
    (SELECT concat(last_name_part1a, last_char) as last_name_part1
    FROM (SELECT initcap(concat(string_agg(z1, ''))) as last_name_part1a
    FROM (select last_arr [ 1 + ((random() * 100 + (generator * 0)) :: int) % 35 ]
    FROM (select '{cu,char,bar,rau,reau,ban,beau,che,choi,moi,dur,dup,bou,gre,gru,choi,chau,col,ast,ara,ame,aud,souch,chon,bon,cheau,tallo,lio,chau,ron,rency,ramb}' :: text [] as last_arr) sub1,
    generate_series(1, 3 + (generator * 0))) AS con_name_first (z1)) sub2,
    (select last_last [ 1 + ((random() * 10 + (generator * 0)) :: int) % 6 ] as last_char
    FROM (select '{x,t,d,e,s}' :: text [] as last_last) sub3) sub4),
    (select part_arr [ 1 + ((random() * 100 + (generator * 0)) :: int) % 50 ] as last_name_part2
    FROM (select '{II,III,De Loinloin,De Meumeu,De Parloin,De Parlà,De MontLoin,De Vallointain,De Perpète,De Pétaouchnok,De Trifouillis-Les-Oies,De Clochemerle,Du Troupaumé,De Saint-glin-glin,De Berdouille,De Macapète,De Foufnie,De Pamparigouste,De GlinGlin,De Linlin,De Fouillard,D''Apeuprès,D''Icibas,D''Autemps,D''Auloin}' :: text [] as part_arr) sub),
    generator as id
    FROM generate_series(1, 1000000) as generator) main_sub;

    explain analyse select *
    from usuario
    where nome ilike '%Eudes%';

    CREATE EXTENSION pg_trgm;

    CREATE INDEX ix_usuario_nome
    ON usuario
    USING gin (nome gin_trgm_ops);

    explain analyse select *
    from usuario
    where nome ilike '%Eudes%';