Re: Serious Performance Loss in 7.0.2??

Tim Perdue wrote:
>
> I know you're all sick of hearing from me.

I hope there was really a <g> at the end of that because it is not true
at all! When problems are seen and solved they offer opportunities for
others in the future, and it is also how things get better :-)

> The following query is at the very heart of the site and it takes
> upwards of 15-20 seconds to run now. It used to be instantaneous.
>
> explain SELECT mailid, mail_date, mail_is_followup, mail_from,
> mail_subject
> FROM mail_archive WHERE mail_list=35 AND mail_year=2000
> AND mail_month=1 ORDER BY mail_date DESC LIMIT 26 OFFSET 0;
>
> NOTICE: QUERY PLAN:
>
> Sort (cost=138.41..138.41 rows=34 width=44)
> -> Index Scan using idx_mail_archive_list_yr_mo on tbl_mail_archive
> (cost=0.00..137.55 rows=34 width=44)
>
> EXPLAIN

OK, I'll give it a go :-)

First of all, I find it easiest to optimise these sort of queries in
psql because you can go back and edit things and 'play' quite a bit to
achieve the desired behaviour, then implement it back in the old PHP
code (or wherever :-).

The query optimiser changed quite a bit from 6.5.3 to 7.x and this seems
to be one area that now works harder to do what you say. From the name
of your index it seems that you have an index on mail_list, mail_year,
mail_month, mail_date?

PostgreSQL seems to not get the index choice right when you have index
matches that are like =, =, =, DESC so you actually need to specify the
ORDER BY clause in full like:
ORDER BY mail_list DESC, mail_year DESC, mail_month DESC, mail_date
DESC
and things will hopefully be all OK again.

Personally I consider this to be a 'bug', or at least a 'buglet', but I
guess I'd bow to Tom's opinion on that :-)

Hope this is some help,
Andrew.

--
_____________________________________________________________________
Andrew McMillan, e-mail: Andrew(at)cat-it(dot)co(dot)nz
Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington
Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267