bonsoir

je suis en train d'essayer differentes approches pour concevoir ma base de donnees au mieux en essayant des requetes types sur 400 000 lignes environ

j'ai lu le tuto de lord casque noir ou il est ecrit qu'une requete d'une demi seconde est lente ! (perso je sais meme pas si je trouverais un mot dans le dico en une minute alors je suis pas trop exigent)

d'un autre coté, j'ai lu qu'il y avait des differences de performances entre les serveurs windows et linux

sachant que je travaille avec wamp, comment dois je interpreter un resultat obtenu entre 0.5 et 1.5 secondes en local ? vais je gagner/perdre beaucoup une fois en ligne sur un serveur mutualisé ?

quand on passe d'une page a une autre quelle est la proportion "recherches infos / chargement + affichage page" ?

si vous avez des exemples, des avis ou des idees, je suis prenneur

Si ta grosse requête de recherche prend 0.5-1.5s c'est ennuyeux mais pas si grave (tant que tu n'as pas un grand nombre d'utilisateurs connectés). Si tu t'attends à ce qu'un utilisateur clique dessus toutes les 10s, et qu'elle prend 0.5s, ça va largement. Si tu t'attends à 10 clics par seconde sur la recherche, par contre c'est mort.

Pour une requête de recherche lente,
- mets un LIMIT (personne ne va regarder la page de résultats 852)
- si tu pagines, mets en cache les résultats des premières pages, ça évite de refaire la requête quand un gus clique sur la page 2. Par exemple tu peux stocker dans la session un array des ids des 300 premières lignes trouvées.

Ce qui est plus grave, c'est quand ta page fait 50 requêtes lentes.

C'est une requête de recherche fulltext sur une table de 400k lignes non ? Sur ce type de requête :

- si tu recherches des mots peu courants, elle sera rapide,
- si tu recherches des mots présents dans 40k lignes, ça ne sera jamais rapide, et de plus, les résultats ne servent à rien (l'utilisateur va préciser sa recherche en ajoutant des mots), d'où le LIMIT.

bah c'est pour l'histoire des cases a cocher en plus des recherches textuelles si je mets des champs a remplir

tu m'avais parlé de recherches sur des bases wikipedia de plusieurs teras ... y'a t il des trucs magiques a connaitre en plus des indexs ?

dois je m'attendre a de grosses differences (en + ou en -) entre ma version locale et en ligne ? c'est juste pour savoir si ca sert vraiment a quelque chose que je fasse tous mes essais !!!

pour le limit : c'etait bien prevu !
pour le nombre de requetes : normalement il n'y en aura qu'une ou deux par page (infos questionnaires et infos membre je pense) donc voila
et pour ce qui est du traffic, je n'ai aucune idees de ce a quoi je peux m'attendre, c'est plus pour m'amuser et apprendre qu'autre chose

de retour apres mes tests voici mes resultats :

recapitulons d'abord le but du jeu : stocker dans une bdd les reponses aux questions d'un formulaire :
- 6 themes
- 24 questions
- 200 checkboxs environ

suite aux conseils des specialistes, je me suis penché sur l'operateur @> pour stocker/rechercher mes reponses dans des tableaux d'id

deux approches :
1/ stocker toutes les reponses dans un seul tableau :
j'ai rempli via php mes formulaires avec des tableaux contenant entre 20 et 100 ids
je ressors mes resultats en moins de 2s

2/ stocker les reponses dans 6 tableaux mais contenant entre 6 et 20 ids
185 s pour ressortir une requete avec limit 1000 sur 3 tableaux seulement

bien que la deuxieme approche semble la plus logique pour ranger correctement des donnees elle est nettement moins performante (sic)

des idees ? d'autres approches completement differentes ?

(j'ai deja essaye de stocker une reponse par colonne : performances potables mais schema plutot degueu quand meme !)

> je ressors mes resultats en moins de 2s

Bizarre, si je fais un test avec 100.000 lignes contenant des arrays de 20 à 100 nombres aléatoires entiers distincts entre 0 et 200:

test=> begin;
test=> create table qcm( cases integer[] not null );CREATE TABLE
test=> CREATE INDEX qcm_cases ON qcm USING GIST (cases gist__int_ops);
test=> INSERT INTO qcm (cases) SELECT (SELECT array_agg(DISTINCT (random()*200)::INTEGER) FROM generate_series(1,20+(random()*80+n*0)::INTEGER)) FROM (SELECT generate_series(1,100000) n) n;
test=> commit;
test=> vacuum analyze qcm;
test=> explain analyze select * from qcm where cases @> '{10,20,30}';
                                                        QUERY PLAN                                                        
--------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on qcm  (cost=33.33..378.21 rows=100 width=227) (actual time=35.993..38.353 rows=2151 loops=1)
   Recheck Cond: (cases @> '{10,20,30}'::integer[])
   ->  Bitmap Index Scan on qcm_cases  (cost=0.00..33.30 rows=100 width=0) (actual time=35.685..35.685 rows=2151 loops=1)
         Index Cond: (cases @> '{10,20,30}'::integer[])
 Total runtime: 38.558 ms

la recherche est très rapide...

donc qu'appelles-tu "je ressors mes resultats" ? quelle requête, etc...

que donne cette requête dans la console : select icount('{1,2}');

evidemment encore un tour de magie de mister lord casque noir !

(je m'en serais pas douté )

j'ai rempli ma table comme je t'avais expliqué dans un
precedent post et j'avais collé dessus un index gin (vu que : ERROR: data type integer[] has no default operator class for access method "gist")

j'avais trouve dans la doc la methode pour afficher les default operators et j'avais vu @> avec gin (est ce du a ma version antédiluvienne de postgres ?)

pour mes requetes, j'ai relevé :

select id, tableau_cases from test where tableau_cases @> '{24,10}'
/* 48s avec limit 1000  */
/* 400 ms avec index gin  */


select id, tableau_cases from test where tableau_cases @> '{24,10,17,12,30,7}' limit 1000
/*1s7 pour 912 lignes trouvees*/

(j'avais essayé cette methode parce que ca semblait plus ressembler a

Citation : lord casque noir dans autre post

questions( question_id )
reponses( reponse_id, question_id fk )

les jointrures en moins

select icount('{1,2}');

ERROR: function icount(unknown) does not exist
LINE 1: select icount('{1,2}');
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.

par contre je te bats : Temps d'exécution total : 35.136 ms lol

je viens de voir que free a enfin cree ma base postgres (apres 5 jours !) donc je vais faire mes essais en ligne maintenant ...
select icount('{1,2}') : meme erreur(et temps d'exec 10 ms lol)

je lis la doc mais je vois pas le rapport exactement ?

> trouves tu normal que ca aille plus vite avec un seul gros tableau que 6 petits ?

oui (c'est plus simple avec un seul index)

> ERROR: operator class "gist__int_ops" does not exist for access method "gist"
> icount('{1,2}'); marche pas

Hum, il me semble que free ont une version antédiluvienne de postgres aussi ...

Sur la 9.1 c'est inclus, sinon il doit falloir installer le module intarray (c'est un fichier sql à exécuter)... mais j'ai des trous de mémoire là !

ha parce qu'en plus tu ecris tout ca de memoire ?!
je croyais que t'avais au moins des postits partout a l'interieur de ce gros casque moi !
bon au moins ca valide (et surtout simplifie) la structure de la bdd (meme si je trouve que ca fait pas trop "merise" mais bon c'est moins pire que mes 200 colonnes de type bit(1) )

a moins que tu n'aies d'autres tours (postits) dans ton casque ...
(je me penche sur la question de l'installation de gist ... ca sera deja pas mal pour ce soir)





ps : le panneau danger c'etait juste pour changer de couleur

> j'ai trouvé du chinois ecrit en anglais par des russes !!!

ah ah ah
c'est une vieille page : tout ça est intégré par défaut dans les dernières versions...

essaie plutôt d'installer une version récente de pg...

> essaie plutôt d'installer une version récente de pg...

j'avais deja essayé mais :
- j'avais pas pu sauvegarder ma base dans un .csv (apparemment pas possible sous windows) et vu le temps que ca m'avait pris en php pour remplir mes colonnes, je voulais pas tout perdre
- du coup j'avais essayé de mettre a jour ma version mais va savoir pourquoi, il me demandait le mot de passe de l'utilisateur postgres et n'acceptait pas le mot de passe que je lui filais
- mais bon maintenant que je peux remplir ma base en 4s, je vais surement repartir de zero

mais comment ajouter des colonnes textes avec tes textes aleatoires (pas pour faire des essais fulltext mais pour simuler ma base bien remplie (vu que j'avais vu que la taille des lignes influait sur les performances)

INSERT INTO qcm (cases) SELECT (SELECT random()*200000000000000000000000)::varchar) FROM (SELECT generate_series(1,100000) n) n;

ca me retourne une chaine ... de chiffres (c'est deja pas mal non ?)

[edit] j'ai meme le 'e' de la puissance de 10

> j'avais pas pu sauvegarder ma base

Utilise pg_dump et pg_restore pour le backup !

Encore une raison d'installer la version >9 : avant les extensions genre tsearch, gist, etc étaient sauvegardées avec la base, maintenant elles ne le sont plus (ce qui évite d'avoir un bordel de fonctions sql en trop dans ta sauvegarde, et d'avoir à les désinstaller et réinstaller pour les upgrades...)

> comment ajouter des colonnes textes avec tes textes aleatoires

Alors tu pourrais mettre des colonnes qui contiennent un nombre aléatoire de "aaaaaaaaaaaaaaaaaa" mais comme postgres compresse les gros textes, et que ça se compresse très bien les "aaaaaaaaaaaaaaaaaa", du coup ça sert à rien !

Le plus simple c'est de prendre un ebook quelconque (au format txt) dans la langue de ton choix et de le découper en fragments dont la taille est représentative de ce que tu auras dans ta base (avec php par exemple) puis d'insérer ça. Groupe tous les INSERT dans une seule transaction, ça ira beaucoup plus vite...

>Utilise pg_dump et pg_restore pour le backup !

je prends note mais j'ai pas bossé la dessus ce soir

>ça se compresse très bien les "aaaaaaaaaaaaaaaaaa", du coup ça sert à rien !

ha bon ? bon je prends note aussi ...

>Le plus simple c'est de prendre un ebook quelconque (au format txt) dans la langue de ton choix et de le >découper en fragments dont la taille est représentative de ce que tu auras dans ta base (avec php par exemple) >puis d'insérer ça.
> Groupe tous les INSERT dans une seule transaction, ça ira beaucoup plus vite...


je vois bien comment faire pour couper une chaine et tout inserer un par un mais l'histoire de la transaction j'ai pas encore fouillé (comment ca peut aller plus vite alors que le travail est le meme)
j'essayerais pour voir la difference de rapidité mais vu que j'etais un peu refroidi par mes scripts php qui prenaient des heures je me suis aventuré a tout faire en sql

je t'avoue que j'ai pas tout compris ce que j'ai fait

/* j'ai encore du mal a visualiser le principe de : */
SELECT array_agg(DISTINCT (random()*200)::INTEGER) FROM generate_series(1,20+(random()*80+n*0)::INTEGER))

mais je suis arrivé a :

INSERT INTO qcm (titre) SELECT (
SELECT array_to_string(
                    ARRAY(
                          SELECT chr(33+(random()*255)::INTEGER)
                          FROM generate_series(1,20+(random()*80+n*0)::INTEGER)
                          )
                 ,'') As chaine
)FROM (SELECT generate_series(1,100000) n) n;
/* je connais pas la plage des codes pour l'utf8 alors j'ai mis ca c marrant */

j'ai pas reussi a faire marcher string_agg() ou concat() mais y'avait cette forme dans un tuto bien foutu

NB : tu noteras egalement que je m'ameliore aussi en chinois-anglais, mon autre satisfaction du soir !

et la je viens de me rendre compte que tu avais ecrit :

Citation : lord casque noir

LE PLUS SIMPLE c'est de prendre un ebook quelconque...

[edit] j'en ai oublié ma question du jour : comment trouver les qcm ou les cases 29,8,21,9 N'ONT PAS ete cochees sans faire

select cases from qcm where not(cases @> '{29}') and not(cases @> '{8}') and not(cases @> '{21}') and not(cases @> '{9}')

c'etait aussi pour ca que j'etais parti sur un codage en binaire : il suffisait de faire un masque pour les cases cochees et un masque pour les cases qu'on recherche non cochees