pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedslow DELETE queries
3+ messages / 2 participants
[nested] [flat]
* slow DELETE queries
@ 2002-06-20 13:23 Denis <denis@startsiden.no>
2002-06-20 15:01 ` Re: slow DELETE queries Manfred Koizar <mkoi-pg@aon.at>
0 siblings, 1 reply; 3+ messages in thread
From: Denis @ 2002-06-20 13:23 UTC (permalink / raw)
To: pgsql-sql; +Cc: denis@startsiden.no; gry@kvinneguiden.no
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: slow DELETE queries
2002-06-20 13:23 slow DELETE queries Denis <denis@startsiden.no>
@ 2002-06-20 15:01 ` Manfred Koizar <mkoi-pg@aon.at>
2002-06-24 08:28 ` Re: slow DELETE queries Denis <denis@startsiden.no>
0 siblings, 1 reply; 3+ messages in thread
From: Manfred Koizar @ 2002-06-20 15:01 UTC (permalink / raw)
To: Denis <denis@startsiden.no>; +Cc: pgsql-sql; denis@startsiden.no; gry@kvinneguiden.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?
Servus
Manfred
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: slow DELETE queries
2002-06-20 13:23 slow DELETE queries Denis <denis@startsiden.no>
2002-06-20 15:01 ` Re: slow DELETE queries Manfred Koizar <mkoi-pg@aon.at>
@ 2002-06-24 08:28 ` Denis <denis@startsiden.no>
0 siblings, 0 replies; 3+ messages in thread
From: Denis @ 2002-06-24 08:28 UTC (permalink / raw)
To: Manfred Koizar <mkoi-pg@aon.at>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2002-06-24 08:28 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-06-20 13:23 slow DELETE queries Denis <denis@startsiden.no>
2002-06-20 15:01 ` Manfred Koizar <mkoi-pg@aon.at>
2002-06-24 08:28 ` Denis <denis@startsiden.no>
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