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.comhttp://www.megabooksearch.com - Search many book listing sites at once
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