From: Denis <denis@startsiden.no>
To: Manfred Koizar <mkoi-pg@aon.at>
Cc: pgsql-sql@postgresql.org
Subject: Re: slow DELETE queries
Date: Mon, 24 Jun 2002 10:28:05 +0200
Message-ID: <200206241028.05886.denis@startsiden.no> (raw)
In-Reply-To: <0gp3huoagdqn6hs4di0f41r1pi79rttlkj@4ax.com>
References: <200206201523.53903.denis@startsiden.no>
<0gp3huoagdqn6hs4di0f41r1pi79rttlkj@4ax.com>
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
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-sql@postgresql.org
Cc: denis@startsiden.no, mkoi-pg@aon.at
Subject: Re: slow DELETE queries
In-Reply-To: <200206241028.05886.denis@startsiden.no>
* 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