Re: Wich hardware suits best for large full-text indexed - Mailing list pgsql-general

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@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83