pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Joshua D. Drake <jd@commandprompt.com>
To: Kris Kiger <kris@musicrebellion.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: tsearch2 poor performance
Date: Mon, 27 Sep 2004 11:46:43 -0700
Message-ID: <41586013.8050509@commandprompt.com> (raw)
In-Reply-To: <41585198.6080709@musicrebellion.com>
References: <71E37EF6B7DCC1499CEA0316A2568328DC9DF0@loki.wc.globexplorer.net>
	<4158409E.8060109@musicrebellion.com>
	<Pine.GSO.4.58.0409272040090.14980@ra.sai.msu.su>
	<41584E49.5040301@musicrebellion.com>
	<41584F91.6000109@commandprompt.com>
	<41585198.6080709@musicrebellion.com>

Kris Kiger wrote:
> Josh,
> 
>    I am running my tests on a dual processor PIII 1 GHz machine with 2Gb 
> of RAM.  There are four hard drives set up.  One for OS/logging and 
> three in a raid-5 configuration for the postgres data directory.
> 

What about other factors?

effective_cache_size
random_page_cost
sort_mem

FYI that example I gave was on a Dual P4-Xeon with 4 Gigs of ram and a 
RAID5 over 6 drives.

Just curious.

J



> Kris
> 
> Joshua D. Drake wrote:
> 
>> Hello,
>> I might of missed this on a previous message, BUT what type of 
>> hardware are we dealing with here? Is it possible that we just don't 
>> have enough IO/RAM to push this?
>> J
>> Kris Kiger wrote:
>>
>>> Oleg,
>>>    Thanks for the help on this.
>>>    The query I used to return the 508 number is:
>>>         SELECT * FROM stat('SELECT vector FROM product') ORDER BY 
>>> ndoc desc, word ;
>>>      Testing says, the more words I use, the faster the query is.  My 
>>> original search word, 'oil', appears in 226,357 documents 233,266 
>>> times. As far as distinct words go, 'oil' is middle of the road for 
>>> occurences.  As it is set up now, the best search time I am getting 
>>> on this single word is roughly 22 seconds. Kris
>>>
>>> Oleg Bartunov wrote:
>>>
>>>> Kris,
>>>>
>>>> do you actually have only 508 disctinct words ? Could you try
>>>> more complex queries, say 2-3 words. Does these queries run faster ?
>>>>     Oleg
>>>> On Mon, 27 Sep 2004, Kris Kiger wrote:
>>>>
>>>>> Regardless of caching, the queries are still taking 19~20 seconds 
>>>>> to run
>>>>> on the 3,000,000 rows.  I've played with performance tuning and 
>>>>> nothing
>>>>> seems to make much of a difference.  If I am reading that list from 
>>>>> stat
>>>>> correctly, then I am operating on  508 distinct words.  Is this the
>>>>> performance I should expect from tsearch2?  Or is something still 
>>>>> awry?
>>>>> I'm inclined to think  something else is wrong, after reading some
>>>>> other people's tsearch performance stats.  Thanks!
>>>>>
>>>>> Kris
>>>>
>>>>
>>>>     Regards,
>>>>         Oleg
>>>
>>>
> 
> 


-- 
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL

Attachments:

jd.vcf (text/x-vcard, 638B) download begin:vcard fn:Joshua D. Drake n:Drake;Joshua D. org:Command Prompt, Inc. adr:;;PO Box 215;Cascade Locks;Oregon;97014;USA email;internet:jd@commandprompt.com title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 note:Command Prompt, Inc. is the largest and oldest US based commercial PostgreSQL support provider. We provide the only commercially viable integrated PostgreSQL replication solution, but also custom programming, and support. We authored the book Practical PostgreSQL, the procedural language plPHP, and adding trigger capability to plPerl. x-mozilla-html:FALSE url:http://www.commandprompt.com/ version:2.1 end:vcard

view thread (18+ messages)  latest in thread

Message-ID: <41586013.8050509@commandprompt.com>
Permalink:  ../41586013.8050509@commandprompt.com/
Also on:    postgresql.org/message-id/41586013.8050509@commandprompt.com

 · 

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-admin@postgresql.org
  Cc: jd@commandprompt.com, kris@musicrebellion.com
  Subject: Re: tsearch2 poor performance
  In-Reply-To: <41586013.8050509@commandprompt.com>
  References: <71E37EF6B7DCC1499CEA0316A2568328DC9DF0@loki.wc.globexplorer.net> <4158409E.8060109@musicrebellion.com> <Pine.GSO.4.58.0409272040090.14980@ra.sai.msu.su> <41584E49.5040301@musicrebellion.com> <41584F91.6000109@commandprompt.com> <41585198.6080709@musicrebellion.com> <41586013.8050509@commandprompt.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