From: | Denis <denis(at)startsiden(dot)no> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Cc: | denis(at)startsiden(dot)no, gry(at)kvinneguiden(dot)no |
Subject: | slow DELETE queries |
Date: | 2002-06-20 13:23:53 |
Message-ID: | 200206201523.53903.denis@startsiden.no |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2002-06-20 13:28:23 | Re: psql -E <dbname> Floating exception (coredump) |
Previous Message | Jan Wieck | 2002-06-20 12:23:10 | Re: Which one is faster? |