Mailbox-Line: From denis@startsiden.no  Mon Jun 24 08:42:55 2002
Content-Type: text/plain;
  charset="iso-8859-1"
From: Denis <denis@startsiden.no>
To: Manfred Koizar <mkoi-pg@aon.at>
Subject: Re: slow DELETE queries
Date: Mon, 24 Jun 2002 10:28:05 +0200
User-Agent: KMail/1.4.1
References: <200206201523.53903.denis@startsiden.no>
	<0gp3huoagdqn6hs4di0f41r1pi79rttlkj@4ax.com>
In-Reply-To: <0gp3huoagdqn6hs4di0f41r1pi79rttlkj@4ax.com>
Cc: pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Message-Id: <200206241028.05886.denis@startsiden.no>

On Thursday 20 June 2002 05:01 pm, you wrote:
> On Thu, 20 Jun 2002 15:23:53 +0200, Denis <denis@startsiden.no> wrote:
> >I traced the queries slowing it all down to this snippet in the debug log:
> >DELETE FROM phpbb_search_wordlist WHERE word_id IN (
> >SELECT word_id FROM phpbb_search_wordmatch WHERE word_id IN (
> >SELECT word_id FROM phpbb_search_wordmatch WHERE post_id IN (70535)
> >GROUP BY word_id) GROUP BY word_id HAVING COUNT(word_id) = 1)

Hi Manfred,

Thanks for your response.

> IN is known to be problematic;  try to use EXISTS or =, wherever
> possible.  Can you rewrite your innermost where clause to WHERE
> post_id = 70535?

I will try out patching the DB abstraction layer to use this if indeed the 
query allows it. (I am unsure whether this query will sometimes have more IDs 
inside the IN. 

> Also create an index on phpbb_search_wordmatch.post_id.

I will try this too ! 

> If it's still too slow, give us some more information:
> Is word_id unique in phpbb_search_wordlist?
> Is (post_id, word_id) unique in phpbb_search_wordmatch?
> How many rows are in your tables?

Here is the information :
word_id is indeed unique in phpbb_search_wordlist. 
(post_id, word_id) should be unique in phpbb_search_wordmatch if the 
application is correctly written. It is supposed to serve as a relational 
lookup table for resolving search queries into a list of words with matching 
words inside.

Rows in related tables :
phpbb_search_wordmatch : 2907191
phpbb_search_wordlist : 118306
phpbb_posts : 70953

VACUUM, VACUUM ANALYZE is performed nightly, maybe this would help to do more 
often ? 

-- 
Denis Braekhus - ABC Startsiden AS
http://www.startsiden.no