pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Chantal Ackermann <chantal.ackermann@biomax.de>
To: pgsql-performance@postgresql.org
Subject: tsearch performance
Date: Thu, 27 Feb 2003 14:21:54 +0100
Message-ID: <3E5E10F2.2000907@biomax.de> (raw)

hi all,

we have a tsearch index on the following table:

            Table "public.sentences"
     Column     |  Type   |     Modifiers
---------------+---------+--------------------
  sentence_id   | bigint  | not null
  puid          | integer |
  py            | integer |
  journal_id    | integer |
  sentence_pos  | integer | not null
  sentence_type | integer | not null default 0
  sentence      | text    | not null
  sentenceidx   | txtidx  | not null
Indexes: sentences_pkey primary key btree (sentence_id),
          sentence_uni unique btree (puid, sentence_pos, sentence),
          sentenceidx_i gist (sentenceidx),
          sentences_puid_i btree (puid),
          sentences_py_i btree (py)

the table contains 50.554.768 rows and is vacuum full analyzed.

The sentenceidx has been filled NOT USING txt2txtidx, but a custom 
implementation that should have had the same effect (parsing into 
words/phrases, deleting stop words). Nevertheless, might this be the 
reason for the very bad performance of the index, or is the table "just" 
to big (I hope not!)?

Note that the index on sentenceidx has not been clustered, yet. I wanted 
to ask first whether I might need to refill the column sentenceidx using 
txt2txtidx. (with so many rows every action has to be reconsidered ;-) )

EXPLAIN ANALYZE
SELECT sentence FROM sentences WHERE sentenceidx @@ 'amino\\ acid';


                           QUERY PLAN
-------------------------------------------------------------------
  Index Scan using sentenceidx_i on sentences
       (cost=0.00..201327.85 rows=50555 width=148)
       (actual time=973940.41..973940.41 rows=0 loops=1)

    Index Cond: (sentenceidx @@ '\'amino acid\''::query_txt)
    Filter: (sentenceidx @@ '\'amino acid\''::query_txt)

  Total runtime: 973941.09 msec
(4 rows)

thank you for any thoughts, hints, tips!
Chantal




Message-ID: <3E5E10F2.2000907@biomax.de>
Permalink:  ../3E5E10F2.2000907@biomax.de/
Also on:    postgresql.org/message-id/3E5E10F2.2000907@biomax.de

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: chantal.ackermann@biomax.de
  Subject: Re: tsearch performance
  In-Reply-To: <3E5E10F2.2000907@biomax.de>
  References: <3E5E10F2.2000907@biomax.de>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox