Re: Wich hardware suits best for large full-text indexed

Diogo,

could you send us 'explain analyze' for fts-specific part of your
query ? For example,

www=# explain analyze select title from s_pages where to_tsquery('regression') @@ fts_index;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Index Scan using s_gist_key on s_pages (cost=0.00..5.95 rows=1 width=29) (actual time=0.781..11.403 rows=62 loops=1)
Index Cond: ('\'regress\''::tsquery @@ fts_index)
Filter: ('\'regress\''::tsquery @@ fts_index)
Total runtime: 11.554 ms
(4 rows)

Frankly, I don't understand your query :) Could you explain what do you
want to find ?

qrydocumentos.conteudo_stem_ix @@ to_tsquery('default_portuguese', 'brasil')

Oleg

On Tue, 30 Mar 2004, Diogo Biazus wrote:

> Dann Corbit wrote:
>
> >What does the EXPLAIN command say about the slowest queries?
> >
> >
> explain SELECT qrydocumentos.coddocumento, qrydocumentos.nomedocumento,
> qrydocumentos.conteudo, qrydocumentos.tamanho, qrydocumentos.hora,
> qrydocumentos.data, qrydocumentos.codfonte, qrydocumentos.nomefonte,
> qrydocumentos.numeroimagens as "numeroImagens", qrydocumentos.subtitulo,
> qrydocumentos.codtipodocumento, qrydocumentos.codformato, numeroacessos
> AS acessos FROM qrydocumentos WHERE qrydocumentos.codgrupousuario = 1
> AND (qrydocumentos.conteudo_stem_ix @@ to_tsquery('default_portuguese',
> 'brasil')) ORDER BY ajustadata(qrydocumentos.datapublicacao) DESC,
> ajustahora(qrydocumentos.horapublicacao) DESC,
> qrydocumentos.coddocumento DESC;
>
> QUERY PLAN
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Sort (cost=1270.87..1271.60 rows=293 width=880)
> Sort Key: ajustadata(documentos.datapublicacao),
> ajustahora(documentos.horapublicacao), documentos.coddocumento
> -> Hash Join (cost=1246.88..1258.87 rows=293 width=880)
> Hash Cond: ("outer".codfonte = "inner".codfonte)
> -> Merge Join (cost=1245.38..1252.25 rows=293 width=861)
> Merge Cond: (("outer".codfonte = "inner".codfonte) AND
> ("outer".codtipodocumento = "inner".codtipodocumento))
> -> Sort (cost=1195.00..1195.73 rows=291 width=845)
> Sort Key: documentos.codfonte,
> documentos.codtipodocumento
> -> Index Scan using documentos_conteudo_stem_ix_ix
> on documentos (cost=0.00..1183.08 rows=291 width=845)
> Index Cond: (conteudo_stem_ix @@
> '\'brasil\''::tsquery)
> Filter: (conteudo_stem_ix @@
> '\'brasil\''::tsquery)
> -> Sort (cost=50.38..50.89 rows=204 width=16)
> Sort Key: fontes_gruposusuario.codfonte,
> tiposdocumento_gruposusuario.codtipodocumento
> -> Merge Join (cost=0.00..42.57 rows=204 width=16)
> Merge Cond: ("outer".codgrupousuario =
> "inner".codgrupousuario)
> -> Index Scan using
> fontes_gruposusuario_codgrupousuario_ix on fontes_gruposusuario
> (cost=0.00..24.75 rows=28 width=8)
> Index Cond: (codgrupousuario = 1)
> -> Index Scan using
> tiposdocumento_gruposusuario_codgrupousuario_ix on
> tiposdocumento_gruposusuario (cost=0.00..13.85 rows=542 width=8)
> -> Hash (cost=1.40..1.40 rows=40 width=19)
> -> Seq Scan on fontes (cost=0.00..1.40 rows=40 width=19)
>
>

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83