pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Rick Jansen <rick@rockingstone.nl>
To: pgsql-performance@postgresql.org
Subject: Tsearch2 performance on big database
Date: Tue, 22 Mar 2005 13:28:07 +0100
Message-ID: <42400F57.3040604@rockingstone.nl> (raw)

Hi,

I'm looking for a *fast* solution to search thru ~ 4 million records of 
book descriptions. I've installed PostgreSQL 8.0.1 on a dual opteron 
server with 8G of memory, running Linux 2.6. I haven't done a lot of 
tuning on PostgreSQL itself, but here's the settings I have changed so far:

shared_buffers = 2000 (anything much bigger says the kernel doesnt allow 
  it, still have to look into that)
effective_cache_size = 32768

Here's my table:

ilab=# \d books
                                        Table "public.books"
     Column     |          Type          |                        Modifiers
---------------+------------------------+----------------------------------------------------------
  recordnumber  | integer                | not null default 
nextval('books_recordnumber_seq'::text)
  membernumber  | integer                | not null default 0
  booknumber    | character varying(20)  | not null default 
''::character varying
  author        | character varying(60)  | not null default 
''::character varying
  titel         | text                   | not null
  description   | character varying(100) | not null default 
''::character varying
  descriprest   | text                   | not null
  price         | bigint                 | not null default 0::bigint
  keywords      | character varying(100) | not null default 
''::character varying
  dollarprice   | bigint                 | not null default 0::bigint
  countrynumber | smallint               | not null default 0::smallint
  entrydate     | date                   | not null
  status        | smallint               | not null default 0::smallint
  recordtype    | smallint               | not null default 0::smallint
  bookflags     | smallint               | not null default 0::smallint
  year          | smallint               | not null default 0::smallint
  firstedition  | smallint               | not null default 0::smallint
  dustwrapper   | smallint               | not null default 0::smallint
  signed        | smallint               | not null default 0::smallint
  cover         | smallint               | not null default 0::smallint
  specialfield  | smallint               | not null default 0::smallint
  idxfti        | tsvector               |
Indexes:
     "recordnumber_idx" unique, btree (recordnumber)
     "idxfti_idx" gist (idxfti)

idxfti is a tsvector of concatenated description and descriprest.

ilab=# select 
avg(character_length(description)),avg(character_length(descriprest)) 
from books;
          avg         |         avg
---------------------+----------------------
  89.1596992873947218 | 133.0468689304200538

Queries take forever to run. Right now we run a MySQL server, on which 
we maintain our own indices (we split the description fields by word and 
have different tables for words and the bookdescriptions they appear in).

For example, a query for the word 'terminology' on our MySQL search 
takes 5.8 seconds and returns 375 results. The same query on postgresql 
using the tsearch2 index takes 30802.105 ms and returns 298 results.

How do I speed this up? Should I change settings, add or change indexes 
or.. what?

Rick Jansen
-- 
Systems Administrator for Rockingstone IT
http://www.rockingstone.com
http://www.megabooksearch.com - Search many book listing sites at once



view thread (14+ messages)  latest in thread

Message-ID: <42400F57.3040604@rockingstone.nl>
Permalink:  ../42400F57.3040604@rockingstone.nl/
Also on:    postgresql.org/message-id/42400F57.3040604@rockingstone.nl

 · 

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 using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: rick@rockingstone.nl
  Subject: Re: Tsearch2 performance on big database
  In-Reply-To: <42400F57.3040604@rockingstone.nl>

* 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