From: Denis <denis@startsiden.no>
To: pgsql-sql@postgresql.org
Cc: denis@startsiden.no
Cc: gry@kvinneguiden.no
Subject: slow DELETE queries
Date: Thu, 20 Jun 2002 15:23:53 +0200
Message-ID: <200206201523.53903.denis@startsiden.no> (raw)
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=283528 width=4)
-> Group (cost=0.00..147789787274.45 rows=2835277 width=4)
-> Index Scan using word_id_phpbb_search_wordmatch on 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=138 width=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 | not null 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
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, gry@kvinneguiden.no
Subject: Re: slow DELETE queries
In-Reply-To: <200206201523.53903.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