Обсуждение: Can I Benefit from and Index Here?
I have a query that is taking longer and longer to run, so I am starting to look at optimizing it a bit... The query is as follows: explain SELECT DISTINCT message_board_topics.rec_num, message_board_topics.topic_name, message_board_topics.topic_body, message_board_topics.topic_author, message_board_topics.topic_author_email, message_board_topics.topic_updated, message_board_topics.administrator_topic, message_board_topics.number_of_comments, to_char(topic_date, 'MM.DD.YYYY') as formatted_date FROM message_board_topics left join message_board_comments on (message_board_comments.topic_id=message_board_topics.rec_num) WHERE upper(topic_name) LIKE upper('madbrowser') OR upper(topic_body) LIKE upper('madbrowser') OR upper(topic_author) LIKE upper('madbrowser') OR upper(message_board_comments.comment_author) LIKE upper('madbrowser') ORDER BY message_board_topics.rec_num DESC; QUERY PLAN ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- ------------------- Unique (cost=24737.05..24980.65 rows=974 width=380) -> Sort (cost=24737.05..24761.41 rows=9744 width=380) Sort Key: message_board_topics.rec_num, message_board_topics.topic_name, message_board_topics.topic_body, message_board_topics.topic_author, message_board_topics.topic_author_email, message_board_topics.topic_updated, message_board_topics.administrator_topic, message_board_topics.number_of_comments, to_char((message_board_topics.topic_date)::timestamp with time zone, 'MM.DD.YYYY'::text) -> Merge Join (cost=17260.42..23018.21 rows=9744 width=380) Merge Cond: ("outer".rec_num = "inner".topic_id) Filter: ((upper(("outer".topic_name)::text) ~~ 'MADBROWSER'::text) OR (upper("outer".topic_body) ~~ 'MADBROWSER'::text) OR (upper(("outer".topic_author)::text) ~~ 'MADBROWSER'::text) OR (upper(("inner".comment_author)::text) ~~ 'MADBROWSER'::text)) -> Index Scan using message_board_topics_pkey on message_board_topics (cost=0.00..1202.44 rows=9744 width=364) -> Sort (cost=17260.42..17562.60 rows=120870 width=16) Sort Key: message_board_comments.topic_id -> Seq Scan on message_board_comments (cost=0.00..5668.70 rows=120870 width=16) Judging from this output, do you guys think I could benefit from any indexing or planner tweaking? Hunter
On Tuesday 22 Apr 2003 5:31 pm, Hunter Hillegas wrote: > I have a query that is taking longer and longer to run, so I am starting to > look at optimizing it a bit... The query is as follows: > > explain SELECT DISTINCT message_board_topics.rec_num, > message_board_topics.topic_name, message_board_topics.topic_body, > message_board_topics.topic_author, message_board_topics.topic_author_email, > message_board_topics.topic_updated, > message_board_topics.administrator_topic, > message_board_topics.number_of_comments, to_char(topic_date, 'MM.DD.YYYY') > as formatted_date > FROM message_board_topics left join > message_board_comments on > (message_board_comments.topic_id=message_board_topics.rec_num) > WHERE > upper(topic_name) LIKE upper('madbrowser') OR upper(topic_body) LIKE > upper('madbrowser') OR upper(topic_author) LIKE upper('madbrowser') OR > upper(message_board_comments.comment_author) LIKE upper('madbrowser') ORDER > BY message_board_topics.rec_num DESC; Well, you might like to try a functional index on upper(topic_name) etc. No point in a straightforward index. I'm assuming you have indexes on the join columns (topic_id, rec_num)? The other thing that leaps out is that you're using LIKE where a simple "=" will do. PG should be able to use an index for this though, since it's anchored on the left. -- Richard Huxton
Richard Huxton <dev@archonet.com> writes: > On Tuesday 22 Apr 2003 5:31 pm, Hunter Hillegas wrote: >> FROM message_board_topics left join >> message_board_comments on >> (message_board_comments.topic_id=message_board_topics.rec_num) >> WHERE >> upper(topic_name) LIKE upper('madbrowser') OR upper(topic_body) LIKE >> upper('madbrowser') OR upper(topic_author) LIKE upper('madbrowser') OR >> upper(message_board_comments.comment_author) LIKE upper('madbrowser') ORDER >> BY message_board_topics.rec_num DESC; > Well, you might like to try a functional index on upper(topic_name) etc. But given the OR structure --- in particular, the fact that he's OR-ing clauses involving fields of both join relations --- an indexscan isn't applicable. For example, there's no point going through the rows of message_board_topics looking for matches for "upper(topic_name) LIKE upper('madbrowser')", because every other row in message_board_topics is also a potential match for any message_board_comments entry that satisfies the WHERE condition on comment_author. So none of the WHERE conditions are actually useful until after the join is formed. It might work to break the thing down into a union of left-side and right-side conditions. For instance SELECT .. FROM a left join b on (a.id = b.id) WHERE ORed-conditions-on-fields-of-a UNION SELECT .. FROM a join b on (a.id = b.id) WHERE ORed-conditions-on-fields-of-b This is not necessarily faster (if there are *lots* of matches, the time needed to do duplicate elimination in the UNION step will hurt). But it seems worth a try if the conditions are all individually indexable. regards, tom lane
Tom- I'd like try to implement something like what you've suggested but I'm not totally up to speed on what your pseudo-sql would translate to... I'm the DBA here by default, not by training. ;-) Hunter > From: Tom Lane <tgl@sss.pgh.pa.us> > Date: Wed, 23 Apr 2003 10:28:10 -0400 > To: Richard Huxton <dev@archonet.com> > Cc: Hunter Hillegas <lists@lastonepicked.com>, PostgreSQL > <pgsql-general@postgresql.org> > Subject: Re: [GENERAL] Can I Benefit from and Index Here? > > Richard Huxton <dev@archonet.com> writes: >> On Tuesday 22 Apr 2003 5:31 pm, Hunter Hillegas wrote: >>> FROM message_board_topics left join >>> message_board_comments on >>> (message_board_comments.topic_id=message_board_topics.rec_num) >>> WHERE >>> upper(topic_name) LIKE upper('madbrowser') OR upper(topic_body) LIKE >>> upper('madbrowser') OR upper(topic_author) LIKE upper('madbrowser') OR >>> upper(message_board_comments.comment_author) LIKE upper('madbrowser') ORDER >>> BY message_board_topics.rec_num DESC; > >> Well, you might like to try a functional index on upper(topic_name) etc. > > But given the OR structure --- in particular, the fact that he's OR-ing > clauses involving fields of both join relations --- an indexscan isn't > applicable. For example, there's no point going through the rows of > message_board_topics looking for matches for "upper(topic_name) LIKE > upper('madbrowser')", because every other row in message_board_topics > is also a potential match for any message_board_comments entry that > satisfies the WHERE condition on comment_author. So none of the WHERE > conditions are actually useful until after the join is formed. > > It might work to break the thing down into a union of left-side and > right-side conditions. For instance > > SELECT .. FROM a left join b on (a.id = b.id) > WHERE ORed-conditions-on-fields-of-a > UNION > SELECT .. FROM a join b on (a.id = b.id) > WHERE ORed-conditions-on-fields-of-b > > This is not necessarily faster (if there are *lots* of matches, the time > needed to do duplicate elimination in the UNION step will hurt). But it > seems worth a try if the conditions are all individually indexable. > > regards, tom lane