[9.2] Optimisation de requêtes avec une partie "plein texte" sujet

Je vais tenter avec un exemple minimaliste

Code:

---

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:

---

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).


• Je m'oriente plus sur les tsvector/tsquery pour la requête finale a priori (impossible d'indexer selon des regexp en 9.2)


• 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)

Auriez-vous des conseils ?


:merci: