From: pgsql-performance@nullmx.com
To: pgsql-performance@postgresql.org
Subject: tsearch2 headline and postgresql.conf
Date: Sun, 22 Jan 2006 01:46:50 -0600
Message-ID: <43D3386A.8000004@nullmx.com> (raw)
Hi folks,
I'm not sure if this is the right place for this but thought I'd ask.
I'm relateively new to postgres having only used it on 3 projects and am
just delving into the setup and admin for the second time.
I decided to try tsearch2 for this project's search requirements but am
having trouble attaining adequate performance. I think I've nailed it
down to trouble with the headline() function in tsearch2.
In short, there is a crawler that grabs HTML docs and places them in a
database. The search is done using tsearch2 pretty much installed
according to instructions. I have read a couple online guides suggested
by this list for tuning the postgresql.conf file. I only made modest
adjustments because I'm not working with top-end hardware and am still
uncertain of the actual impact of the different paramenters.
I've been learning 'explain' and over the course of reading I have done
enough query tweaking to discover the source of my headache seems to be
headline().
On a query of 429 documents, of which the avg size of the stripped down
document as stored is 21KB, and the max is 518KB (an anomaly), tsearch2
performs exceptionally well returning most queries in about 100ms.
On the other hand, following the tsearch2 guide which suggests returning
that first portion as a subquery and then generating the headline() from
those results, I see the query increase to 4 seconds!
This seems to be directly related to document size. If I filter out
that 518KB doc along with some 100KB docs by returning "substring(
stripped_text FROM 0 FOR 50000) AS stripped_text" I decrease the time to
1.4 seconds, but increase the risk of not getting a headline.
Seeing as how this problem is directly tied to document size, I'm
wondering if there are any specific settings in postgresql.conf that may
help, or is this just a fact of life for the headline() function? Or,
does anyone know what the problem is and how to overcome it?
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: pgsql-performance@nullmx.com
Subject: Re: tsearch2 headline and postgresql.conf
In-Reply-To: <43D3386A.8000004@nullmx.com>
* 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