Skip site navigation (1) Skip section navigation (2)

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

From: Teodor Sigaev <teodor(at)sigaev(dot)ru>
To: Diogo Biazus <diogo(at)ikono(dot)com(dot)br>
Cc: Dann Corbit <DCorbit(at)connx(dot)com>, pgsql-general(at)postgresql(dot)org
Subject: Re: Wich hardware suits best for large full-text indexed
Date: 2004-03-31 07:18:34
Message-ID: 406A70CA.1040602@sigaev.ru (view raw or flat)
Thread:
Lists: pgsql-general
> 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;

What is distribution of records by codgrupousuario field?
You can use multi-column index (with contrib/btree_gist):
create index fti on qrydocumentos using gist (codgrupousuario, conteudo_stem_ix);
or partial index
create index fti on qrydocumentos using gist (conteudo_stem_ix) where 
codgrupousuario = 1;

One more. Let you use ispell dictionary ( I suppose, for Portuguese language, 
http://fmg-www.cs.ucla.edu/geoff/ispell-dictionaries.html#Portuguese-dicts )

-- 
Teodor Sigaev                                  E-mail: teodor(at)sigaev(dot)ru

In response to

Responses

pgsql-general by date

Next:From: bwhiteDate: 2004-03-31 08:10:33
Subject: Question about rtrees (overleft replacing left in nodes)
Previous:From: Ericson SmithDate: 2004-03-31 06:21:47
Subject: Re: Large DB

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group