X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 8A66C9DCA42 for ; Sun, 22 Jan 2006 03:53:41 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 59560-02 for ; Sun, 22 Jan 2006 03:53:40 -0400 (AST) X-Greylist: delayed 00:06:39.507235 by SQLgrey- Received: from k2smtpout01-01.prod.mesa1.secureserver.net (k2smtpout01-02.prod.mesa1.secureserver.net [64.202.189.89]) by postgresql.org (Postfix) with SMTP id 5C1C89DC818 for ; Sun, 22 Jan 2006 03:53:37 -0400 (AST) Received: (qmail 12793 invoked from network); 22 Jan 2006 07:46:59 -0000 Received: from unknown (HELO mail.servantwebhosting.com) ([68.178.159.82]) (envelope-sender ) by k2smtpout01-01.prod.mesa1.secureserver.net (qmail-ldap-1.03) with SMTP for ; 22 Jan 2006 07:46:59 -0000 Received: (qmail 30432 invoked by uid 2527); 22 Jan 2006 00:46:59 -0700 Received: from 70.64.198.193 by jonah.prod.phx1.secureserver.net (envelope-from , uid 2525) with qmail-scanner-1.25-st-qms (clamdscan: 0.86.2/989. spamassassin: 3.0.2. perlscan: 1.25-st-qms. Clear:RC:0(70.64.198.193):SA:0(-0.6/5.0):. Processed in 4.440342 secs); 22 Jan 2006 07:46:59 -0000 X-Antivirus-MYDOMAIN-Mail-From: pgsql-performance@nullmx.com via jonah.prod.phx1.secureserver.net X-Antivirus-MYDOMAIN: 1.25-st-qms (Clear:RC:0(70.64.198.193):SA:0(-0.6/5.0):. Processed in 4.440342 secs Process 27907) X-Envelope-From: pgsql-performance@nullmx.com Received: from s010600e0296eec95.mj.shawcable.net (HELO ?192.168.1.131?) (rob@nullmx.com@70.64.198.193) by mail.servantwebhosting.com with AES256-SHA encrypted SMTP; 22 Jan 2006 00:46:54 -0700 Message-ID: <43D3386A.8000004@nullmx.com> Date: Sun, 22 Jan 2006 01:46:50 -0600 From: pgsql-performance@nullmx.com Reply-To: pgsql-performance@nullmx.com User-Agent: Mozilla Thunderbird 1.0.7 (Windows/20050923) X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: tsearch2 headline and postgresql.conf Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.55 required=5 tests=[NO_REAL_NAME=0.55] X-Spam-Score: 0.55 X-Spam-Level: X-Archive-Number: 200601/404 X-Sequence-Number: 16882 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?