Обсуждение: slow DELETE queries

I am having considerable trouble with phpBB 2.0.1, a forum application 
(http://www.phpbb.com) on PostgreSQL 7.1.3.. 

There are some huge slowdowns in operation when moderating the board.
I have searched their bugsbase, and tried the supportforum, without much luck.
Normal VACUUM schedule 

I traced the queries slowing it all down to this snippet in the debug log:
---------
Jun 20 15:03:45 ps2 postgres[18531]: [9-1] DEBUG:  query: 
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)
---------

An EXPLAIN turns out this :

=>EXPLAIN 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);
NOTICE:  QUERY PLAN:

Seq Scan on phpbb_search_wordlist  (cost=0.00..17254458491840534.00 rows=116750 width=6)
SubPlan
->  Materialize  (cost=147789794362.64..147789794362.64 rows=283528 width=4)->  Aggregate  (cost=0.00..147789794362.64
rows=283528width=4) ->  Group  (cost=0.00..147789787274.45 rows=2835277 width=4)  ->  Index Scan using
word_id_phpbb_search_wordmatchon phpbb_search_wordmatch  (cost=0.00..147789780186.26 rows=2835277 width=4)SubPlan   ->
Materialize (cost=52125.21..52125.21 rows=14 width=4)    ->  Group  (cost=52124.86..52125.21 rows=14 width=4)     ->
Sort (cost=52124.86..52124.86 rows=138 width=4)      ->  Seq Scan on phpbb_search_wordmatch  (cost=0.00..52119.96
rows=138width=4)
 

EXPLAIN

I figured maybe the schema is not properly set up with indexes, so I tried some descriptions : 

=> \d phpbb_search_wordlist                                   Table "phpbb_search_wordlist" Attribute  |         Type
      |                            Modifier
 
-------------+-----------------------+----------------------------------------------------------------word_id     |
integer              | not null default nextval('phpbb_search_wordlist_id_seq'::text)word_text   | character
varying(50)| not null default ''word_common | smallint              | not null default '0'
 
Indices: phpbb_search_wordlist_pkey,        word_id_phpbb_search_wordlist

=>\d phpbb_search_wordmatch       Table "phpbb_search_wordmatch" Attribute  |   Type   |       Modifier
-------------+----------+----------------------post_id     | integer  | not null default '0'word_id     | integer  |
notnull default '0'title_match | smallint | not null default '0'
 
Index: word_id_phpbb_search_wordmatch

The indexes are like this :

=> \d phpbb_search_wordlist_pkey
Index "phpbb_search_wordlist_pkey"Attribute |         Type
-----------+-----------------------word_text | character varying(50)
unique btree (primary key)

=> \d word_id_phpbb_search_wordlist
Index "word_id_phpbb_search_wordlist"Attribute |  Type
-----------+---------word_id   | integer
btree

=> \d word_id_phpbb_search_wordmatch
Index "word_id_phpbb_search_wordmatch"Attribute |  Type
-----------+---------word_id   | integer
btree


I have been trying to read up on indexes and performance, also earlier, but
here I am on shaky ice(?). I am not sure how to enhance the performance here,
and the schema does indeed look sane to me. Might of course be lack of 
knowledge in SQL performance issues ! :-)

Anyone who can see what could be improved here, either in the queries or 
in the schema ? 

Any help highly appreciated, I will of course continue asking the phpBB people,
but to them postgresql support is very new, and I figure any SQL expertise 
would help us both out ! :)
-- 
Denis Braekhus - ABC Startsiden AS
http://www.startsiden.no

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)

Denis,

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?

Also create an index on phpbb_search_wordmatch.post_id.

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?

ServusManfred

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