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

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

From: Diogo Biazus <diogo(at)ikono(dot)com(dot)br>
To: Dann Corbit <DCorbit(at)connx(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Wich hardware suits best for large full-text indexed
Date: 2004-03-30 22:31:36
Message-ID: 4069F548.2030804@ikono.com.br (view raw or flat)
Thread:
Lists: pgsql-general
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)

-- 
Diogo Biazus
diogo(at)ikono(dot)com(dot)br
http://www.ikono.com.br

In response to

Responses

pgsql-general by date

Next:From: Dann CorbitDate: 2004-03-30 22:39:52
Subject: Re: Wich hardware suits best for large full-text indexed databases
Previous:From: Dann CorbitDate: 2004-03-30 22:12:00
Subject: Re: Wich hardware suits best for large full-text indexed databases

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