pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Oleg Bartunov <oleg@sai.msu.su>
To: pgsql-performance@nullmx.com
Cc: pgsql-performance@postgresql.org
Subject: Re: tsearch2 headline and postgresql.conf
Date: Sun, 22 Jan 2006 11:24:55 +0300 (MSK)
Message-ID: <Pine.GSO.4.63.0601221110190.14417@ra.sai.msu.su> (raw)
In-Reply-To: <43D3386A.8000004@nullmx.com>
References: <43D3386A.8000004@nullmx.com>

You didn't provides us any query with explain analyze.
Just to make sure you're fine.

 	Oleg
On Sun, 22 Jan 2006, pgsql-performance@nullmx.com wrote:

> 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?
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>              http://www.postgresql.org/docs/faq
>

 	Regards,
 		Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83



view thread (3+ messages)  latest in thread

Message-ID: <Pine.GSO.4.63.0601221110190.14417@ra.sai.msu.su>
Permalink:  ../Pine.GSO.4.63.0601221110190.14417@ra.sai.msu.su/
Also on:    postgresql.org/message-id/Pine.GSO.4.63.0601221110190.14417@ra.sai.msu.su

 · 

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: oleg@sai.msu.su, pgsql-performance@nullmx.com
  Subject: Re: tsearch2 headline and postgresql.conf
  In-Reply-To: <Pine.GSO.4.63.0601221110190.14417@ra.sai.msu.su>
  References: <43D3386A.8000004@nullmx.com> <Pine.GSO.4.63.0601221110190.14417@ra.sai.msu.su>

* 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