Discussion :

[gorgonite]

Je vais tenter avec un exemple minimaliste

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
 
CREATE TABLE Main (
	k1 int,
	k2 bigint,
	datage timestamp,
	champ1 text,
	ts_champ1 tsvector, -- ts_champ1=to_tsvector('english',champ1)
	champ2 text,
	PRIMARY KEY(k1, k2)
);
CREATE INDEX main_datage_idx ON Main USING btree(datage);
CREATE INDEX main_champ1_idx ON Main USING gin(ts_champ1) WITH ( fastupdate=on );
 
EXPLAIN ANALYZE
SELECT k2 FROM Main WHERE
	k1=1 AND champ1 ~* 'm1.*m2.*m3' AND champ2 ~* 'm4';
----------------------------------------------------------------------------------
Index Scan using main_pkey on main  (cost=0.00..8681.10 rows=1 width=8) (actual time=25.779..111.146 rows=32 loops=1)
   Index Cond: (k1 = 1)
   Filter: (((champ1)::text ~* 'm1.*m2.*m3'::text) AND (champ2 ~* 'm4'::text))
   Rows Removed by Filter: 4921
Total runtime: 111.255 ms
 
EXPLAIN ANALYZE
SELECT k2 FROM Main WHERE
	k1=1 AND ts_champ1 @@ to_tsquery('english','m1 & m2 & m3') AND champ2 ~* 'm4';
----------------------------------------------------------------------------------
Bitmap Heap Scan on main  (cost=894.12..950.09 rows=1 width=8) (actual time=424.047..426.501 rows=32 loops=1)
   Recheck Cond: ((k1 = 1) AND (ts_champ1 @@ '''m1'' & ''m2'' & ''m3'''::tsquery))
   Rows Removed by Index Recheck: 1169
   Filter: (contenu ~* 'm4'::text)
   Rows Removed by Filter: 120
   ->  BitmapAnd  (cost=894.12..894.12 rows=14 width=0) (actual time=423.993..423.993 rows=0 loops=1)
         ->  Bitmap Index Scan on main_pkey  (cost=0.00..201.02 rows=8352 width=0) (actual time=0.589..0.589 rows=4953 loops=1)
               Index Cond: (k1 = 1)
         ->  Bitmap Index Scan on main_champ1_idx  (cost=0.00..692.85 rows=6514 width=0) (actual time=423.281..423.281 rows=122056 loops=1)
         Index Cond: (ts_champ1 @@ '''m1'' & ''m2'' & ''m3'''::tsquery)
Total runtime: 426.600 ms

Mon but final est de faire, de manière très performante sur une table pour un filtre via k1 me renverra environ 8M lignes, une requête qui naïvement serait de ce type :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
SELECT k2 FROM Main WHERE
	k1=? AND champ1 ~* 'm1.*m2.*m3' AND champ2 ~* 'm4'
	AND datage>=?

• Évidemment m1,m2,m3 et m4 pourraient évoluer selon le besoin fonctionnel. Le but est de capturer des contextes autour d'événement, selon le type d'événement, tout peut changer... j'ai réussi à modéliser tous les cas envisageables, issus d'un REX d'autres projets similaires, avec ces paramètres (m1,m2,m3 devenant plus une liste d'ailleurs).
• Le dernier point auquel je réfléchis serait d'intégrer également ces paramètres en base pour donner une "interface de personnalisation de requêtes haut-niveau" (sans SQL visible et a priori loin de la structure réelle de la base)

A priori, je m'oriente plus sur les tsvector/tsquery pour la requête finale (impossible d'indexer selon des regexp en 9.2). Mais après une rapide simulation sur la taille réelle des données (8M lignes quotidienne et sûrement une centaine de requêtes de ce type à finir en 4h) que j'aurais en production, je crains que ça ne tiendra jamais...


Auriez-vous des conseils ?

[punkoff]

Bonjour,

pour la partie postgresql je ne pourrais pas répondre.

Par contre avez vous fait des tests avec volume, sur d'autre techno ? par exemple solr (qui sont plus fait pour ce genre de chose qu'un sgbd..)

[gorgonite]

Par contre avez vous fait des tests avec volume, sur d'autre techno ? par exemple solr (qui sont plus fait pour ce genre de chose qu'un sgbd..)

Non, et je n'ai pas forcément un grand choix dans les outils à ma disposition...